@Daedalus can you take this one? Two things I'd like checked before anyone writes the full query: (1) is the usual fix for the nested-interval leak a running MAX(end_epoch) over intervals ordered by start, instead of LAG(end_epoch), and does that merge [100,500], [200,300], [400,600] into one span? (2) is it right that SQLite rejects COUNT(DISTINCT ...) as a window function, so the distinct count needs a correlated subquery or a self-join?
Short answers: (1) yes, if the frame is set explicitly. (2) yes for SQLite, and PostgreSQL 17 rejects it too. One correction: the LAG version doesn't actually let the 350 event through.
(1) Running MAX instead of LAG
WITH ordered AS (
SELECT start_epoch, end_epoch,
MAX(end_epoch) OVER (ORDER BY start_epoch, end_epoch
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_prev_end
FROM maintenance
),
islands AS (
SELECT start_epoch, end_epoch,
SUM(CASE WHEN max_prev_end IS NULL OR start_epoch > max_prev_end
THEN 1 ELSE 0 END)
OVER (ORDER BY start_epoch, end_epoch ROWS UNBOUNDED PRECEDING) AS island_id
FROM ordered
)
SELECT MIN(start_epoch) AS span_start, MAX(end_epoch) AS span_end
FROM islands GROUP BY island_id;
Traced by hand: the rows give max_prev_end NULL, 500, 500. 200 isn't greater than 500, and neither is 400, so all three rows land in one island: [100, 600]. LAG compares 400 with 300 (the inner interval's end), which starts a new island.
The frame ... AND 1 PRECEDING is required. With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That includes the row itself, so max_prev_end is always at least start_epoch and no new island ever starts.
About the leak: LAG gives [100,500] and [400,600]. Those spans overlap, but there's no gap between them, and 350 is still inside [100,500]. MIN/MAX over any grouping always covers every interval in the group. So the bug overlaps spans and overcounts total maintenance time, but no event slips through. If you only need to exclude events, you don't need to merge at all:
WITH kept AS (
SELECT e.* FROM events e
WHERE NOT EXISTS (SELECT 1 FROM maintenance m
WHERE e.ts_epoch BETWEEN m.start_epoch AND m.end_epoch)
)
SELECT k.id, k.ts_epoch,
(SELECT COUNT(DISTINCT k2.user_id) FROM kept k2
WHERE k2.ts_epoch BETWEEN k.ts_epoch - 604800 AND k.ts_epoch) AS users_7d
FROM kept k
ORDER BY k.ts_epoch, k.id;
(2) DISTINCT in a window aggregate
Yes. SQLite rejects it when the statement is prepared, and so does PostgreSQL. I'm going from memory on the exact error text, so running the query settles that. The usual fix is a correlated subquery like the one above. BETWEEN ... - 604800 AND ... matches RANGE 604800 PRECEDING, which includes both ends. On the sample data I get, by hand: 700 → 1, 750 → 2, both 800 rows → 3.
A side note on the peer-row complaint: with a time-based window, rows at the same timestamp should share one window. The two 800 rows getting 3 each is correct. Switching to ROWS to step one row at a time would make the result depend on how ties happen to be ordered. For the median, rank the values within each row's window, the same way as the subquery above, rather than counting the window's rows.
🏛 The council's answer (Claude, Gemini, Grok, Llama, Mistral · 82% sure)
The earlier answer is mostly right but incomplete: it never produced the rolling median and used a 7-day window where your expected output uses 100 seconds.
What it got right
- Running MAX over ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING replaces LAG and merges the nested intervals into [100, 600]. The explicit frame is needed. The default RANGE frame includes the current row, so no new island would ever start.
- The LAG version does not leak event 350. It gives [100,500] and [400,600]. They overlap, and 350 is inside the first, so the bug only overlaps spans. Your claimed false gap (300,400) does not happen.
- Merging is unnecessary for exclusion. NOT EXISTS against the raw maintenance rows is enough.
- DISTINCT inside a window aggregate is rejected by SQLite and PostgreSQL. A correlated subquery or a grouped join is the workaround.
- Peer rows at the same timestamp should share one RANGE window. Both ts=800 rows getting the same result is correct, not a bug.
- Its hand counts (1, 2, 3, 3) are also right for a 100 s window.
What it missed or got wrong
- It gave no median query. It only said to rank values within each row's window.
- It kept 604800 s although your expected output uses 100 s.
A full query (100 s window; use 604800 for 7 days)
WITH kept AS (
SELECT e.* FROM events e
WHERE NOT EXISTS (SELECT 1 FROM maintenance m
WHERE e.ts_epoch BETWEEN m.start_epoch AND m.end_epoch)
),
win AS (
SELECT k.id AS eid, w.user_id, w.latency_ms,
ROW_NUMBER() OVER (PARTITION BY k.id ORDER BY w.latency_ms, w.id) AS rn,
COUNT(*) OVER (PARTITION BY k.id) AS n
FROM kept k JOIN kept w ON w.ts_epoch BETWEEN k.ts_epoch-100 AND k.ts_epoch
),
agg AS (
SELECT eid, COUNT(DISTINCT user_id) AS users,
AVG(CASE WHEN rn IN ((n+1)/2,(n+2)/2) THEN latency_ms END) AS median_ms
FROM win GROUP BY eid
)
SELECT k.id, k.ts_epoch, a.users, a.median_ms
FROM kept k JOIN agg a ON a.eid = k.id
ORDER BY k.ts_epoch, k.id;
COUNT(DISTINCT) works here because it is a plain GROUP BY aggregate, not a window function. The ranks (n+1)/2 and (n+2)/2 are the same row when n is odd and the two middle rows when n is even, so AVG interpolates. Ties are stable because of the latency_ms, id ordering.
Hand-computed expected output (not run): id 2 has 1 user and median 20. Id 3 has 2 users and median 30. Ids 4 and 5 have 3 users and median 60.
Gemini offered a different median built from LIMIT/OFFSET subqueries. It is plausible but much more convoluted, and its correctness was not hand-checked. The query above is the clearer choice. Neither was executed on SQLite 3.53.0, so run it before relying on it. The join costs roughly events × window size, so index events(ts_epoch).
Where they agree
- Running MAX with ROWS ... AND 1 PRECEDING fixes nested-interval merging; LAG is wrong for nesting.
- LAG does not actually let event 350 through; it only produces overlapping spans.
- NOT EXISTS against raw maintenance rows is enough to exclude events; merging is unnecessary for that.
- SQLite and PostgreSQL reject COUNT(DISTINCT) as a window aggregate; correlated subqueries or grouped joins are the workaround.
- Peer rows at the same timestamp sharing one RANGE window is correct behavior.
Where they differ
- How to compute the rolling median
- Claude: Self-join with ROW_NUMBER partitioned per anchor event, then average the middle ranks; gives a complete query with hand-checked outputs.
- Gemini: Correlated subqueries using ORDER BY with LIMIT/OFFSET computed from window counts, averaged for the median.
- Grok: Mentions NTH_VALUE or ROW_NUMBER percentile in scalar subqueries but gives no query.
- Llama: Says to rank values within each window but gives no query.
- Mistral: Suggests a recursive CTE plus windowed subquery, with no query given.
- Better supported: Claude's ROW_NUMBER approach is the best supported: it is concrete, handles ties and even counts, and has hand-checked outputs. Gemini's is plausible but unverified.
What none of them could settle
- None of the queries was run on SQLite 3.53.0.
- The exact error text for DISTINCT in a window aggregate.
- Performance on large tables.
- Whether a single-pass solution without self-joins or correlated subqueries exists.
Sources they gave
- sqlite.org/windowfunctions.html
Each AI's own answer
- Claude · 85% sure: Your question has three parts, and the earlier answer covers the first two correctly but never gives the median. Check of the earlier answer - It is right that the LAG version does not leak event 350. LAG gives the islands [100,500] and [400,600]. They overlap, and 350 is inside [100,500]. Your 'false gap (300,400)' does not occur, and MIN/MAX over a group always covers every interval in it. The bug only produces overlapping spans. - Its running-MAX fix, MAX(end_epoch) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), is correct. The explicit frame is needed. The default RANGE…
- Gemini · 95% sure: To exclude events falling inside maintenance windows, you do not need to merge overlapping intervals at all. A NOT EXISTS subquery against the raw maintenance table filters out events in nested or overlapping intervals directly. If you do need merged maintenance spans separately, replace LAG(end_epoch) with MAX(end_epoch) OVER (ORDER BY start_epoch, end_epoch ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) to track the running maximum end timestamp. Because SQLite does not support COUNT(DISTINCT ... OVER ...) or window-based percentile functions over RANGE frames with peer rows, th…
- Grok · 80% sure: The prior answer is mostly right: running MAX (not LAG) with the ROWS frame fixes nesting for island merging; LAG splits on inner ends. For pure exclusion you can skip merging entirely and use NOT EXISTS. SQLite rejects COUNT(DISTINCT ...) OVER (RANGE ...), so correlated subqueries are the standard workaround. RANGE peers at identical timestamps is correct and desired; use that for the window. A single declarative query can combine the NOT EXISTS filter, then two scalar subqueries per row for the 7-day distinct count and median (via ordered window + NTH_VALUE or ROW_NUMBER percentile). No temp…
- Llama · 80% sure: To solve the problem of calculating a 7-day rolling median and a 7-day rolling distinct-user count over sparse event timestamps in SQLite, while excluding events that fall inside merged maintenance intervals, we need to address three main issues: merging overlapping maintenance intervals, handling duplicate timestamps, and calculating the rolling distinct-user count and median. The cleanest pure-SQL query to solve all three parts together involves using a combination of window functions, subqueries, and careful handling of interval merging and event exclusion.
- Mistral · 85% sure: The prior answer correctly identifies that (1) using MAX(end_epoch) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) fixes the nested-interval merging, producing a single [100, 600] span, and (2) SQLite (and PostgreSQL 17) do not support COUNT(DISTINCT) OVER (RANGE ...), requiring a correlated subquery. It also correctly notes that the LAG version does not leak event id=1 (350) because the split spans still cover it. However, it misses a clean single-query solution for the rolling median and distinct count after filtering events by merged maintenance intervals. The cor…
_AIs from different companies answered separately; Claude combined their answers. Check anything important: they can all be wrong together._
⚖️ Which AI's answer helped most? Your vote counts toward the Assembly's Verdict. Votes are public: everyone who can see this conversation sees who picked which.