Write a simple SQL query to find customers who placed orders on three consecutive days.

To identify customers who have placed orders on three consecutive days, you can use window functions to compare each order date with the two preceding dates for the same customer. The query below assumes a table named orders with columns order_id, customer_id, and order_date.

WITH ordered AS (
    SELECT
        customer_id,
        order_date,
        LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev1,
        LAG(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev2
    FROM orders
)
SELECT DISTINCT customer_id
FROM ordered
WHERE order_date = prev1 + INTERVAL '1' DAY
  AND prev1 = prev2 + INTERVAL '1' DAY;

Explanation

1. The CTE ordered adds two lagged columns: prev1 (the previous day's order) and prev2 (two days before).
2. The WHERE clause checks that the current order_date is exactly one day after prev1 and that prev1 is exactly one day after prev2. This guarantees a run of three consecutive days.
3. DISTINCT ensures each customer appears only once.

Complexity

The query scans the table once to compute the window functions, which is O(n) time and O(n) space for the intermediate result. The final filtering and distinct operation are also linear. This is efficient for large datasets and works on most modern RDBMS that support window functions.

If your database does not support window functions, you can achieve the same result with self‑joins:

SELECT DISTINCT o1.customer_id
FROM orders o1
JOIN orders o2 ON o1.customer_id = o2.customer_id AND o2.order_date = o1.order_date + INTERVAL '1' DAY
JOIN orders o3 ON o1.customer_id = o3.customer_id AND o3.order_date = o1.order_date + INTERVAL '2' DAY;

Both approaches return the list of customers who have placed orders on three consecutive days.