大数据SQL题02 查询至少连续三天下单的用户

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

题目需求

查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：

user_id
101

订单信息表：order_info

order_id(订单id)	user_id(用户id)	create_date(下单日期)	total_amount(订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

解题思路1：lead()/lag()开窗

SELECT  user_id
FROM
(
	SELECT  user_id,
	        DATEDIFF(create_date,lag_2_date) AS diff
	FROM
	(
		SELECT  user_id,
		        create_date,
		        lag(create_date,2,'1970-01-01') over (partition by user_id ORDER BY create_date asc) AS lag_2_date
		FROM
		(
			SELECT  user_id,
			        create_date
			FROM order_info
			GROUP BY  user_id,
			          create_date
		) t1
	) t2
) t3
WHERE diff = 2
GROUP BY  user_id

解题思路2：row_number()日期对比

SELECT  user_id
FROM
(
	SELECT  user_id,
	        DATE_SUB(create_date,rn) AS flag
	FROM
	(
		SELECT  user_id,
		        create_date,
		        row_number() over (partition by user_id ORDER BY create_date asc) AS rn
		FROM
		(
			SELECT  user_id,
			        create_date
			FROM order_info
			GROUP BY  user_id,
			          create_date
		) t1
	) t2
) t3
GROUP BY  user_id,
          flag
HAVING COUNT(1) >= 3