题解 |

# 1.查询出所有商品被购买记录	
	select
		a.product_id,
		b.uid , 
		b.order_id
	from
		tb_product_info a ,
		tb_order_overall b ,
		tb_order_detail c
	where
		a.product_id = c.product_id
		and c.order_id = b.order_id
		and b.status = 1
		and tag = '零食'
		and datediff(date((select max(event_time) from tb_order_overall)),
		date(event_time))<90;
8002|101|301001|2021-09-30 10:00:00
8003|102|301011|2021-10-31 11:00:00
8001|102|301011|2021-10-31 11:00:00
8001|102|301002|2021-10-01 11:00:00
8003|102|301002|2021-10-01 11:00:00
8002|101|301003|2021-11-02 10:00:00
8003|101|301003|2021-11-02 10:00:00
8002|105|301013|2021-11-02 10:00:00
8003|104|301005|2021-11-03 10:00:00

# 2. 再根据商品id和用户id分组，计算每个用户购买某个商品的订单数量。如果数量>=2 计数为1
	select
		a.product_id,
		b.uid ,
       	if(count(b.order_id)>= 2,1,0)
	from
		tb_product_info a ,
		tb_order_overall b ,
		tb_order_detail c
	where
		a.product_id = c.product_id
		and c.order_id = b.order_id
		and b.status = 1
		and tag = '零食'
		and datediff(date((select max(event_time) from tb_order_overall)),
		date(event_time))<90
	group by
		a.product_id,
		b.uid
8002|101|1
8003|102|1
8001|102|1
8003|101|0
8002|105|0
8003|104|0		
		
# 3. 按照商品id分组.  count(t.cnt) 就是某一商品被购买过的人数，  sum(t.cnt)是某商品被购买至少两次的人数。 

select
	product_id,round(sum(t.cnt)/ count(t.cnt),3)  rate
from
	(select
		a.product_id,uid ,if(count(b.order_id)>= 2,1,0) cnt
	from tb_product_info a , tb_order_overall b ,tb_order_detail c
	where a.product_id = c.product_id and c.order_id = b.order_id and b.status = 1
	and tag = '零食' and datediff(date((select max(event_time) from tb_order_overall)), date(event_time))<90
	group by a.product_id, b.uid) t
group by t.product_id
order by rate desc,product_id limit 3;

8001|1.000
8002|0.500
8003|0.333