大数据SQL题45 复购率问题

原题链接：http://practice.atguigu.cn/#/question/45/desc?qType=SQL

题目需求

现有电商订单表（order_detail）如下。

order_id(订单id)	user_id(用户id)	product_id(商品id)	price（售价）	cnt（数量）	order_date（下单时间）
1	1	1	5000	1	2022-01-01
2	1	3	5500	1	2022-01-02
3	1	7	35	2	2022-02-01
4	2	2	3800	3	2022-03-03

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（以订单详情表(order_detail)中最后的日期）在内的近90天。结果中复购率保留2位小数，并按复购率倒序、商品ID升序排序。

期望结果如下：

product_id(商品id)	cpr<decimal(16,2)>(复购率)
3	1.00
9	1.00
8	0.50
5	0.33
7	0.25
1	0.00
2	0.00
6	0.00

解题思路

SELECT  product_id,
        cast(SUM(IF(purchase_cnt >= 2,1,0))/SUM(1) AS decimal(16,2)) AS cpr
FROM
(
	SELECT  product_id,
	        user_id,
	        COUNT(1) AS purchase_cnt
	FROM
	(
		SELECT  product_id,
		        order_date,
		        user_id,
		        MAX(order_date) OVER () AS max_date
		FROM order_detail
	) t1
	WHERE DATEDIFF(max_date, order_date) < 90
	GROUP BY  product_id,
	          user_id
) t1
GROUP BY  product_id