How do you carry the last non-NULL value forward when IGNORE NULLS is unavailable?

## The problem: last observation carried forward A time series where a value is recorded only when it changes — a sensor reading, a price, a status — leaves NULLs (or missing rows) in between. Reporting usually wants each row to show the most recent known value, an operation commonly called *last observation carried forward*. `LAG` alone does not do it: with the default null treatment, `RESPECT NULLS`, it returns whatever is in the target row, NULL included. A single NULL run defeats it, and `LAG(reading, 2)` is not a fix because the run length varies. ## The standard answer: null treatment SQL defines a null-treatment option on the offset and value functions: ```sql -- standard syntax; verify your engine implements it SELECT ts, reading, LAST_VALUE(reading) IGNORE NULLS OVER ( ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS filled FROM sensor_log; ``` With `IGNORE NULLS`, the function skips NULL rows when locating its target, so the last non-NULL value within the frame is returned. `LAG(reading) IGNORE NULLS` similarly walks back past NULLs to the nearest earlier value. `RESPECT NULLS` is the default and is what you get if you write nothing. The caveat matters: support is uneven across engines, and some widely used ones do not implement the option at all. Never assume it is available in a query that has to run on more than one product — check the engine's documentation. ## The portable trick ```sql SELECT ts, reading, FIRST_VALUE(reading) OVER (PARTITION BY grp ORDER BY ts) AS filled FROM ( SELECT ts, reading, COUNT(reading) OVER (ORDER BY ts) AS grp FROM sensor_log ) t; ``` The mechanism has two steps. **Step one — build the group id.** `COUNT(reading)` counts non-NULL arguments only. Used as a window function with `ORDER BY ts` and the default frame (start of partition through the current row), it returns how many actual readings have been seen so far. It increases by one on each row that carries a value, and stays exactly the same on every NULL row that follows. So every NULL run inherits the count of the value that precedes it: the value row and its trailing NULLs share one `grp`. **Step two — take the group's first value.** Within each `grp`, the first row by `ts` is the row that carried the value; `FIRST_VALUE(reading)` returns it for every row in the group. `FIRST_VALUE` is safe under the default frame because that frame always starts at the partition's first row — unlike `LAST_VALUE`, which would need an explicit frame. `MAX(reading) OVER (PARTITION BY grp)` also works, since the group contains exactly one non-NULL value and aggregates skip NULLs. `FIRST_VALUE` is clearer about intent, and it is the form that keeps working if the group could ever hold more than one value. ## Leading NULLs Rows before the first ever reading get `grp = 0` and their `FIRST_VALUE` is NULL. That is the honest result: no observation has occurred yet, so nothing can be carried forward. If a business rule says those rows should show a baseline, apply `COALESCE(filled, :baseline)` explicitly, so that the substitution is visible in the query rather than hidden in the mechanism. ## Partitioned series With several independent series, both windows need the same partitioning: ```sql COUNT(reading) OVER (PARTITION BY sensor_id ORDER BY ts) AS grp ... FIRST_VALUE(reading) OVER (PARTITION BY sensor_id, grp ORDER BY ts) ``` Omitting `sensor_id` from the second window is the common bug: group ids are only unique *within* a sensor, so different sensors' groups would merge. ## Carrying forward the other direction The mirror problem — fill each NULL with the *next* known value — reverses the ordering in the counting step and takes the group's value the same way. Be explicit about which direction the business wants; "the last known price" and "the price it later turned out to be" are different claims about the past. ## Missing rows versus NULL values This technique fills NULLs in rows that exist. If the series is missing rows entirely for some periods, no window function can invent them: generate the period rows first (a calendar table or a generated series), left-join the observations onto it, and then carry forward over the dense result. ## What interviewers are checking That you know `RESPECT NULLS` is the default and why that defeats naive `LAG`; that you can name `IGNORE NULLS` while being honest about portability; and that you can derive the counting trick rather than recite it, which requires understanding that `COUNT(col)` skips NULLs and that a windowed count is monotonic.