Pattern

Gaps and islands (streaks)

Group consecutive days (or numbers) into runs: streaks, sessions, periods.

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

Consecutive days share the same island; GROUP BY island gives one row per run.

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

Try it in Randori

Practise:The longest run of days with orders