题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
http://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
SELECT user_id,
MAX(diff_rank) AS days_count
FROM (
SELECT user_id,
DATE_SUB(sales_date, INTERVAL date_rank DAY) AS dt,
DENSE_RANK() OVER(PARTITION BY user_id, DATE_SUB(sales_date, INTERVAL date_rank DAY) ORDER BY sales_date) AS diff_rank
FROM (
SELECT user_id, sales_date,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY sales_date) AS date_rank
FROM sales_tb
)date_rank_t
)diff_rank_t
GROUP BY user_id
HAVING days_count >= 2
ORDER BY user_id