题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

WITH t1 AS (
SELECT
user_id,
sales_date,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY sales_date) AS rk
FROM sales_tb
GROUP BY user_id,sales_date
)

,
t2 AS (SELECT
user_id,
DATE_SUB(sales_date,INTERVAL rk DAY) AS days
FROM t1)

SELECT
user_id,
COUNT(days) AS days_count
FROM t2
GROUP BY user_id,days
HAVING days_count>=2