NULL — Learn NOT IN traps, aggregate NULLs, JOIN conversion, NULLIF, and division by zero in Practice

QUESTION 6

The NOT IN and NULL Subquery Trap — One NULL in a subquery makes every row disappear as UNKNOWN

NOT IN / NOT EXISTSCorrelated subqueryUNKNOWN excludes allNULL-safe comparison

Background ▼

col NOT IN (a, b, c) is internally equivalent to col <> a AND col <> b AND col <> c. If the list contains even one NULL, col <> NULL → UNKNOWN, and UNKNOWN propagates through the AND chain, so all rows are excluded.

-- When the list contains NULL, UNKNOWN propagates and all rows are excluded
col NOT IN (10, 30, NULL)
-- Internal expansion: col <> 10 AND col <> 30 AND col <> NULL (← UNKNOWN)

-- Solution 1: NOT EXISTS (unaffected by NULL)
WHERE NOT EXISTS (SELECT 1 FROM sub WHERE sub.col = t.col)

-- Solution 2: exclude NULL from the subquery
WHERE col NOT IN (SELECT col FROM sub WHERE col IS NOT NULL)

Principle of NULL-safe exclusion: With NOT IN and a subquery, always check whether the subquery can return NULL. NOT EXISTS checks whether the correlated subquery returns no rows, so it is unaffected by NULL. This is a "time bomb" that makes every row disappear the moment NULL enters the data.

Problem ▼

From the employees table, extract employees in departments that are not managed (their dept_id does not exist in managed_depts). Return the columns emp_id, name, dept_id, ordered by emp_id ascending.

Tables used

► employees (6 rows)

emp_id	name	dept_id
1	Tanaka	10
2	Suzuki	20
3	Sato	30
4	Ito	40
5	Yamada	NULL
6	Takahashi	20

► managed_depts (3 rows)

dept_id	dept_name
10	Sales
30	HR
NULL	Undefined

Note: managed_depts.dept_id contains NULL (an undefined department). Writing NOT IN (SELECT dept_id FROM managed_depts) lets NULL enter the list and makes all rows disappear. Use NOT EXISTS for a NULL-safe result.

Expected Output ▼

Expected output (emp_id ascending):

emp_id	name	dept_id
2	Suzuki	20
4	Ito	40
5	Yamada	NULL
6	Takahashi	20

Note: dept_id=10 (Tanaka) and dept_id=30 (Sato) are managed departments and are excluded. With NOT IN, the NULL in managed_depts makes UNKNOWN exclude all 6 rows, producing 0 rows.

Model Answer ▼

SELECT
  emp_id, name, dept_id
FROM   employees
WHERE  NOT EXISTS (
    SELECT 1
    FROM   managed_depts
    WHERE  managed_depts.dept_id = employees.dept_id  -- correlated condition for each row
)
ORDER BY emp_id;

/*
  Execution order:
  1. FROM: read employees (6 rows)
  2. WHERE NOT EXISTS: evaluate the correlated subquery for each row
  3. ORDER BY: output in ascending emp_id order
  */

Explanation (table transitions & key points) ▼

SELECT
  emp_id, name, dept_id
FROM   employees
WHERE  NOT EXISTS (
    SELECT 1
    FROM   managed_depts
    WHERE  managed_depts.dept_id = employees.dept_id
)
ORDER BY emp_id;

LEGEND ▼

Rows read / loaded

① FROM employees (6 rows) / managed_depts (3 rows) ▼

FROM employees / managed_deptsRead 6 rows from employees. managed_depts has 3 rows: dept_id=10 (Sales), 30 (HR), and NULL (Undefined). The NULL in managed_depts.dept_id creates the NOT IN trap.

1 / 4

emp_id	name	dept_id	managed_depts.dept_id (subquery values)
1	Tanaka	10	(10, 30, NULL)
2	Suzuki	20	(10, 30, NULL)
3	Sato	30	(10, 30, NULL)
4	Ito	40	(10, 30, NULL)
5	Yamada	NULL	(10, 30, NULL)
6	Takahashi	20	(10, 30, NULL)

employees: 6 rows / managed_depts: (10, 30, NULL) — 3 rows including NULL

LEARNING POINTS

If a NOT IN subquery returns NULL, every row is excluded: col NOT IN (a, b, NULL) expands to col <> a AND col <> b AND col <> NULL. col <> NULL is UNKNOWN → the entire AND chain is UNKNOWN → WHERE treats it as FALSE and excludes every row. As long as the subquery column can be NULL, even a query that looks correct becomes a "time bomb" that returns 0 rows.

NOT EXISTS is a safe alternative unaffected by NULL: NOT EXISTS returns TRUE when the correlated subquery returns no rows. When col is NULL in WHERE sub.col = t.col, NULL = NULL is UNKNOWN → the subquery returns no row → EXISTS=FALSE → NOT EXISTS=TRUE → the row is correctly included. NOT EXISTS is recommended for NULL-safe existence checks.

Alternative: exclude NULL from the subquery with NOT IN (SELECT col FROM t WHERE col IS NOT NULL): Adding WHERE col IS NOT NULL to the subquery removes NULL from the list, making NOT IN safe. However, note that this also changes how rows whose own col is NULL (Yamada, emp_id=5) are handled. Choose NOT EXISTS or NOT IN + IS NOT NULL according to the business logic.

ANTI-PATTERNS

Use NOT IN (SELECT col FROM subquery) as an exclusion condition without further thought: As long as the subquery can return NULL, NOT IN evaluates to UNKNOWN for every row and excludes them. Use NOT IN only when it is guaranteed that the subquery returns no NULL; otherwise use NOT EXISTS or a NOT IN + IS NOT NULL filter.

Misread "0 rows" as "no data exists" and waste time debugging: A NOT IN + NULL bug can suddenly return 0 rows after the data grows and a NULL row enters, so it is discovered late. When using NOT IN, check the NULL count with SELECT COUNT(*) FROM subquery WHERE col IS NULL, or rewrite it with NOT EXISTS.

Practical column: Choosing between NOT IN and NOT EXISTS, and how often the issue occurs

The NOT IN + NULL bug is one of the most common "silent failure" NULL bugs. Common scenarios include: ① an exclusion-list (blacklist) table designed in a way that allows NULL (for example, a row where ban_list.user_id is NULL), and ② using an intermediate table produced by a LEFT JOIN as a NOT IN subquery (a LEFT JOIN result can contain NULL). In dbt and data pipelines, NOT EXISTS is often easier for the optimizer to optimize and is also recommended from a performance perspective. Teams can reduce risk by requiring code review to confirm IS NOT NULL in the subquery whenever NOT IN is used.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 7

The Aggregate Function NULL Trap — COUNT(*) / COUNT(column) / AVG silently shrink the denominator

COUNT / AVGAggregate NULL exclusionShrinking denominatorCOALESCE correction

Background ▼

Aggregate functions automatically ignore NULL. Only COUNT(*) counts every row; COUNT(col), SUM(col), and AVG(col) exclude rows where the column is NULL. In particular, AVG's shrinking denominator creates a quiet bug in which "the average for representatives with results" is reported as "the average for everyone."

-- Different behavior
COUNT(*)                  -- count all rows (including NULL)
COUNT(amount)             -- count non-NULL rows only

-- AVG excludes NULL and shrinks the denominator (equivalent to SUM / COUNT(amount))
AVG(amount)

-- Average for everyone, treating NULL as 0
AVG(COALESCE(amount, 0))

AVG's "invisible denominator shrinkage": When AVG excludes amount=NULL (no result), the average for representatives with results is reported as the average for all representatives. If NULL means "0 (no result)," correct it with COALESCE and use true_avg; if NULL means "missing (cannot be aggregated)," keep AVG as-is or display the count explicitly.

Problem ▼

From the sales_results table, calculate the headcount, number of representatives with results, average sales for representatives with results, and average for everyone including 0 results by department (dept). Return the columns dept, head_count, active_reps, avg_amount, true_avg, ordered by dept ascending.

Tables used

► sales_results (8 rows)

rep_id	dept	amount
1	Tokyo	250000
2	Tokyo	300000
3	Tokyo	NULL
4	Tokyo	NULL
5	Osaka	150000
6	Osaka	200000
7	Osaka	180000
8	Osaka	NULL

Note: rep_id=3,4 (Tokyo) and rep_id=8 (Osaka) have amount=NULL (no results this month). COUNT(*) counts everyone, while COUNT(amount) counts only representatives with results. Pay attention to how far avg_amount and true_avg diverge.

Expected Output ▼

Expected output (dept ascending):

dept	head_count	active_reps	avg_amount	true_avg
Osaka	4	3	176667	132500
Tokyo	4	2	275000	137500

Note: Osaka: SUM=530000, AVG (denominator 3)=176667, true_avg (denominator 4)=132500. Tokyo: SUM=550000, AVG (denominator 2)=275000, true_avg (denominator 4)=137500. The gap between avg_amount and true_avg shows the effect of excluding NULL.

Model Answer ▼

SELECT
  dept,
  COUNT(*)                          AS head_count,   -- all rows (including NULL)
  COUNT(amount)                     AS active_reps,  -- excludes NULL
  ROUND(AVG(amount))               AS avg_amount,   -- average excluding NULL (shrinking denominator)
  ROUND(AVG(COALESCE(amount, 0)))  AS true_avg      -- average for everyone (corrected to 0)
FROM   sales_results
GROUP BY dept
ORDER BY dept;

/*
  Execution order:
  1. FROM      → read sales_results
  2. GROUP BY  → split into 2 groups
  3. SELECT    → COUNT and AVG (NULL exclusion / COALESCE correction)
  4. ORDER BY  → output in ascending dept order
  */

Explanation (table transitions & key points) ▼

SELECT
  dept,
  COUNT(*) AS head_count,
  COUNT(amount) AS active_reps,
  ROUND(AVG(amount)) AS avg_amount,
  ROUND(AVG(COALESCE(amount, 0))) AS true_avg
FROM   sales_results
GROUP BY dept
ORDER BY dept;

LEGEND ▼

Rows read / loaded

① FROM sales_results (8 rows) ▼

FROM sales_resultsRead 8 rows from sales_results. Tokyo rep_id=3,4 and Osaka rep_id=8 have amount=NULL (no results this month). How these rows affect COUNT and AVG is the key point of this question.

1 / 5

rep_id	dept	amount
1	Tokyo	250000
2	Tokyo	300000
3	Tokyo	NULL
4	Tokyo	NULL
5	Osaka	150000
6	Osaka	200000
7	Osaka	180000
8	Osaka	NULL

8 rows read (NULL: 2 Tokyo rows, 1 Osaka row)

LEARNING POINTS

COUNT(*) counts every row, while COUNT(col) counts only non-NULL rows: COUNT(*) counts all rows, including NULL. COUNT(col) excludes NULL rows. This difference is an important indicator of missing records. Use COUNT(*) - COUNT(col) to check how many rows are NULL.

AVG(col) uses a denominator that excludes NULL — the denominator shrinks: AVG(amount) is equivalent to SUM(amount) / COUNT(amount), so the number of rows after NULL exclusion becomes the denominator. When NULL means "0 results," AVG creates the appearance of high performance. Compare it with true_avg, which includes everyone by using AVG(COALESCE(amount, 0)).

Calculate AVG yourself with a SUM and COUNT combination: To control NULL handling completely, use SUM(amount) / NULLIF(COUNT(*), 0) to calculate an average with everyone as the denominator while preventing division by zero. NULLIF(COUNT(*), 0) returns NULL for a zero-row group instead of raising an error, so it is safe.

ANTI-PATTERNS

Mislabel AVG(amount) as "the average for all representatives" on a dashboard: If NULL means "no results = 0," using AVG(amount) reports the average for representatives with results as the average for all representatives. Always display head_count and active_reps together so readers can understand the context.

Confuse COUNT(*) with COUNT(col) when building a report: If you want to write "sales for n of 8 representatives," COUNT(amount) returns 5 because NULL rows are excluded. Use COUNT(*) for the size of the population that forms the denominator and COUNT(col) for the number of valid values.

Practical column: How to handle NULL in KPI calculations — define "missing" versus "zero"

One of the most important design decisions in analytical SQL is to clarify whether NULL means missing data or an intentional zero. If it means missing (cannot be measured), automatic exclusion by AVG or SUM is the correct behavior. If it means an intentional zero (no results), correct it with COALESCE before aggregation. A common dbt staging-model pattern is to apply COALESCE(amount, 0) AS amount early so downstream models do not need to think about NULL. Regularly checking COUNT(*) - COUNT(col) as a data-quality monitoring metric also helps detect NULL contamination early.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 8

LEFT JOIN ON-Clause Filters — Preserve non-matching rows as NULL

LEFT JOINON-clause filterCondition evaluation during joinPreserve non-matching rows as NULL

Background ▼

The purpose of a LEFT JOIN is to preserve every row from the left table. If you want to specify a condition on right-table columns (such as orders above a certain amount) while still preserving left-table rows that do not meet the condition as NULL, write the filter condition in the ON clause.

-- Writing the condition in ON preserves non-matching rows as NULL
FROM customers c
LEFT JOIN orders o
       ON c.customer_id = o.customer_id
      AND o.amount > 20000

When the ON clause is evaluated: The ON clause is evaluated during the join. Rows that do not satisfy the condition are not joined, and the left-table row is retained with NULLs on the right. To filter right-table rows while displaying non-matching rows as NULL, put the condition in the ON clause.

Problem ▼

For every customer in the customers table, display the maximum amount of orders above 20,000 yen (large_order). For customers with no large order or no orders at all, display NULL for large_order. Return the columns customer_id, customer_name, large_order, ordered by customer_id ascending.

Tables used

► customers (4 rows)

customer_id	customer_name
1	Tanaka Trading
2	Suzuki Trading
3	Sato Trading
4	New Customer

► orders (7 rows)

order_id	customer_id	amount
1	1	10000
2	1	35000
3	1	40000
4	2	28000
5	2	8000
6	3	5000
7	3	12000

Note: customer 4 (New Customer) has no orders. Customer 3 (Sato Trading) has orders, but none above 20,000 yen. Put the filter condition in the ON clause to preserve customers 3 and 4 in the result.

Expected Output ▼

Expected output (customer_id ascending):

customer_id	customer_name	large_order
1	Tanaka Trading	40000
2	Suzuki Trading	28000
3	Sato Trading	NULL
4	New Customer	NULL

The ON-clause filter preserves non-matching customers 3 and 4 as NULL, so all 4 customers are correctly returned.

Model Answer ▼

SELECT
  c.customer_id,
  c.customer_name,
  MAX(o.amount)  AS large_order
FROM       customers c
LEFT JOIN  orders o
        ON  c.customer_id = o.customer_id
       AND o.amount > 20000          -- filter in ON (non-matching customers are kept as NULL)
GROUP BY   c.customer_id, c.customer_name
ORDER BY   c.customer_id;

/*
  Execution order:
  1. FROM customers                     → read the table
  2. LEFT JOIN ... ON ... AND o.amount  → conditional join (non-matches kept as NULL)
  3. GROUP BY                           → group by customer
  4. SELECT MAX                         → aggregate the maximum (NULL for non-matches)
  5. ORDER BY                           → output in ascending customer_id order
  */

Explanation (table transitions & key points) ▼

SELECT
  c.customer_id,
  c.customer_name,
  MAX(o.amount) AS large_order
FROM       customers c
LEFT JOIN  orders o
        ON  c.customer_id = o.customer_id
       AND o.amount > 20000GROUP BY   c.customer_id, c.customer_name
ORDER BY   c.customer_id;

LEGEND ▼

Rows read / loaded

① FROM customers (4 rows) / orders (7 rows) ▼

FROM customers c / orders oRead 4 rows from customers and 7 rows from orders. Customer 1 (Tanaka Trading) has two large orders, customer 3 (Sato Trading) has orders but all are 20,000 yen or less, and customer 4 has no orders.

1 / 5

c.customer_id	c.customer_name	o.order_id	o.amount
1	Tanaka Trading	1	10000
1	Tanaka Trading	2	35000
1	Tanaka Trading	3	40000
2	Suzuki Trading	4	28000
2	Suzuki Trading	5	8000
3	Sato Trading	6	5000
3	Sato Trading	7	12000
4	New Customer	(none)	(none)

customers: 4 rows / orders: 7 rows

LEARNING POINTS

ON-clause filters are evaluated during the join, and non-matching rows are retained as NULL: A condition written in the ON clause is evaluated as part of the join. Right-table rows that do not satisfy it are not joined, and the left-table row is retained with NULLs. This precisely meets the requirement "show every customer, not only customers with an order above 20,000 yen, and show the amount when a qualifying order exists."

Flexible joins with multiple conditions: A LEFT JOIN ON clause can include not only an equality join on the key (c.customer_id = o.customer_id) but also a condition that narrows the target data (o.amount > 20000). This combines joining and filtering cleanly in one step without first creating an intermediate table or subquery.

ANTI-PATTERNS

Over-filter the data in a subquery before joining: Even though the filter can be written in ON, some queries use a subquery such as LEFT JOIN (SELECT * FROM orders WHERE amount > 20000) o. Using the ON-clause condition keeps the query simpler and easier to read.

Practical column: Using ON-clause filters and performance

ON-clause filtering is frequently used in dashboards and reports that need to use every row of a master table as the baseline, join only transactions meeting a condition, and then aggregate them. For example, you might join only the current month's sales to a complete product list (ON p.id = s.product_id AND s.month = '2024-10'). Using the ON clause improves readability and can also make it easier for the database optimizer to choose an efficient join plan.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 9

Turn Division by Zero into NULL with NULLIF — Use NULL as a Defensive Tool

NULLIFPrevent division by zeroUse NULL as a valid resultCombining with COALESCE

Background ▼

NULLIF(expr, value) returns NULL when expr = value; otherwise it returns expr unchanged. Using it to prevent division by zero safely returns NULL instead of raising an error.

-- Basic: NULL when a = b; otherwise return a
NULLIF(a, b)

-- Prevent division by zero (return NULL when units=0, making the result NULL)
revenue / NULLIF(units, 0)

-- Return 0 when division by zero would occur
COALESCE(revenue / NULLIF(units, 0), 0)

Use NULL as a tool: NULL means "unknown," but NULLIF can actively convert zero to NULL to safely skip division by zero. This intentionally uses NULL propagation so that the result becomes NULL rather than causing an error.

Problem ▼

For each daily campaign record in the campaign_daily table, calculate and output clicks, conversion rate (conv_rate = conversions / clicks), and revenue per click (rev_per_click = revenue / clicks). For rows where clicks is 0, display NULL for conv_rate and rev_per_click (prevent division by zero with NULLIF). Return the columns date, campaign, clicks, conv_rate, rev_per_click, ordered by date and campaign ascending.

Tables used

► campaign_daily (6 rows)

date	campaign	clicks	conversions	revenue
2024-03-01	CP_A	1000	50	200000
2024-03-01	CP_B	0	0	0
2024-03-02	CP_A	800	32	160000
2024-03-02	CP_B	500	20	100000
2024-03-03	CP_A	0	0	0
2024-03-03	CP_B	600	24	120000

Note: CP_B (2024-03-01) and CP_A (2024-03-03) have clicks=0 (days with no delivery). Because dividing by 0 raises an error, convert zero to NULL with NULLIF(clicks, 0) before division. Round conv_rate and rev_per_click to 4 decimal places.

Expected Output ▼

Expected output (date and campaign ascending):

date	campaign	clicks	conv_rate	rev_per_click
2024-03-01	CP_A	1000	0.0500	200.0000
2024-03-01	CP_B	0	NULL	NULL
2024-03-02	CP_A	800	0.0400	200.0000
2024-03-02	CP_B	500	0.0400	200.0000
2024-03-03	CP_A	0	NULL	NULL
2024-03-03	CP_B	600	0.0400	200.0000

Model Answer ▼

SELECT
  date,
  campaign,
  clicks,
  ROUND(conversions::numeric / NULLIF(clicks, 0), 4) AS conv_rate,    -- turn clicks=0 into NULL to avoid a division error
  ROUND(revenue::numeric    / NULLIF(clicks, 0), 4) AS rev_per_click
FROM   campaign_daily
ORDER BY date, campaign;

/*
  Execution order:
  1. FROM      → read campaign_daily
  2. SELECT    → prevent division by zero with NULLIF and round
  3. ORDER BY  → output in ascending date and campaign order
  */

Explanation (table transitions & key points) ▼

SELECT
  date, campaign, clicks,
  ROUND(
    conversions::numeric / NULLIF(clicks, 0),
    4
  ) AS conv_rate,
  ROUND(
    revenue::numeric    / NULLIF(clicks, 0),
    4
  ) AS rev_per_click
FROM   campaign_daily
ORDER BY date, campaign;

LEGEND ▼

Rows read / loaded

① FROM campaign_daily (6 rows) ▼

FROM campaign_dailyRead 6 rows from campaign_daily. CP_B (2024-03-01) and CP_A (2024-03-03) have clicks=0 (days with no delivery). Direct division for these rows would raise a division-by-zero error.

1 / 4

date	campaign	clicks	conversions	revenue
2024-03-01	CP_A	1000	50	200000
2024-03-01	CP_B	0	0	0
2024-03-02	CP_A	800	32	160000
2024-03-02	CP_B	500	20	100000
2024-03-03	CP_A	0	0	0
2024-03-03	CP_B	600	24	120000

6 rows read (2 rows have clicks=0 → direct division would error)

LEARNING POINTS

NULLIF(expr, value) returns NULL when expr = value: NULLIF(a, b) is equivalent to CASE WHEN a = b THEN NULL ELSE a END. The most common use case is x / NULLIF(y, 0) to prevent division by zero. When y=0, it returns NULL, NULL propagates through the division, and the output is NULL instead of an error.

Combine it with COALESCE to return a default value: COALESCE(x / NULLIF(y, 0), 0) returns 0 when y=0. Use NULLIF alone when you want to output NULL, and combine it with COALESCE when you want to convert the result to a specific default value. Business logic determines whether NULL or 0 is semantically correct.

PostgreSQL ROUND(x, n) requires the numeric type: PostgreSQL does not directly support ROUND(float, n), so cast explicitly with ROUND(x::numeric, n). Casting an integer column to numeric before division (for example, conversions::numeric / NULLIF(clicks, 0)) preserves precision for decimal calculations.

ANTI-PATTERNS

Write division-by-zero handling as a verbose CASE WHEN: CASE WHEN clicks = 0 THEN NULL ELSE conversions::numeric / clicks END works, but it is verbose. conversions::numeric / NULLIF(clicks, 0) is shorter and communicates the intent clearly, and it works in major DBMSs such as PostgreSQL, MySQL, BigQuery, and Snowflake.

Filter with WHERE clicks > 0 as a division-by-zero workaround: Filtering out zero-click rows with WHERE creates a separate problem: data for days with no delivery disappears. When the requirement is to show conv_rate as NULL (not calculable) on days with no delivery, NULLIF—not WHERE exclusion—is the correct solution.

Practical column: Uses for NULLIF beyond division by zero

NULLIF is useful for more than preventing division by zero. ① Treat an empty string as NULL: use NULLIF(col, '') to convert an empty string to NULL so later processing can use COALESCE or IS NULL (common when cleaning data after CSV import). ② Treat a sentinel value as NULL: use NULLIF(error_code, -1) to convert error value -1 to NULL and aggregate only normal values. ③ Calculate a change ratio: month-over-month change with ROUND((current - prev) / NULLIF(prev, 0), 4) appears in Q10. NULLIF is a powerful tool that expresses the design intent "treat this value as NULL" in one function.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 10

Window Functions and NULL Propagation — Boundary NULL in LAG and chained NULLs in month-over-month growth

LAG / window functionsNULL propagation chainCTE patternMonth-over-month / time-series analysis

Background ▼

LAG(col, offset, default) returns the value from a previous row, but NULL can arise for two different reasons: the column itself is NULL, or the first row has no previous row (a boundary NULL). These causes must be distinguished, and the design must prevent unintended NULL propagation.

-- ① Prevent boundary NULL only (the first row becomes 0, but a NULL in the previous row remains NULL)
LAG(revenue, 1, 0) OVER (ORDER BY month)

-- ② Prevent both boundary NULL and NULL in the data itself (practical pattern)
LAG(COALESCE(revenue, 0), 1, 0) OVER (ORDER BY month)

Defend against both kinds of NULL: The third argument of LAG is used only when the previous row does not exist, such as for the first row. It cannot prevent NULL when the previous row exists but its value is NULL; in that case, NULL is returned unchanged. In practice, protect the target column with COALESCE and also provide LAG's third argument to obtain a reliable default value (such as 0) in every situation.

Problem ▼

From the monthly_revenue table, calculate monthly revenue (revenue), previous-month revenue (prev_revenue), and month-over-month growth rate (mom_growth).
When revenue is NULL because data was not collected, treat it as 0. When using LAG() to obtain the previous month's revenue, also return 0 for the first month and when the previous month's revenue is 0 (including a value corrected from NULL).
Calculate mom_growth = ROUND((current month - previous month) / previous month, 4), and display NULL when the previous month is 0 and the calculation cannot be performed (division by zero). Return month, revenue, prev_revenue, mom_growth, ordered by month ascending.

Tables used

► monthly_revenue (6 rows)

month	revenue
2024-01	1000000
2024-02	1200000
2024-03	NULL
2024-04	1100000
2024-05	1400000
2024-06	1350000

Note: treat revenue=NULL in 2024-03 (data not collected) as 0. For the first month, such as 2024-01, and when the previous month is 0 (2024-04), use NULLIF to safely output NULL instead of raising a division-by-zero error.

Expected Output ▼

Expected output (month ascending):

month	revenue	prev_revenue	mom_growth
2024-01	1000000	0	NULL
2024-02	1200000	1000000	0.2000
2024-03	0	1200000	-1.0000
2024-04	1100000	0	NULL
2024-05	1400000	1100000	0.2727
2024-06	1350000	1400000	-0.0357

Note: in 2024-03, revenue becomes 0 and month-over-month growth is -1.0000 (-100%). In 2024-04, prev_revenue is 0, so division by zero is avoided and mom_growth is NULL.

Model Answer ▼

WITH lagged AS (
  SELECT
    month,
    COALESCE(revenue, 0)  AS revenue,  -- correct an uncollected month to 0
    LAG(COALESCE(revenue, 0), 1, 0) OVER (ORDER BY month) AS prev_revenue  -- get 0 for boundary and previous-month NULL
  FROM   monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue)::numeric / NULLIF(prev_revenue, 0), 4  -- NULL on division by zero
  ) AS mom_growth
FROM   lagged
ORDER BY month;

/*
  Execution order:
  1. CTE lagged
  2. Outer SELECT
  3. ORDER BY: output in ascending month order
  */

Explanation (table transitions & key points) ▼

WITH lagged AS (
  SELECT
    month,
    COALESCE(revenue, 0) AS revenue,
    LAG(COALESCE(revenue, 0), 1, 0) OVER (ORDER BY month) AS prev_revenue
  FROM   monthly_revenue
)
SELECT
  month, revenue, prev_revenue,
  ROUND(
    (revenue - prev_revenue)::numeric
    / NULLIF(prev_revenue, 0),
    4
  ) AS mom_growth
FROM   lagged
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM monthly_revenue (6 rows) ▼

FROM monthly_revenueRead 6 rows from monthly_revenue. Revenue is NULL in 2024-03 because data was not collected. We will actively control this NULL so it does not cause unintended propagation.

1 / 5

month	revenue
2024-01	1000000
2024-02	1200000
2024-03	NULL
2024-04	1100000
2024-05	1400000
2024-06	1350000

6 rows read (2024-03 revenue=NULL)

LEARNING POINTS

Actively control NULL instead of leaving it unchecked (defensive programming): When NULL enters a calculation, NULL propagation can make the entire result NULL unexpectedly. To prevent this, correct values to a clear default (0 here) with COALESCE before calculating; this is extremely important in practice.

Defend against LAG's boundary NULL and value NULL in two layers: LAG's third argument (default) prevents the boundary NULL returned when a previous row does not exist, such as for the first row. It does not help when the previous row's value itself is NULL. Writing LAG(COALESCE(revenue, 0), 1, 0) ensures that both a previous month with NULL (corrected to 0 by COALESCE) and the first month (0 supplied as the third argument) safely produce 0.

Prevent only division by zero with NULLIF (a two-layer defensive wall): If you correct NULL to 0 and then divide directly, the query stops with a division-by-zero error. To avoid this, apply NULLIF(prev_revenue, 0) only to the denominator, intentionally converting 0 to NULL and outputting a safe, non-calculable result as NULL.

ANTI-PATTERNS

Pass NULL directly into a calculation because you assume "NULL is ignored": Unlike SUM and AVG, arithmetic operations (+, -, *, /) do not ignore NULL. If even one operand is NULL, the result is always NULL. In time-series analysis and KPI calculations, use COALESCE inside the CTE to cleanse NULL before calculating.

Use months where revenue=NULL without forward-filling them (an alternative approach): This question shows the case where the business requirement is to treat NULL as 0, but in practice it is also common to fill a missing month with the previous month's value (forward fill). BigQuery supports forward fill with LAST_VALUE(revenue IGNORE NULLS) OVER (...), and Snowflake supports LAST_VALUE(revenue) IGNORE NULLS OVER (...). PostgreSQL does not support IGNORE NULLS, so use a CTE that removes missing rows before LAG, or implement forward fill with a cumulative group and MAX. Always confirm which requirement applies to the business case.

Practical column: NULL-handling strategies in time-series analysis

Month-over-month KPI calculations are among the most frequently requested analyses. In practice, NULL-handling strategies fall into two categories. ① Treat NULL as zero (the method used here): assume that data not collected means no activity, calculate with 0, and detect it as a sharp negative growth rate. ② Fill NULL with forward fill (ffill): fill NULL with the most recent non-NULL value before calculating month-over-month change (a snapshot-table approach). In dbt, the date spine pattern is standard: use a surrogate key and a LEFT JOIN to a date spine (the complete list of months) to fill months without data with 0 or the previous value. Choose between the strategies according to the business logic, and document the choice in SQL comments or the dbt description.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.