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

WITH t1 AS (
    SELECT sales_date,
           user_id,
           DENSE_RANK() over (PARTITION BY user_id ORDER BY sales_date ASC) AS rank_num
    FROM sales_tb
    GROUP BY sales_date, user_id
),
t2 AS (
    SELECT user_id,
           DATE_ADD(sales_date, INTERVAL - rank_num DAY ) AS origin_date
    FROM t1
),
t3 AS (
    SELECT user_id,
           count(origin_date) AS days_count
    FROM t2
    GROUP BY user_id
),
t4 AS (
    SELECT *
    FROM t3
    WHERE days_count >= 2
    ORDER BY user_id ASC
)
SELECT * FROM t4;

# 这道题的关键在于如何在第一步进行（用户，日期）去重操作！