A table signal(symbol, d, above_threshold boolean) holds one row per symbol per trading day. Return every maximal run of consecutive trading days where above_threshold is true, with its start date, end date and length. Give the SQL and explain why the standard trick works.

A table signal(symbol, d, above_threshold boolean) holds one row per symbol per trading day. Return every maximal run of consecutive trading days where above_threshold is true, with its start date, end date and length. Give the SQL and explain why the standard trick works.

Approach: Number the rows twice, once over all days and once over the true days only, then reason about what the difference of those two numbers does inside a run and at a break.

Group on the difference of two row numbers: SELECT symbol, min(d) AS start_d, max(d) AS end_d, count(*) AS len FROM (SELECT symbol, d, above_threshold, row_number() OVER (PARTITION BY symbol ORDER BY d) - row_number() OVER (PARTITION BY symbol, above_threshold ORDER BY d) AS grp FROM signal) t WHERE above_threshold GROUP BY symbol, grp. The gaps and islands trick works because both counters advance by one on every true row inside a run, so their difference is constant across that run, while a false day advances only the first counter and shifts the difference for every later run. The difference has no meaning as a number and is only ever used as a grouping key. Two conditions matter. The numbering must run over trading days rather than calendar days, so the source has to contain a row per trading day even when the value is false, otherwise a weekend merges two runs that a holiday separated. And the filter on above_threshold has to be applied after the numbering, because removing the false rows first destroys the shift that separates the runs.

Follow-up: How do you extend this to runs that tolerate a single false day inside them?

Key concepts: row_number, gaps and islands, grouping key, partition by symbol.