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

with order_log as -- 近90天内,各零食类产品,每位uid是否有复购
         (
             select distinct
                    product_id,
                    uid,
#                     可以省略的字段(用于条件/参考数据/辅助列情况)
#                     tag,
#                     order_id,
#                     status,
#                     date(event_time) event_date,
#                     date(max(event_time) over (order by event_time desc)) as today, -- 今天(最大日期)
#                     count(event_time) over (partition by product_id,uid) as purchase_cnt, -- 每个产品每个用户的购买次数
                    if(count(event_time) over (partition by product_id,uid) >= 2, 1, 0) as if_repurchase
             from tb_order_detail
                      left join tb_order_overall using (order_id)
                      left join tb_product_info using (product_id)
             where tag = '零食' -- 零食大类
             and   (status = '0' or status = '1') -- 筛选退货记录
             and   date(event_time) >= -- 业务日期大于Today()-90
                    (
                 select date_sub(max(date(event_time)), interval 89 day) recent90_date
                 from tb_order_overall
                    )
            #         只是为了看得舒服排个序        
            #  order by product_id, event_time
         )

select product_id,
       round(sum(if_repurchase) / count(distinct uid), 3) as repurchase_rate
from order_log
group by product_id

-- 显示复购率前三的(ID升序)的商品,并列第三的不显示
-- 如果并列也显示,需要用到rank开窗求顺序后显示rank <= 3的
order by repurchase_rate desc, product_id asc
limit 3;