Objective

Write a SQL query to identify users who went on a shopping spree, defined as making purchases on 3 or more consecutive days.

Table Schema:

• purchases

Task Requirements:

• A shopping spree is 3 or more consecutive calendar days with at least one purchase each day
• Multiple purchases on the same day count as a single day
• A user qualifies if they have at least one such streak, regardless of how many streaks they have
• Return distinct user IDs sorted in ascending order
  
  

Output columns: user_id

WITH daily_purchases AS (
    SELECT DISTINCT user_id, purchase_date
    FROM purchases
),
streaks AS (
    SELECT
        user_id,
        purchase_date - ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY purchase_date
        )::INT AS date_group
    FROM daily_purchases
),
spree_users AS (
    SELECT user_id
    FROM streaks
    GROUP BY user_id, date_group
    HAVING COUNT(*) >= 3
)
SELECT DISTINCT user_id
FROM spree_users
ORDER BY user_id;

Explanation

Step 1: Deduplicating Same-Day Purchases

WITH daily_purchases AS (
    SELECT DISTINCT user_id, purchase_date
    FROM purchases
)

A user might make multiple purchases on the same day. Since we only care about whether a purchase happened on a given day (not how many), SELECT DISTINCT collapses duplicate dates per user into a single row.

Step 2: The Row Number Trick for Consecutive Dates

purchase_date - ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY purchase_date
)::INT AS date_group

This is the core technique. For each user, ROW_NUMBER() assigns sequential numbers (1, 2, 3...) to their purchase dates in order. When you subtract this number from the date, consecutive dates all produce the same result. For example, if a user purchased on Jan 5, 6, 7, the row numbers are 1, 2, 3, and date - row_number gives Jan 4, Jan 4, Jan 4. Non-consecutive dates break this pattern and produce different values.

Step 3: Grouping by Streak

SELECT user_id
FROM streaks
GROUP BY user_id, date_group
HAVING COUNT(*) >= 3

GROUP BY user_id, date_group clusters each user's consecutive-day sequences together. HAVING COUNT(*) >= 3 filters to only keep groups where the streak is 3 or more days. This gives us one row per qualifying streak.

Step 4: Deduplicating Users

SELECT DISTINCT user_id
FROM spree_users
ORDER BY user_id

A user might have multiple qualifying streaks (e.g., one in March and another in April). DISTINCT ensures each user appears only once in the final output. The results are sorted by user_id in ascending order.