题解 | #某店铺的各商品毛利率及店铺整体毛利率#
零食类商品中复购率top3高的商品
http://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
1.统计每个零食类商品每个用户在近90天内的购买次数
with t1 as(
select t1.product_id, t2.uid,count(t2.uid) cnt
from tb_product_info t1
join tb_order_detail t3
on t1.product_id = t3.product_id
join tb_order_overall t2
on t2.order_id = t3.order_id
where status=1 and tag = '零食'
and date(event_time)>=date_sub((select date(max(event_time)) from tb_order_overall),interval 89 day)
group by t1.product_id,t2.uid)
得到如下表所示的统计结果
2.计算每个商品的复购率并得到top3
select product_id, round(sum(if(cnt>1,1,0))/count(uid),3) repurchase_rate
from t1
group by
product_id
order by repurchase_rate desc, product_id
limit 3
3.最终的sql
with t1 as(
select t1.product_id, t2.uid,count(t2.uid) cnt
from tb_product_info t1
join tb_order_detail t3
on t1.product_id = t3.product_id
join tb_order_overall t2
on t2.order_id = t3.order_id
where status=1 and tag = '零食'
and date(event_time)>=date_sub((select date(max(event_time)) from tb_order_overall),interval 89 day)
group by t1.product_id,t2.uid)
select product_id, round(sum(if(cnt>1,1,0))/count(uid),3) repurchase_rate
from t1
group by
product_id
order by repurchase_rate desc, product_id
limit 3
