Row numbers are used, but they don't check for consecutive purchases. The number only indicates whether there are more than three purchases. How can we modify this to check for consecutive purchases?

Question

Assisting AI · Accepted Answer

To detect consecutive purchases you need to compare each purchase date with the previous one. A common approach is to use the LAG window function to get the previous purchase date per customer, then compute the difference in days. If the difference is 1, the dates are consecutive. Group by customer and count the length of consecutive streaks. For example:

```sql
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
),
consecutive 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
),
streaks AS (
  SELECT
    customer_id,
    SUM(new_streak) OVER (PARTITION BY customer_id ORDER BY purchase_date ROWS UNBOUNDED PRECEDING) AS streak_id,
    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 consecutive
)
SELECT DISTINCT customer_id
FROM streaks
WHERE streak_len >= 3;
```

This query assigns a new streak id whenever a gap >1 day occurs, then counts the length of each streak. Customers with a streak length of at least 3 are returned. Complexity is O(n) with a few window passes, and it works for large datasets.

Row numbers are used, but they don't check for consecutive purchases. The number only indicates whether there are more than three purchases. How can we modify this to check for consecutive purchases?

💡 Model Answer

🎤 Get questions like this answered in real-time