题解 | 某宝店铺连续2天及以上购物的用户及其对应的天数
某宝店铺连续2天及以上购物的用户及其对应的天数
https://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
WITH sub AS( SELECT DISTINCT sales_date, user_id FROM sales_tb ) SELECT user_id, COUNT(*)+1 FROM( SELECT sales_date, user_id, datediff(sales_date, LAG(sales_date, 1)OVER(PARTITION BY user_id ORDER BY sales_date)) last_date_interval FROM sub ) sub2 WHERE last_date_interval = 1 GROUP BY user_id ORDER BY user_id ASC;
查看12道真题和解析