Problem Description
A table records employee information with three fields: id (sort key), name, and dept (department). The dept column contains NULL values that need to be forward filled—each NULL should be replaced with the most recent non-NULL value in the same column.
Source Data
Expected Result
For example, id=4–5 (dept=NULL): take the dept value Sales from id=3.
SQLazy Step-by-Step Implementation
Core idea: First create a logical grouping marker (grp) that increments each time dept is NOT NULL, grouping consecutive NULL rows with their preceding non-NULL row into the same partition. Then use grp as the partition key, taking the max dept value within each partition to forward fill the NULLs. This two-step strategy—create grouping marker first, then aggregate by partition—is SQLazy’s classic pattern for forward-fill problems.
[Click to run this example online]
The steps are explained below.
*Step 1: Sort by id in ascending order
*
sort id asc
Sort by id in ascending order to ensure records are processed in sequence; this is the prerequisite for subsequent grouping and filling.
Step 2: Create a logical grouping marker
compute if ((dept notnull) then 1 else 0) cum as grp
This is the most critical step. Use the computed column with the cum (running total) argument to cumulatively sum the condition if ((dept notnull) then 1 else 0). When dept is NOT NULL, it contributes 1 (starting a new group); when NULL, it contributes 0 (continuing the current group). The cumulative result grp increments by 1 each time dept is non-NULL, dividing the data into partitions.
Step 3: Forward fill by partition
compute dept max as filled_dept; partition grp
Within the grp partition, use the max aggregation to get the dept value. Each partition has only the first row’s dept as non-NULL, the rest are NULL. The MAX function automatically picks the non-NULL value, achieving forward fill.
The partition grp ensures that fills in different partitions do not interfere with each other. This replaces NULL values with the preceding non-NULL value in id order.
Step 4: Use derive to select the output fields
**
**Generated SQL
After confirming the logic of the above 4 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):
WITH t2 AS (
SELECT id, name, dept
, SUM(CASE
WHEN dept IS NOT NULL THEN 1
ELSE 0
END) OVER (ORDER BY CASE
WHEN id IS NULL THEN 1
ELSE 0
END, id ASC ROWS UNBOUNDED PRECEDING) AS grp
FROM forwardFill
)
t3 AS (
SELECT id, name, dept, grp
, MAX(dept) OVER (PARTITION BY grp) AS filled_dept
FROM t2
)
SELECT id, name, filled_dept AS dept
FROM t3
ORDER BY CASE
WHEN id IS NULL THEN 1
ELSE 0
END, id ASC
;
SQLazy lets you describe logic in business language instead of writing nested SQL queries. Step-by-step calculation breaks forward fill into independent steps, each verifiable independently. The cum (conditional running total) automatically generates group numbers—this is cleaner than SQL’s window function approach for forward-fill problems.
Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy
Top comments (1)
Nice walkthrough. I like that you explain the reasoning before showing the SQL. The “create a logical group first, then aggregate within that group” pattern is useful well beyond forward-fill problems—it also appears in sessionization, gap-and-island detection, and event stream analysis. Understanding the pattern is much more valuable than memorizing a specific query.