题解 | #某店铺的各商品毛利率及店铺整体毛利率#
零食类商品中复购率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
哎，不知道说什么，父母有他们时代的历史包袱和思想惯性，这件事我也是在大学刚毕业甚至工作了2-3年后就一直感受到的，除非你们一直有深度沟通的习惯，否则不要期望通过几次沟通就能互相理解。所以“离开原生家庭，每个人送给自己的最重要的成年礼”，保持距离，长期沟通，精神独立，然后继续做你想做的事。