题解 | 某宝店铺连续2天及以上购物的用户及其对应的天数
某宝店铺连续2天及以上购物的用户及其对应的天数
https://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
WITH sub AS(
SELECT DISTINCT user_id, sales_date
FROM sales_tb
),
sub2 AS(
SELECT user_id, sales_date, date_sub(sales_date, INTERVAL ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY sales_date) DAY) AS consec_date
FROM sub
)
SELECT user_id, count(*) days_count
FROM sub2
GROUP BY user_id
HAVING days_count >= 2;
查看7道真题和解析