Interview › Gaps and islands

SQL interview: consecutive days / islands

An island is a run of days with no gap. Subtract a row number from the date and the run shares a key.

Prompt. For each customer, find streaks of consecutive order dates and the length of each streak.

Hint: DATEADD(day, −rn, day) as grp

Your T-SQL

WITH d AS (
  SELECT customer_id, CAST(order_date AS date) AS day,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY CAST(order_date AS date)) AS rn
  FROM Orders
  GROUP BY customer_id, CAST(order_date AS date)
)
SELECT customer_id, MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS days
FROM (
  SELECT *, DATEADD(day, -rn, day) AS grp FROM d
) x
GROUP BY customer_id, grp;

Two orders on one day are one day. Group the date first.

Then write it on a ticket

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

Clock in free