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

#1.创建临时表a保存零食类商品id和uid
with a as
(
    select od.product_id product_id, uid
    from tb_product_info pi 
    inner join tb_order_detail od
    on pi.product_id = od.product_id
    inner join tb_order_overall ov
    on ov.order_id = od.order_id
    where event_time BETWEEN 
        date_sub((select max(event_time) from tb_order_overall),INTERVAL 89 day)
        and (select max(event_time) from tb_order_overall)
       and tag = '零食'
       and status = 1
)

#2.计算复购率
select repurchase_table.product_id, 
round(repeated_num / total_num, 3) as repurchase_rate
from
    #repurchase_table保存复购人数
    (
        select product_id, 
        (case when count(uid)>1 
            then count(distinct uid)
            else 0
            end) as repeated_num
        from a
        group by product_id, uid
    ) repurchase_table
right join
    #total_table总人数
    (
    select product_id, count(distinct uid) as total_num
        from a
        group by product_id
    ) total_table
on repurchase_table.product_id = total_table.product_id 
order by repurchase_rate desc, product_id 
limit 3

这题好难啊，写了一上午。。

首先是日期函数：计算日期区间用date_add(日期, interval x day/month..)和between and

接着是复购率计算：对每个product_id都要计算购买人数和复购人数，要分别计算并按照product_id把两个表连接