Competitive SQL Series #1 — Identify Consecutive Purchase Patterns (Hard)

Problem Statement:

You are given a table purchases with the following schema:

purchases(
 user_id INT,
 purchase_date DATE,
 amount INT
)

Write an SQL query to find all users who made purchases on three or more consecutive days.

Sample Input:

Expected Output:

Hint:

Use DATEDIFF(), window functions, or gap-based grouping to identify sequences of consecutive dates.

Solution:

WITH ranked_purchases AS (
  SELECT
    user_id,
    purchase_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
  FROM purchases
),
grouped_purchases AS (
  SELECT
    user_id,
    purchase_date,
    DATE_SUB(purchase_date, INTERVAL rn DAY) AS grp
  FROM ranked_purchases
),
consecutive_groups AS (
  SELECT
    user_id,
    COUNT(*) AS consecutive_days
  FROM grouped_purchases
  GROUP BY 1, 2
)
SELECT DISTINCT user_id
FROM consecutive_groups
WHERE consecutive_days >= 3;

Explanation:

1. ranked_purchases:
   Assigns a row number to each user's purchases in order of date.
2. grouped_purchases:
   Uses the trick:
   purchase_date - row_number gives the same result for consecutive days
   → all consecutive days fall into the same group.
3. consecutive_groups:
   Groups by this "date gap" and counts the number of rows (i.e., consecutive days).
4. Final SELECT:
   Filters users who had 3 or more purchases in a row.

Stay tuned! Happy learning!