Tecnica

Gaps and islands (serie consecutive)

Raggruppa giorni (o numeri) consecutivi in serie: streak, sessioni, periodi.

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

I giorni consecutivi condividono lo stesso island; GROUP BY island dà una riga per serie.

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

Provalo in Randori

Esercitati:The longest run of days with orders