题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
http://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
思路：
-
从商品信息表tpi中筛选出tag="零食"的记录
-
tpi连接tod(on=product_id)，再连接too(on=order_id)，均进行内连接
-
使用窗口函数从too中计算出最近日期，注意日期格式转换
-
计算每个产品每个用户在近90天内的购买次数
-
对每个产品分组计算购买次数大于1的用户数与总用户数，从而计算出复购率
-
复购率保留3位，按复购率倒序、按product_id升序，保留前三（注意SQL执行顺序）
代码：
select
product_id,
round(sum(if(purchase_cnt > 1, 1, 0)) / count(uid), 3) as repurchase_rate
from (
select
product_id,
uid,
sum(if(datediff(recent, dt) <= 89, 1, 0)) as purchase_cnt
from (
select
tod.product_id,
too.uid,
date(too.event_time) as dt,
max(date(too.event_time)) over () as recent
from tb_order_detail as tod
inner join tb_product_info as tpi on tod.product_id = tpi.product_id
inner join tb_order_overall as too on tod.order_id = too.order_id
where
tpi.tag = "零食"
) as t
group by product_id, uid
) as t2
group by product_id
order by repurchase_rate desc, product_id
limit 3
投递字节跳动等公司8个岗位 >