Customer ID 1 meets the criteria because they made purchases on 25th Jan, 26th Jan, and 27th Jan – three consecutive days. Customer ID 2 does not meet the criteria because, although they made more than three purchases, the dates are not consecutive. Write an SQL query to find customer IDs of customers who have made purchases on at least three consecutive days.

WITH ranked AS (
  SELECT
    customer_id,
    purchase_date,
    LAG(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS prev_date
  FROM purchases
),
streaks AS (
  SELECT
    customer_id,
    purchase_date,
    CASE
      WHEN prev_date IS NULL OR purchase_date - prev_date > 1 THEN 1
      ELSE 0
    END AS new_streak
  FROM ranked
),
grouped AS (
  SELECT
    customer_id,
    SUM(new_streak) OVER (PARTITION BY customer_id ORDER BY purchase_date ROWS UNBOUNDED PRECEDING) AS grp,
    COUNT(*) OVER (PARTITION BY customer_id, SUM(new_streak) OVER (PARTITION BY customer_id ORDER BY purchase_date ROWS UNBOUNDED PRECEDING)) AS streak_len
  FROM streaks
)
SELECT DISTINCT customer_id
FROM grouped
WHERE streak_len >= 3;