[牛客面试题 SQL16 零食类商品中复购率top3高的商品]

牛客面试题 SQL16 零食类商品中复购率top3高的商品

• 问题：请统计零食类商品中复购率top3高的商品。
• 定义：某商品 复购率=近90天购买它至少两次的人数/购买它的总人数。
  （近90天指包含最大日期（记为当天）在内的近90天）
  
• 近90天 购买它两次的人数。

	  select t.product_id,uid,count(*) as 用户购买的次数
			from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
			where date(event_time) > date_sub((select date(max(event_time)) from tb_order_overall)
							,interval 89 day ) and tag='零食'  
			group by t.product_id,uid

-难点在于 按照 product_id 和 uid 分组之后，用count(*) 就能得到 用户购买的次数（就是当product_id 和uid 都出现的次数）

• 个人认为 top 3应该用 dense_rank()
• 注意，在开窗函数里使用排序函数之后，在外面就不能用order by 了。
• 序号 函数
  row_number()：显示分区中不重复不间断的序号（1，2，3，4
  dense_rank():显示分区中重复不间断的序号（1，2，2，3）
  rank（）：显示分区中重复间断的序号。（1，2，2，4

select product_id ,requrchase_rate
  from (
	  select  product_id,
	-- 		sum(if( 用户购买的次数>1,1,0)) 购买了2次的用户数,
	--         count(distinct uid) 购买的总人数,
			round(sum(if(用户购买的次数>1,1,0))/count(distinct uid),3)  requrchase_rate,
			rank() over(order by round(sum(if(用户购买的次数>1,1,0))/count(distinct uid),3)) 排名
	  from(
		  select t.product_id,uid ,count(*)
			from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
			where date(event_time) > date_sub((select date(max(event_time)) from tb_order_overall)
							,interval 89 day ) and tag='零食'  
			group by t.product_id,uid
		) t
	group by product_id

) tt 
where 排名<=3
order by requrchase_rate desc,product_id ;