题解 | #某店铺的各商品毛利率及店铺整体毛利率#

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