Muster

Gaps and Islands (Serien)

Aufeinanderfolgende Tage (oder Zahlen) zu Läufen gruppieren: Serien, Sitzungen, Zeiträume.

SELECT day - CAST(ROW_NUMBER() OVER (ORDER BY day) AS INTEGER) AS island, day
FROM days;

Aufeinanderfolgende Tage teilen dieselbe island; GROUP BY island liefert eine Zeile pro Lauf.

WITH days AS (SELECT DISTINCT CAST(ordered_at AS DATE) AS day FROM orders),
grouped AS (
  SELECT day, day - CAST(ROW_NUMBER() OVER (ORDER BY day) AS INTEGER) AS island
  FROM days
)
SELECT MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS days_in_a_row
FROM grouped
GROUP BY island
ORDER BY days_in_a_row DESC, first_day
LIMIT 3;

first_day	last_day	days_in_a_row
2025-05-18	2025-08-14	89
2024-07-23	2024-10-09	79
2025-09-11	2025-11-11	62

In Randori ausprobieren

Üben:The longest run of days with orders