Days in a Row in Python

An interviewer asks: “What is the longest run of days in a row on which the shop got an order?” This is the classic gaps and islands question. The trick: number the days in date order with ROW_NUMBER() and subtract that number from the day. Inside a run both go up by 1, so the difference stays the same; after a missing day it jumps.

On paper, with days of one month:

day | row | day - row
10  | 1   | 9
11  | 2   | 9
12  | 3   | 9
15  | 4   | 11
16  | 5   | 11

That makes two runs: key 9 (the 10th to the 12th) and key 11 (the 15th and 16th). The key means nothing by itself; it is just the same inside a run. GROUP BY the key then gives one group per run, and MIN, MAX and COUNT(*) give its first day, last day and length.

Days as numbers: subtracting text dates doesn't count days, and the day of the month restarts every month. julianday() turns a date into a count of days, so the day after May 31 is exactly one more:

SELECT julianday('2026-06-01') - julianday('2026-05-31');   -- 1.0

The shop's orders are on 2026-03-02, 03-15, 04-05, 04-18, 05-07, 05-21 and 05-30. For this lesson, a hidden setup adds eight more orders, one per day, on 05-31, 06-01, 06-02, 06-03, 06-05, 06-06, 06-07 and 06-09. Your query must print the longest run:

first_day | last_day | days
2026-05-30 | 2026-06-03 | 5
(1 row)

Your task: in query, write a WITH query named runs that gives each order's ordered date and julianday(ordered) minus its ROW_NUMBER() in date order, named run_id. Below it, read runs, group by run_id, select each run's first_day, last_day and number of days, sort the longest run first and keep only one row.

Assignment
Follow the Coding Tutorial to build the longest-run query, one clause per line.

Hint
The trick needs exactly one row per day, and here every day has one order. If a day could have two orders, you would number the distinct days instead. Each clause is its own string inside the brackets of query; end each one with a space.