SQL NULL — Applied The NOT IN Trap, FILTER, LEFT JOIN

QUESTION 1

GROUP BY and NULL — NULL is treated as one 'peer group'

GROUP BY + NULLCOALESCE + GROUP BYNULL GroupingCOUNT DISTINCT and NULL

Background ▼

When you group a column containing NULL with GROUP BY, all rows whose value is NULL are collected into one group called 'NULL'. Unlike a normal comparison (NULL = NULL → UNKNOWN), GROUP BY treats NULL values as belonging to the same group.

/* GROUP BY region only: NULL remains NULL in the aggregation */
SELECT region, COUNT(*) FROM sales GROUP BY region;
-- > Tokyo|2, Osaka|2, NULL|2

/* Give the NULL group a label with COALESCE */
SELECT COALESCE(region, 'Unknown'), COUNT(*) FROM sales
GROUP BY COALESCE(region, 'Unknown');
-- > Tokyo|2, Osaka|2, Unknown|2

COUNT(DISTINCT col) and NULL: COUNT(DISTINCT region) excludes NULL and returns the number of unique non-NULL values (only the two types Tokyo and Osaka). Using COUNT(DISTINCT COALESCE(region, 'Unknown')) includes the NULL group and returns three types.

Problem ▼

From the sales table, calculate the total sales amount by region (total_amount) and the number of orders (sale_count). Aggregate NULL region values as 'Unknown' and return the results in descending total_amount order. The output columns must be region, total_amount, sale_count.

Tables used

► sales (6 rows)

sale_id	region	amount
1	Tokyo	15000
2	Osaka	8000
3	Tokyo	12000
4	NULL	5000
5	Osaka	9000
6	NULL	7000

Expected Output ▼

region	total_amount	sale_count
Tokyo	27000	2
Osaka	17000	2
Unknown	12000	2

Model Answer ▼

SELECT
  COALESCE(region, 'Unknown')  AS region,  -- convert NULL to 'Unknown'
  SUM(amount)              AS total_amount,
  COUNT(*)                 AS sale_count
FROM   sales
GROUP BY COALESCE(region, 'Unknown')       -- aggregate by the converted value (one NULL group)
ORDER BY total_amount DESC;

/*
  Execution order:
  1. FROM: read the sales table (6 rows)
  2. GROUP BY: convert NULL to 'Unknown' with COALESCE and split into 3 groups (Tokyo, Osaka, Unknown)
  3. SELECT: calculate SUM(amount) and COUNT(*) for each group
  4. ORDER BY: sort by total amount in descending order
*/

Explanation (table transitions & key points) ▼

SELECT
  COALESCE(region, 'Unknown') AS region,
  SUM(amount) AS total_amount,
  COUNT(*) AS sale_count
FROM   sales
GROUP BY COALESCE(region, 'Unknown')
ORDER BY total_amount DESC;

LEGEND ▼

Rows read / loaded

① FROM sales (6 rows) ▼

FROM salesRead the 6 rows from the sales table. Two rows have NULL in region (region not set).

1 / 5

sale_id	region	amount
1	Tokyo	15000
2	Osaka	8000
3	Tokyo	12000
4	NULL	5000
5	Osaka	9000
6	NULL	7000

6 rows read

LEARNING POINTS

GROUP BY treats NULL values as one group: In a normal comparison, NULL = NULL is UNKNOWN, but GROUP BY makes an exception and collects all NULL rows into one group. With GROUP BY region, NULL becomes the 'NULL group', although the SELECT output still displays NULL. Applying COALESCE to the GROUP BY expression lets you give the NULL group a meaningful label.

Make the GROUP BY and SELECT grouping keys explicit: PostgreSQL lets you reference a SELECT alias in GROUP BY, as in SELECT COALESCE(region,'Unknown') AS r ... GROUP BY r. However, when an alias is ambiguous because it has the same name as an input column, the input column takes precedence. For portability and clarity, repeat the same COALESCE expression in GROUP BY or transform the value in a subquery before grouping. If COALESCE has many arguments, pre-transforming the value in a CTE improves readability.

COUNT(DISTINCT col) excludes NULL: COUNT(DISTINCT region) returns the number of unique non-NULL values (the two types Tokyo and Osaka). To count unique values including the NULL group, you need COUNT(DISTINCT COALESCE(region, 'Unknown')). Check that GROUP BY and COUNT(DISTINCT) handle NULL consistently.

ANTI-PATTERNS

Make a SELECT alias the same as an input column and create ambiguity in GROUP BY: With SELECT COALESCE(region,'Unknown') AS region ... GROUP BY region, PostgreSQL interprets region in GROUP BY as the input column, so it may not refer to the intended SELECT alias. The result happens to match for this data, but as normalization rules grow it can make the grouping key and display label diverge. Use an alias such as region_label, or write the same COALESCE expression explicitly in GROUP BY.

Filter the NULL group with HAVING IS NULL after COALESCE: After conversion with COALESCE, HAVING COALESCE(region,'Unknown') IS NULL matches nothing because 'Unknown' is a non-NULL string. After conversion, use HAVING COALESCE(region,'Unknown') = 'Unknown'; when filtering the original column, use HAVING region IS NULL.

Practical column: Visualizing NULL groups and monitoring data quality

When the NULL group is large in a data analysis, it often signals a data collection or input-quality problem. Use GROUP BY COALESCE(region,'Unknown') to count the 'Unknown' group explicitly and regularly monitor its share of the whole (the NULL rate); this is a basic data-quality practice. In dbt, a common design is to create an aggregate view that includes the NULL group and monitor the NULL-rate trend on a dashboard. A sudden increase in NULLs may indicate a problem upstream in the data pipeline, such as ETL or form input.

QUESTION 2

Window Functions and NULL — Distinguish 'boundary NULL' from 'data NULL' in LAG

LAG / LEADNULL PropagationWindow FunctionsNULLS FIRST/LAST

Background ▼

The window function LAG(col) references the value from the previous row, but the NULL it returns can have two different causes with different meanings.

/* ① Boundary NULL: the first row has no previous row, so it returns NULL */
LAG(amount) OVER (ORDER BY dt)

/* ② Data NULL: the previous row's value itself is NULL (missing) */
-- The default-value argument avoids only ① boundary NULL, not ② data NULL
LAG(amount, 1, 0) OVER (ORDER BY dt)

NULL propagation: An arithmetic operation containing NULL always returns NULL. In amount / prev_amount - 1, if either amount or prev_amount is NULL, growth_rate is also NULL. This accurately represents that the growth rate between missing data points cannot be calculated.

Problem ▼

Using the daily_sales table, calculate the day-over-day growth rate (growth_rate). Use a CTE and LAG to obtain prev_amount, then calculate growth_rate = (amount / prev_amount − 1) × 100. On a day when amount or prev_amount is NULL, growth_rate must also be NULL. Return dt, amount, prev_amount, growth_rate in ascending dt order, rounding growth_rate to one decimal place.

Tables used

► daily_sales (7 rows)

dt	amount
2024-01-01	10000
2024-01-02	12000
2024-01-03	NULL
2024-01-04	9000
2024-01-05	11000
2024-01-06	NULL
2024-01-07	13000

Expected Output ▼

dt	amount	prev_amount	growth_rate
2024-01-01	10000	NULL	NULL
2024-01-02	12000	10000	20.0
2024-01-03	NULL	12000	NULL
2024-01-04	9000	NULL	NULL
2024-01-05	11000	9000	22.2
2024-01-06	NULL	11000	NULL
2024-01-07	13000	NULL	NULL

Model Answer ▼

WITH lagged AS (
  SELECT
    dt,
    amount,
    LAG(amount) OVER (ORDER BY dt) AS prev_amount  -- previous day's value (NULL on the first row)
  FROM   daily_sales
)
SELECT
  dt,
  amount,
  prev_amount,
  ROUND(
    (amount::numeric / NULLIF(prev_amount, 0) - 1) * 100,  -- avoid division by zero (return NULL when zero)
    1
  ) AS growth_rate
FROM   lagged
ORDER BY dt;

/*
  Execution order:
  1. CTE (lagged): read daily_sales and use LAG to obtain the previous day's amount
     (the first row is a 'boundary NULL'; a row whose previous day is missing has a 'data NULL')
  2. SELECT: calculate the day-over-day rate using prev_amount
     (if amount or prev_amount is NULL, NULL propagates to the result)
  3. ORDER BY: sort by date in ascending order
*/

Explanation (table transitions & key points) ▼

WITH lagged AS (
  SELECT
    dt, amount,
    LAG(amount) OVER (ORDER BY dt) AS prev_amount
  FROM   daily_sales
)
SELECT
  dt, amount, prev_amount,
  ROUND(
    (amount::numeric / NULLIF(prev_amount, 0) - 1) * 100,
    1
  ) AS growth_rate
FROM   lagged
ORDER BY dt;

LEGEND ▼

Rows read / loaded

① FROM daily_sales (7 rows) ▼

FROM daily_salesRead the 7 rows from daily_sales. The amount column contains NULL (missing values) on 2024-01-03 and 2024-01-06. The key point is how LAG propagates these NULLs.

1 / 4

dt	amount
2024-01-01	10000
2024-01-02	12000
2024-01-03	NULL
2024-01-04	9000
2024-01-05	11000
2024-01-06	NULL
2024-01-07	13000

7 rows read

LEARNING POINTS

LAG can return two kinds of NULL with different meanings: ① A 'boundary NULL' occurs on the first row because the previous row does not physically exist. The third argument (default value) of LAG(col, 1, 0) can avoid it. ② A 'data NULL' occurs because the previous row's value is NULL (missing data). The default-value argument affects only ①, not ②. This distinction matters when analysis needs to tell the two cases apart; add a flag column after LAG to identify them.

NULL propagation: arithmetic containing NULL always returns NULL: NULL + 1, NULL * 100, and NULL / 5 all return NULL. If even one value in an expression is NULL, the whole expression becomes NULL, so apply COALESCE inside the calculation before filling a missing value. This is the fundamental mechanism by which NULL can quietly break a calculation.

Control the position of NULL in ORDER BY with NULLS FIRST / NULLS LAST: PostgreSQL defaults to ASC with NULLS LAST (NULL at the end), and DESC with NULLS FIRST (NULL at the beginning). Explicitly writing ORDER BY col DESC NULLS LAST guarantees the intended order without relying on a DBMS-specific default. The same works in OVER (ORDER BY dt NULLS FIRST) for a window function.

ANTI-PATTERNS

Assume the LAG default value also fills data NULL: LAG(amount, 1, 0) OVER (ORDER BY dt) returns 0 only for the first row. On 2024-01-04, for example, the previous row's amount (2024-01-03) is NULL, so the default is not used and NULL is returned as-is. The default-value argument is used only when the previous row does not exist, not when the previous row's value is NULL.

Replace NULL caused by propagation with 0% afterward using COALESCE: COALESCE(growth_rate, 0) converts a NULL growth_rate to 0%, but that creates the incorrect business interpretation that 'the growth rate on a missing day was 0%'. NULL accurately represents 'cannot calculate / missing', so keep it as NULL and design the dashboard label appropriately instead of replacing it casually.

Practical column: Handling missing dates and NULL in time-series analysis

Day-over-day, month-over-month, and year-over-year comparisons are standard analyses, but missing days (NULL) can make growth rates become NULL in a chain. In practice, Snowflake's LAG(amount) IGNORE NULLS OVER (ORDER BY dt) skips NULL and references the most recent non-NULL value. BigQuery's LAG does not support the IGNORE NULLS syntax, so you can implement forward filling with another expression such as LAST_VALUE(amount IGNORE NULLS) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). PostgreSQL does not support IGNORE NULLS, so use a CTE that removes missing rows before LAG, or implement forward filling with a cumulative group and MAX. Whether to skip missing dates or retain NULL is a KPI-definition decision and should be documented in the data specification.

QUESTION 3

The NOT IN NULL Trap — One NULL can make every row 'quietly disappear'

NOT IN and NULLNOT EXISTSSubquery NULL TrapAnti-Join Patterns

Background ▼

If a NOT IN list contains even one NULL, every row evaluates to UNKNOWN → WHERE excludes all rows, returning zero rows. This is a critical trap.

/* If NOT IN contains NULL, every row becomes UNKNOWN and is excluded */
dept_id NOT IN (10, NULL)
-- For dept_id=20: IN evaluates to UNKNOWN → NOT IN is also UNKNOWN → excluded (bug)

/* NOT EXISTS is a NULL-safe anti-join */
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.dept_id = c.dept_id)
-- With no matching row (including NULL comparisons), EXISTS=FALSE → NOT EXISTS=TRUE (passes)

A NULL in a NOT IN list is a 'time bomb' that makes every row UNKNOWN: In a subquery such as NOT IN (SELECT col FROM ...), one NULL in the subquery column excludes every row. NOT EXISTS is the recommended NULL-safe alternative.

Problem ▼

From the candidates table, exclude candidates whose dept_id appears in blocked_dept_ids. Note that the dept_id column of blocked_dept_ids contains NULL. If a candidate's dept_id is NULL (Sato), include that candidate because you cannot confirm that the candidate is blocked. The output columns must be candidate_id, name, dept_id, ordered by ascending candidate_id.

Tables used

► candidates (5 rows)

candidate_id	name	dept_id
1	Tanaka	10
2	Suzuki	20
3	Sato	NULL
4	Ito	10
5	Yamada	30

► blocked_dept_ids (2 rows)

dept_id
10
NULL

Expected Output ▼

candidate_id	name	dept_id
2	Suzuki	20
3	Sato	NULL
5	Yamada	30

Model Answer ▼

SELECT
  candidate_id,
  name,
  dept_id
FROM   candidates c
WHERE  NOT EXISTS (             -- only candidates with no matching row pass
  SELECT 1
  FROM   blocked_dept_ids b
  WHERE  b.dept_id = c.dept_id  -- correlated comparison (NULL dept_id does not match and passes)
)
ORDER BY candidate_id;

/*
  1. Use the correlated subquery (NOT EXISTS) to identify blocked departments
  2. dept_id=10 has EXISTS=TRUE and is excluded
  3. dept_id=NULL (Sato), 20, and 30 have EXISTS=FALSE and pass
*/

Explanation (table transitions & key points) ▼

SELECT
  candidate_id, name, dept_id
FROM   candidates c
WHERE  NOT EXISTS (
  SELECT 1
  FROM   blocked_dept_ids b
  WHERE  b.dept_id = c.dept_id
)
ORDER BY candidate_id;

LEGEND ▼

Rows read / loaded

① FROM candidates (5 rows) + blocked_dept_ids: (10, NULL) ▼

FROM candidates / blocked_dept_ids contains NULLInspect the 5 rows in candidates. blocked_dept_ids.dept_id contains two values: (10, NULL). This NULL creates the NOT IN trap.

1 / 3

candidate_id	name	dept_id
1	Tanaka	10
2	Suzuki	20
3	Sato	NULL
4	Ito	10
5	Yamada	30

candidates: 5 rows / blocked_dept_ids: (10, NULL)

LEARNING POINTS

A NULL in a NOT IN list makes every row UNKNOWN and excludes it: x NOT IN (..., NULL, ...) expands to NOT (x = v1 OR x = v2 OR x = NULL), and x = NULL is UNKNOWN. With UNKNOWN in the OR chain, IN=UNKNOWN → NOT IN=UNKNOWN → WHERE excludes the row. Every row is excluded because the query cannot prove that it is 'not equal to NULL'; this is a consequence of three-valued logic.

NOT EXISTS is NULL-safe: It checks whether a correlated subquery returns any matching row. Even when NULL = NULL is UNKNOWN, the subquery returns zero rows, so EXISTS=FALSE and NOT EXISTS=TRUE. NOT EXISTS passes when a match cannot be proven, so a candidate with dept_id=NULL passes because its presence in the block list cannot be proven.

There are three ways to make NOT IN safer: ① NOT EXISTS (recommended): NULL-safe and readable. ② LEFT JOIN + IS NULL: the same principle as the anti-join pattern (NULL-safe). ③ NOT IN + WHERE IS NOT NULL: remove NULL from the subquery first with NOT IN (SELECT col FROM t WHERE col IS NOT NULL). In practice, ① or ② is recommended.

ANTI-PATTERNS

Assume NULL in a NOT IN subquery column is harmless: If the source table of a subquery has no NOT NULL constraint, data-entry mistakes or external-source data can introduce NULL. If NULL can appear in the subquery, use NOT EXISTS or LEFT JOIN + IS NULL instead of NOT IN. A latent bug that survived because test data had no NULL can explode with production data.

Decide how candidates with dept_id=NULL (Sato) should be handled: With NOT EXISTS, a candidate whose dept_id is NULL passes because membership in the block list cannot be proven. If the business requirement is to exclude candidates with NULL, add AND dept_id IS NOT NULL to WHERE. Whether NULL candidates pass or are excluded is a business requirement; confirm the specification before implementation.

Practical column: Choosing NOT IN vs NOT EXISTS vs LEFT JOIN + IS NULL in production

The three anti-join forms have different characteristics. NOT EXISTS is the safest and NULL-safe. It is a correlated subquery evaluated per row, but the optimizer can optimize it, so the practical performance difference is often small. LEFT JOIN + IS NULL is easy to inspect and makes multi-column joins and extra conditions straightforward; its execution plan is also easy to understand. NOT IN is safe only when the subquery column is guaranteed NOT NULL and the NULL behavior of the outer key is acceptable. In practice, use NOT EXISTS or LEFT JOIN + IS NULL, and avoid NOT IN when using a subquery as a best practice.

QUESTION 4

FILTER and CASE WHEN Aggregation — A practical pattern for conditional NULL aggregation

FILTER AggregationCASE WHEN + COUNT/AVGConditional AggregationNULL and AVG (Empty Groups)

Background ▼

Adding FILTER (WHERE ...) to an aggregate function lets you aggregate only rows that satisfy a condition. It is important to understand its equivalence to CASE WHEN and the difference in how NULL is handled when the aggregated column contains NULL.

/* FILTER clause: aggregate only matching rows */
COUNT(*) FILTER (WHERE event_type = 'click')
-- COUNT(*) counts rows, so a NULL in another column still counts as 1

/* Specify the target column and skip NULL */
AVG(duration_sec) FILTER (WHERE event_type = 'purchase')
-- rows where duration_sec is NULL are excluded from the calculation

When the aggregate result is 0 versus NULL: COUNT returns 0 when zero rows match the condition. In contrast, AVG and SUM return NULL when no rows match. AVG(col) also returns NULL when every candidate value of col is NULL.

Problem ▼

Using the app_events table, calculate per-user counts by event type (view_count, click_count, purchase_count) and the average duration of purchase events (avg_purchase_sec). Return user_id, view_count, click_count, purchase_count, avg_purchase_sec in ascending user_id order, rounding avg_purchase_sec to one decimal place.

Tables used

► app_events (8 rows)

event_id	user_id	event_type	duration_sec
1	U1	view	15
2	U1	click	NULL
3	U1	purchase	45
4	U2	view	20
5	U2	click	NULL
6	U2	click	NULL
7	U3	purchase	60
8	U3	view	10

Expected Output ▼

user_id	view_count	click_count	purchase_count	avg_purchase_sec
U1	1	1	1	45.0
U2	1	2	0	NULL
U3	1	0	1	60.0

Model Answer ▼

SELECT
  user_id,
  COUNT(*) FILTER (WHERE event_type = 'view')     AS view_count,    -- aggregate only matching rows
  COUNT(*) FILTER (WHERE event_type = 'click')    AS click_count,
  COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
  ROUND(
    AVG(duration_sec) FILTER (WHERE event_type = 'purchase'),  -- average purchase rows only
    1
  )                                                AS avg_purchase_sec
FROM   app_events
GROUP BY user_id
ORDER BY user_id;

/*
  Execution order:
  1. FROM: read app_events
  2. GROUP BY: split the rows by user_id
  3. SELECT (FILTER aggregation)
  4. ORDER BY: sort by user ID
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id,
  COUNT(*) FILTER (WHERE event_type = 'view') AS view_count,
  COUNT(*) FILTER (WHERE event_type = 'click') AS click_count,
  COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
  ROUND(
    AVG(duration_sec) FILTER (WHERE event_type = 'purchase'),
    1
  ) AS avg_purchase_sec
FROM   app_events
GROUP BY user_id
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

① FROM app_events (8 rows) ▼

FROM app_eventsRead the 8 rows from app_events. Every click event has NULL in duration_sec.

1 / 5

event_id	user_id	event_type	duration_sec
1	U1	view	15
2	U1	click	NULL
3	U1	purchase	45
4	U2	view	20
5	U2	click	NULL
6	U2	click	NULL
7	U3	purchase	60
8	U3	view	10

8 rows read

LEARNING POINTS

Add conditions to aggregate functions with FILTER (WHERE ...): This is a standard SQL feature supported by PostgreSQL 9.4+. COUNT(*) FILTER (WHERE cond) counts only rows where cond=TRUE. Its equivalent with CASE WHEN is COUNT(CASE WHEN cond THEN 1 END), but FILTER is more readable and makes the aggregation plan simpler. It is especially useful when writing several conditional aggregates in one GROUP BY.

Omitting ELSE in CASE WHEN is equivalent to ELSE NULL: When ELSE is omitted from CASE WHEN cond THEN 1 END, ELSE NULL is supplied implicitly. COUNT skips NULL, so only matching rows are counted. Adding ELSE 0 makes every row non-NULL and produces the same result as COUNT(*) (counts every row), which is a trap. The same applies to SUM: SUM(CASE WHEN cond THEN amount ELSE 0 END) sums amount over all rows.

AVG for an empty group is NULL, not 0: When AVG(col) FILTER (WHERE cond) has zero matching rows in a group, AVG returns NULL. COUNT returns 0 for zero matches, whereas AVG/SUM return NULL. That is why U2's avg_purchase_sec is NULL. If you want to replace it with 0, use COALESCE(AVG(...) FILTER (...), 0), but confirm that 'no purchases = average 0 seconds' is the correct interpretation for the business requirement.

ANTI-PATTERNS

Break COUNT by adding ELSE 0 to CASE WHEN: COUNT(CASE WHEN event_type='view' THEN 1 ELSE 0 END) makes every row non-NULL because of ELSE 0, so COUNT returns the total number of rows in the group (the same as COUNT(*)). For conditional counts, omit ELSE or write ELSE NULL explicitly. ELSE 0 can be correct in SUM when the intent is to include non-matching rows as 0, but for COUNT you should omit ELSE.

Run GROUP BY separately for every condition: Running a separate GROUP BY and join for each event type makes the JOIN design more complex and can hurt performance. FILTER or CASE WHEN can aggregate several conditions in one GROUP BY. This pattern is also called pivot-style aggregation.

Practical column: Choosing FILTER vs CASE WHEN and reading the execution plan

FILTER and CASE WHEN are nearly equivalent, but FILTER is easier to read in practice and many optimizers process it efficiently. BigQuery uses COUNTIF(cond), while Redshift often uses CASE WHEN, so conventions differ by DBMS. For AVG FILTER and SUM FILTER on a column containing NULL, it is important to understand whether NULL skipping happens before or after FILTER (in fact, FILTER first narrows the target rows, and the aggregate then skips NULL within those rows).

QUESTION 5

Multiple-Condition LEFT JOIN — Results change depending on filters in ON versus WHERE

ON vs WHERECASE WHEN AggregationAdvanced LEFT JOINSUM=NULL vs COUNT=0

Background ▼

When you aggregate data after a LEFT JOIN, both the location of the filter and the aggregation method can change the result substantially.

/* 1. Difference between ON and WHERE */
LEFT JOIN orders o ON m.id = o.member_id AND o.status = 'completed'
-- ✓ members with no match remain (correct LEFT JOIN behavior)

LEFT JOIN orders o ON m.id = o.member_id
WHERE o.status = 'completed'
-- × members with no orders disappear (effectively becomes INNER JOIN)

/* 2. Aggregate multiple conditions with CASE WHEN */
SUM(CASE WHEN o.status = 'completed' THEN o.amount END)
COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END)

SUM NULL versus COUNT NULL: When every CASE result in a group is NULL, SUM returns NULL while COUNT returns 0. SUM needs COALESCE to convert NULL to 0, while COUNT does not.

Problem ▼

Using the members and orders tables, calculate the total amount of completed orders per member (completed_amount) and the number of cancelled orders (cancelled_count). Members with no orders must also be output as 0. Return member_id, name, completed_amount, cancelled_count in ascending member_id order.

Tables used

► members (5 rows)

member_id	name
1	Tanaka
2	Suzuki
3	Sato
4	Ito
5	Yamada

► orders (8 rows)

order_id	member_id	status	amount
1	1	completed	5000
2	1	cancelled	2000
3	2	completed	8000
4	2	completed	3000
5	3	pending	1500
6	3	cancelled	1000
7	4	completed	6000
8	1	pending	4000

Expected Output ▼

member_id	name	completed_amount	cancelled_count
1	Tanaka	5000	1
2	Suzuki	11000	0
3	Sato	0	1
4	Ito	6000	0
5	Yamada	0	0

Model Answer ▼

SELECT
  m.member_id,
  m.name,
  COALESCE(
    SUM(CASE WHEN o.status = 'completed' THEN o.amount END),  -- total completed orders only
    0
  )                  AS completed_amount,
  COALESCE(
    COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END),  -- count cancelled orders
    0
  )                  AS cancelled_count
FROM   members m
LEFT JOIN orders o ON m.member_id = o.member_id  -- keep members with no orders
GROUP BY m.member_id, m.name
ORDER BY m.member_id;

/*
  Execution order:
  1. FROM & LEFT JOIN      → join members and orders (keep members with no orders)
  2. GROUP BY              → group by member
  3. SELECT (CASE WHEN aggregation) → conditional aggregates (COALESCE corrects to 0)
  4. ORDER BY              → sort by member ID
  */

Explanation (table transitions & key points) ▼

SELECT
  m.member_id, m.name,
  COALESCE(
    SUM(CASE WHEN o.status = 'completed' THEN o.amount END),
    0
  ) AS completed_amount,
  COALESCE(
    COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END),
    0
  ) AS cancelled_count
FROM   members m
LEFT JOIN orders o ON m.member_id = o.member_id
GROUP BY m.member_id, m.name
ORDER BY m.member_id;

LEGEND ▼

Rows read / loaded

① FROM members ▼

FROM members mThese are the 5 base rows in members. Yamada (member_id=5), who has no orders, exists at this point too.

1 / 6

member_id	name
1	Tanaka
2	Suzuki
3	Sato
4	Ito
5	Yamada

5 rows

LEARNING POINTS

Where you put the filter matters: ON versus WHERE: When a filter for the right-hand table of a LEFT JOIN is in ON, unmatched rows remain as NULL and the LEFT JOIN behavior is preserved. When it is in WHERE, a NULL row produces NULL = 'completed' → UNKNOWN → WHERE excludes it, effectively turning the join into an INNER JOIN. Put right-table filters in ON, or use CASE WHEN for conditional aggregation.

Aggregate multiple conditions from one LEFT JOIN with CASE WHEN: With the pattern SUM(CASE WHEN status='completed' THEN amount END), you can safely calculate several conditional aggregates from one LEFT JOIN. Omitting ELSE in CASE means ELSE NULL, so SUM and COUNT skip NULL and aggregate only matching rows. This is simpler and often more efficient than running multiple JOINs, and it handles NULL consistently.

SUM returns NULL for zero matching values, while COUNT returns 0: When every CASE WHEN result in a group is NULL, SUM returns NULL but COUNT returns 0. SUM needs COALESCE(SUM(...), 0), while COUNT does not (adding it is harmless). Keeping this difference in mind helps you omit unnecessary COALESCE and prevents forgetting the COALESCE that is actually needed.

ANTI-PATTERNS

Turn a LEFT JOIN into an INNER JOIN with WHERE: With LEFT JOIN orders o ON ... WHERE o.status = 'completed', rows where o.status=NULL (members with no orders, or only pending/cancelled orders) are excluded. Yamada and Sato disappear, so the result is not an all-member aggregation. Use CASE WHEN aggregation or add AND o.status='completed' to the ON clause.

Split the aggregation across multiple LEFT JOINs: It is possible to fetch completed and cancelled orders with separate LEFT JOINs and combine them, but the join order and conditions can create a Cartesian product or duplicate rows. One LEFT JOIN + CASE WHEN aggregation is simpler and safer. If multiple JOINs are necessary, aggregate in each subquery with GROUP BY before joining to prevent a row-count explosion.

Practical column: LEFT JOIN + CASE WHEN pivot aggregation and dbt design

Per-member order aggregation by status is a typical pivot-style pattern. In practice, it is common to aggregate 3–5 statuses in one query; adding another CASE WHEN column makes it easy to extend. In dbt, a common separation is to perform the LEFT JOIN + CASE WHEN aggregation in an intermediate model and convert COALESCE NULL→0 in a mart model. Centralizing COALESCE makes the logic for treating NULL as 0 easier to manage. PostgreSQL's crosstab function and BigQuery's PIVOT clause can also create dynamic pivots, but static CASE WHEN is usually recommended for readability and portability.