Technique

Îlots (séries consécutives)

Regrouper des jours (ou nombres) consécutifs en séries : séries de connexions, sessions, périodes.

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

Les jours consécutifs ont le même island ; GROUP BY island donne une ligne par série.

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

L’essayer en Randori

Pratiquer :La plus longue série de jours avec commandes