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

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