Collapse a Status Log Into Runs
Problem
A monitor writes at most one row per day into status_log(day, status), with day as a YYYY-MM-DD date and status either 'up' or 'down'. Some days have no row because the monitor was offline. Collapse the log into runs: a run is a stretch of consecutive calendar days that all have a row with the same status. Return status, start_day, end_day and days for every run, sorted by start_day. A missing day ends a run just like a change of status does.
Examples
Input: status_log = [("2026-09-01", "up"), ("2026-09-02", "up"), ("2026-09-03", "down"), ("2026-09-04", "up"), ("2026-09-05", "up")]
Output: [("up", "2026-09-01", "2026-09-02", 2), ("down", "2026-09-03", "2026-09-03", 1), ("up", "2026-09-04", "2026-09-05", 2)]Input: status_log = [("2026-09-01", "up"), ("2026-09-02", "up"), ("2026-09-04", "up")]
Output: [("up", "2026-09-01", "2026-09-02", 2), ("up", "2026-09-04", "2026-09-04", 1)]
Why: nothing was recorded on 09-03, so the up run breaks thereInput: status_log = [("2026-09-30", "down")]
Output: [("down", "2026-09-30", "2026-09-30", 1)]
Why: edge case, a single row is a run of one dayHints
0 / 3
Look at the rows of one status only, sorted by day. Inside a run, the day number and the row's position in that list both go up by exactly 1 per row.
So day number minus ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) stays constant inside a run and jumps whenever a day is skipped, whether the skipped day had the other status or no row at all.
Compute that difference as a group key with julianday(day) in a CTE, then GROUP BY status and the key, and take MIN(day), MAX(day) and COUNT(*) for each group.
Solution
This is the gaps-and-islands pattern. Number the rows of each status in day order with ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) and subtract that number from the day number. Within a run both values climb by one per row, so the difference stays the same; the moment a calendar day is skipped for that status, the day number jumps by more than the row number and the difference changes. A skipped day can be a day with the other status or a day with no row, and the one formula catches both, which is why the partition by status is essential. Grouping by status and that difference yields one group per run, and MIN, MAX and COUNT(*) describe it. A version with LAG that flags each row where a run starts and then sums the flags works too, but needs one more step. The window sorts each status once, O(n log n), and the grouping is linear.
import sqlite3
QUERY = """
WITH keyed AS (
SELECT day, status,
julianday(day)
- ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) AS grp
FROM status_log
)
SELECT status, MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS days
FROM keyed
GROUP BY status, grp
ORDER BY start_day
"""
def run(rows):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE status_log (day TEXT PRIMARY KEY, status TEXT)")
db.executemany("INSERT INTO status_log VALUES (?, ?)", rows)
return db.execute(QUERY).fetchall()
print(run([("2026-09-01", "up"), ("2026-09-02", "up"), ("2026-09-03", "down"), ("2026-09-04", "up"), ("2026-09-05", "up")])) # -> [('up', '2026-09-01', '2026-09-02', 2), ('down', '2026-09-03', '2026-09-03', 1), ('up', '2026-09-04', '2026-09-05', 2)]
print(run([("2026-09-01", "up"), ("2026-09-02", "up"), ("2026-09-04", "up")])) # -> [('up', '2026-09-01', '2026-09-02', 2), ('up', '2026-09-04', '2026-09-04', 1)]
print(run([("2026-09-30", "down")])) # -> [('down', '2026-09-30', '2026-09-30', 1)]Stuck on the idea rather than the code? Window Functions covers it.