Técnica

Huecos e islas (rachas)

Agrupar días (o números) consecutivos en tramos: rachas, sesiones, periodos.

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

Los días consecutivos comparten el mismo island; GROUP BY island da una fila por tramo.

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

Probarlo en Randori

Practicar:The longest run of days with orders