题解 | 零食类商品中复购率top3高的商品

with t as (
    select
    product_id,
    count(distinct uid) as perchase_cnt,
    count(uid)-count(distinct uid) as re_perchase_cnt
    from tb_order_detail inner join tb_order_overall using(order_id)
    inner join tb_product_info using(product_id)
    where tag='零食' AND event_time >= (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
    group by product_id
)
select
product_id,
round(re_perchase_cnt/perchase_cnt,3) as repurchase_rate
from t
order by repurchase_rate desc, product_id asc
limit 3




# SELECT product_id,
#     ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
# FROM (
#     SELECT uid, product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
#     FROM tb_order_detail
#     JOIN tb_order_overall USING(order_id)
#     JOIN tb_product_info USING(product_id)
#     WHERE tag="零食" AND event_time >= (
#         SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
#         FROM tb_order_overall
#     )
#     GROUP BY uid, product_id
# ) as t_uid_product_info
# GROUP BY product_id
# ORDER BY repurchase_rate DESC, product_id
# LIMIT 3;