Interview › Consecutive dates

SQL interview: customers who ordered 3 days in a row

Three days in a row is an island of length 3, or date, date+1, date+2 as a join.

Prompt. Customers who placed at least one order on three consecutive calendar days.

Hint: Island COUNT(*) >= 3

Your T-SQL

WITH d AS (
  SELECT DISTINCT customer_id, CAST(order_date AS date) AS day FROM Orders
),
n AS (
  SELECT customer_id, day,
         DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY day), day) AS grp
  FROM d
)
SELECT DISTINCT customer_id
FROM n
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3;

The self-join version is fine for 3. Islands scale when they say “N days.”

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free