Performance Optimization — Learn Anti Joins, FILTER Pivots, Sort Elimination, and Top-N in Practice

QUESTION 6

Conditional Anti Join — Find and efficiently exclude members with no participation in the last 90 days using NOT EXISTS

NOT EXISTSCorrelated conditionPartial-match exclusionComposite-index use

Background ▼

In Basic 2 Q6, we used NOT EXISTS to find “people who have never participated.” In practice, a more advanced pattern appears constantly: period-conditioned exclusion, such as “no participation in the last 90 days (= dormant).” The key is to put the additional condition inside the subquery—you can directly express “there is no participation within the last 90 days,” allowing the planner to choose a Hash Anti Join with filtering through a composite index.

-- ✗ NOT IN + period condition: a NULL makes the result empty (the Basic Q6 trap returns)
WHERE member_id NOT IN (SELECT member_id FROM event_entries
                     WHERE entered_at >= DATE '2026-03-14')

-- ✓ Put the condition inside the subquery: NULL-safe + Anti Join
WHERE NOT EXISTS (SELECT 1 FROM event_entries e
                  WHERE e.member_id = m.member_id
                    AND e.entered_at >= DATE '2026-03-14')

Declare the scope of “does not exist” in the expression: The WHERE inside NOT EXISTS is the definition of “what must not exist for a row to pass.” Put matching member_id AND within the period inside the subquery to mean “a person with no participation during the period.” Trying to put it outside breaks the meaning, even though this is an exclusion. The principle is to complete the granularity of every exclusion condition inside the subquery.

Problem ▼

There is a members table (100,000 actual rows) and an event_entries table (3 million actual rows; member_id allows NULL to represent guest participation). Retrieve “dormant members” whose status is active and who have never participated on or after 2026-03-14, in a form that is not broken by NULL and can be optimized as an Anti Join. Return member_id, name (member_id ascending). Also define a composite index to speed up matching on the subquery side.

Source tables

- members

member_id	name	status
1	Aoki	active
2	Baba	active
3	Chiba	inactive
4	Doi	active
5	Endo	active

- event_entries (member_id allows NULL)

entry_id	member_id	entered_at
1	2	2026-05-10
2	4	2026-01-20
3	NULL	2026-05-01
4	5	2026-06-01
5	2	2026-02-11

※ Imagine this as a mailing list for dormant-member reminders. With NOT IN, the NULL on entry_id=3 (within the period) makes the mailing list empty.

Expected Output ▼

Expected output (member_id ascending):

member_id	name
1	Aoki
4	Doi

Doi (4) is dormant because there is participation history but none during the period. Baba (2) and Endo (5) participated during the period, while Chiba (3) is excluded because inactive.

Model Answer ▼

CREATE INDEX idx_entries_member_date
  ON event_entries (member_id, entered_at);  -- Equality (member_id) → range (entered_at) column order

SELECT   m.member_id, m.name
FROM     members m
WHERE    m.status = 'active'
  AND NOT EXISTS (
    SELECT 1
    FROM   event_entries e
    WHERE  e.member_id  = m.member_id        -- NULL cannot match with =, so it is harmless
      AND  e.entered_at >= DATE '2026-03-14'  -- Only participation during the period is grounds for exclusion
  )
ORDER BY m.member_id;

/*
  Logical execution order:
  1. Build a hash table from in-period event_entries   → register member_id values
  2. Filter members to status='active'                 → retain candidates
  3. Hash matching (Hash Anti Join)                   → pass only rows with no match
  4. Sort in member_id ascending order                → return
  */

Explanation (table transitions & key points) ▼

SELECT   m.member_id, m.name
FROM     members m
WHERE    m.status = 'active'
  AND NOT EXISTS (
  SELECT 1 FROM event_entries e
  WHERE e.member_id = m.member_id
    AND e.entered_at >= DATE '2026-03-14'
)
ORDER BY m.member_id;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

1. Target table — members (only active members are candidates) ▼

FROM members m WHERE m.status = 'active'Only active members are candidates for the dormant check. Inactive Chiba is removed at this point. Narrowing the population with the outer WHERE before matching the exclusion list also reduces the number of matches to perform.

1 / 5

member_id	name	status
1	Aoki	active
2	Baba	active
3	Chiba	inactive
4	Doi	active
5	Endo	active

4 candidate rows (100,000 actual rows)

LEARNING POINTS

Keep the definition of exclusion inside the subquery: The WHERE inside NOT EXISTS declares what causes exclusion if even one row exists. Put every condition—period, type, amount, and so on—inside to express complex absence conditions such as “no participation within 90 days” or “no unpaid invoice” with one correlated subquery. Moving conditions outside changes the meaning, turning exclusion into filtering.

Support the matching side with “equality → range” column order: With an index on (member_id, entered_at), even a Nested Loop can check member_id equality + entered_at range for each member using only the index, and EXISTS stops as soon as it finds one row (early termination for a semijoin). The index can also help filter by period before building a hash table when a Hash Anti Join is chosen. Giving the planner multiple good options is the essence of index design.

“Never” and “not recently” are different conditions of the same pattern: The difference between Basic Q6 (complete non-participation) and this question (non-participation during a period) is one line: AND e.entered_at >= …. Once you know the pattern (NOT EXISTS + correlated condition), you can handle countless variations by swapping conditions—the efficiency of learning by patterns rather than by isolated syntax.

ANTI-PATTERNS

Put the period condition in WHERE with LEFT JOIN: LEFT JOIN event_entries e ON e.member_id = m.member_id WHERE e.entered_at >= … AND e.entry_id IS NULL is a logical contradiction: the period condition in WHERE removes the NULL-extended rows, so the result is always empty. With LEFT JOIN, the period condition must be in the ON clause; this placement trap is precisely why NOT EXISTS should be the first candidate.

Aggregate MAX(entered_at) for everyone and compare afterward: Finding each member’s last participation with GROUP BY and then selecting people older than 90 days unnecessarily aggregates the full participation history even for members who are not candidates for exclusion. NOT EXISTS only checks whether one in-period row exists, and the difference grows with heavy users who have many participation records. Choose the aggregate form only when you need to display the last participation date itself.

Practical note: Design dormant-detection queries to grow

Dormant-member extraction is a representative query that grows new conditions after it is first built: “90 days → also 30 days,” “exclude email participation,” and “use a different standard for corporate members.” With the NOT EXISTS pattern, adding a condition means adding one AND line inside the subquery, while the Anti Join execution strategy remains available. With NOT IN or an aggregate as the starting point, every added condition forces another review of NULL risks and scan cost. Choosing a change-friendly pattern from the start is itself performance management—in Q7, we will strengthen a pivot aggregate that withstands “condition growth” on the aggregation side in one scan.

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

FILTER × GROUP BY Pivot Aggregation — Count, sales, and completion rate by category in one scan

FILTER clauseCross-tabulationNULLIF / COALESCEOne scan

Background ▼

In Basic 2 Q7, FILTER consolidated condition-based aggregates for the whole table into one scan. In the advanced set, we combine it with GROUP BY to build a cross-tabulation (pivot). Practical reports also inevitably need ratios—completion rate, churn rate, and so on—and these can be calculated as expressions of aggregate values within the same scan. There are two traps: integer division truncates to 0, and SUM becomes NULL in a group with zero matching rows.

-- ✗ Integer division: 2 / 3 = 0 (the fraction disappears) / denominator 0 causes division by zero
COUNT(*) FILTER (WHERE …) / COUNT(*)

-- ✓ Multiply by 100.0 to make it numeric + use NULLIF to turn denominator 0 into NULL
ROUND(100.0 * COUNT(*) FILTER (WHERE …) / NULLIF(COUNT(*), 0), 1)

NULLIF(x, 0) is the standard zero-division guard: NULLIF(a, b) returns NULL when a = b and returns a otherwise. Wrap a denominator in NULLIF(denominator, 0) so the whole expression becomes NULL when the denominator is 0 and the query does not fail with an error. Likewise, a SUM(…) FILTER with zero matching rows becomes NULL, so reporting convention is to turn it back into 0 with COALESCE(…, 0).

Problem ▼

From the orders table (5 million actual rows), aggregate the following four values for each category in one scan only: total_cnt (all rows), completed_cnt (completed rows), completed_amt (completed sales total; 0 when there are no matches), and completed_rate (completion rate as a percentage to one decimal place, using a formula that does not fail with denominator 0). Output in ascending category order.

Source table

- orders

order_id	category	status	amount
1	book	completed	1200
2	book	cancelled	800
3	food	completed	500
4	food	completed	700
5	food	completed	300
6	toy	pending	900
7	book	completed	2000

※ If you write the “completion-rate report by category” as subqueries for each category × aggregate type, it becomes 5 million rows × (number of categories × 4) scans.

Expected Output ▼

Expected output (ascending category, 3 rows):

category	total_cnt	completed_cnt	completed_amt	completed_rate
book	3	2	3200	66.7
food	3	3	1500	100.0
toy	1	0	0	0.0

toy has zero completed rows—SUM becomes NULL and the rate numerator becomes 0. The question tests COALESCE and numeric conversion.

Model Answer ▼

SELECT   category,
         COUNT(*)                                              AS total_cnt,
         COUNT(*)    FILTER (WHERE status = 'completed')     AS completed_cnt,
         COALESCE(SUM(amount) FILTER (WHERE status = 'completed'), 0)
                                                               AS completed_amt,
         ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'completed')
               / NULLIF(COUNT(*), 0), 1)                   AS completed_rate
FROM     orders
GROUP BY category
ORDER BY category;   -- This is the only table scan

/*
  Logical execution order:
  1. Scan orders only once from the beginning (Seq Scan)
  2. Group rows by category as they are read (HashAggregate)
  3. Evaluate FILTER conditions row by row
  4. After all rows are read, calculate rate for each group as an expression of aggregate values
  5. Sort in ascending category order and return
  */

Explanation (table transitions & key points) ▼

SELECT   category,
  COUNT(*) AS total_cnt,
  COUNT(*) FILTER (WHERE status = 'completed'),
  COALESCE(SUM(amount) FILTER (WHERE …), 0),
  ROUND(100.0 * cnt
        / NULLIF(COUNT(*), 0), 1)
FROM     orders
GROUP BY category
ORDER BY category;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

1. Target table — orders (category × status as two axes) ▼

FROM ordersThe background color represents the category group. The goal is a pivot table with category as rows and aggregate types as columns. The objective is to finish by reading these 6+1 rows (5 million actual rows) only once.

1 / 5

order_id	category	status	amount
1	book	completed	1200
2	book	cancelled	800
3	food	completed	500
4	food	completed	700
5	food	completed	300
6	toy	pending	900
7	book	completed	2000

7 rows (5 million actual rows)

LEARNING POINTS

GROUP BY and FILTER are orthogonal: GROUP BY decides “which row to gather a record into” (the vertical axis), while FILTER decides “which column a record affects” (the horizontal axis). Combining them completes a pivot table in one scan, and the scan count remains one even as categories or statuses grow. When the set of aggregate types changes dynamically, also remember the option of taking a long-form result with GROUP BY category, status and pivoting it in the presentation layer.

Write ratios as expressions of aggregate values in the same SELECT: Aggregate functions can be freely combined inside SELECT expressions (aggregate / aggregate, aggregate - aggregate, and so on). There is no need to calculate the ratio later in a subquery. Be careful with numeric types, though: PostgreSQL truncates integer division, so the standard approach is to convert to decimal first with 100.0 * or ::numeric.

Distinguish aggregates that become NULL from those that do not: With zero matching rows, COUNT returns 0, while SUM / AVG / MIN / MAX return NULL. Use COALESCE to fill report values with 0 and NULLIF to guard division by zero—make these two a habitual pair. Mixing boundary cases such as toy into sample data is also practical wisdom: always test with groups that have no matching rows.

ANTI-PATTERNS

Calculate the rate with correlated subqueries: A form such as (SELECT COUNT(*) FROM orders o2 WHERE o2.category = o.category AND status = 'completed') / … is the worst pattern, expanding to number of groups × full-table scans. When both numerator and denominator come from the same table, always complete the calculation inside one GROUP BY aggregate.

Put a condition that belongs in HAVING into WHERE (or vice versa): “Only categories with at least one completed row” is a post-aggregation condition, so use HAVING COUNT(*) FILTER (…) >= 1. If you write WHERE status = 'completed', total_cnt and the rate denominator also become completed-only, silently corrupting the numbers. WHERE selects rows; HAVING selects groups—confusing them is an immediate bug in aggregate reports (we use it in Q10).

Practical note: Build report SQL defensively

When dashboard numbers disappear because of NULL or division by zero, viewers cannot tell whether there is no data or the query broke. The three basics of a report that does not break are COALESCE for zero-filling, NULLIF for zero-division protection, and ROUND for fixed precision. Also anticipate natural moments when a group has zero matching rows, such as the start of a month or immediately after a new category is added, and create test data for them; this automatically covers boundaries like toy in this question. In Q8, we will make the list-view side of the report—filtering + sorting + pagination—fast with one index.

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

One Composite Index for WHERE + ORDER BY — All the way to Keyset Pagination

Composite indexORDER BYKeyset paginationSort-node elimination

Background ▼

In Basic 2 Q8, a single-column index eliminated the sort for ORDER BY. Real-world list screens almost always combine “filtering + sorting + pagination.” A form such as WHERE category = 'tech' ORDER BY published_at DESC LIMIT 3 becomes a single operation with a composite index (category, published_at DESC): jump to the equality interval → read the interval in order → stop after N rows. For pagination, keyset pagination (using the previous page’s last-row value as the boundary) avoids OFFSET and keeps deep pages fast.

-- ✗ OFFSET: rows to discard are still read (page 100 = read and discard 297 rows, then return 3)
ORDER BY published_at DESC LIMIT 3 OFFSET 297

-- ✓ keyset: jump directly from the previous page’s last value through the index
WHERE category = 'tech' AND published_at < '2026-06-02'
ORDER BY published_at DESC LIMIT 3

Column-order principle: “equality → sort column”: Entries in a composite index are ordered by column 1 and then column 2. Put category (equality) first so tech entries form a contiguous interval, already ordered by published_at DESC within that interval. With the reverse order, (published_at DESC, category), tech rows are scattered across the full index; the sort may disappear, but skipping irrelevant categories remains.

Problem ▼

From the articles table (2 million actual rows), retrieve the three latest articles in category = 'tech' (page 1) in descending published_at order. Also write (1) the composite index that eliminates the sort stage for this query and (2) the page-2 query that does not use OFFSET (assume the last row on page 1 had published_at = '2026-06-02'). Return article_id, title, published_at.

Source table

- articles (the heap is ordered by neither category nor date)

article_id	category	title	published_at
1	life	Morning Habits	2026-05-01
2	tech	Index Design	2026-06-09
3	life	Organizing Your Days Off	2026-04-12
4	tech	JOIN Basics	2026-06-11
5	life	The Science of Sleep	2026-03-30
6	tech	Sorting and Memory	2026-06-02
7	tech	Using CTEs	2026-05-20
8	tech	Introduction to Statistics	2026-04-25

※ Imagine a category-based latest-articles list with a “Load more” button. Each access either sorts 2 million rows or walks three entries in one contiguous index interval—that is the difference.

Expected Output ▼

Expected output (page 1: tech, published_at descending, 3 rows):

article_id	title	published_at
4	JOIN Basics	2026-06-11
2	Index Design	2026-06-09
6	Sorting and Memory	2026-06-02

Page 2 (keyset method):

article_id	title	published_at
7	Using CTEs	2026-05-20
8	Introduction to Statistics	2026-04-25

Both pages use only Limit → Index Scan in the execution plan. There is no Sort node and no discarded OFFSET scan.

Model Answer ▼

CREATE INDEX idx_articles_cat_pub
  ON articles (category, published_at DESC);  -- Equality (category) → sort column (published_at)

-- Page 1
SELECT   article_id, title, published_at
FROM     articles
WHERE    category = 'tech'          -- Go directly to the tech interval using the first index column
ORDER BY published_at DESC           -- Already ordered within the interval → no Sort
LIMIT    3;

-- Page 2 (keyset: use the previous page’s last published_at as the boundary)
SELECT   article_id, title, published_at
FROM     articles
WHERE    category = 'tech'
  AND    published_at < DATE '2026-06-02'  -- Seek directly to the continuation point through the index
ORDER BY published_at DESC
LIMIT    3;

/*
  Logical execution order (page 1):
  1. Seek to the beginning of the category = 'tech' interval in idx_articles_cat_pub
  2. Read one index entry at a time in interval order and fetch title etc. from the heap
  3. LIMIT stops the scan after returning 3 rows (there is no Sort stage)
  */

Explanation (table transitions & key points) ▼

CREATE INDEX idx_articles_cat_pub
  ON articles (category, published_at DESC);

SELECT   article_id, title, published_at
FROM     articles
WHERE    category = 'tech'
ORDER BY published_at DESC
LIMIT    3;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

1. Target table — heap ordered by neither category nor date ▼

FROM articles (physical order = insertion/update effects)tech and life are mixed, and the rows are not in date order. To satisfy “only tech, newest first, 3 rows”, the choice is between reading and sorting every row on every request or having an index do the work in advance.

1 / 5

article_id	category	title	published_at
1	life	Morning Habits	2026-05-01
2	tech	Index Design	2026-06-09
3	life	Organizing Your Days Off	2026-04-12
4	tech	JOIN Basics	2026-06-11
5	life	The Science of Sleep	2026-03-30
6	tech	Sorting and Memory	2026-06-02
7	tech	Using CTEs	2026-05-20
8	tech	Introduction to Statistics	2026-04-25

8 rows (2 million actual rows; tech is only a subset)

LEARNING POINTS

Composite-index column order is “equality → range/sort column”: Fixing the first column with equality (category) lets the ordering of the second column (published_at) work fully inside that interval. The keyset condition published_at < … is also handled as a range seek through the same index, so one index serves three roles: WHERE, ORDER BY, and pagination. In the reverse order, the interval is fragmented; even if Sort disappears, skipped filter rows remain.

OFFSET means “read, then discard”; keyset means “seek before reading”: OFFSET n internally retrieves n rows and then throws them away. Cost grows with the page number, and inserting rows while a user is viewing the list can also shift page boundaries, causing duplicates or omissions. Keyset seeks directly to a physical position using the boundary value, keeping cost and boundaries stable. For a “next” or “load more” UI, choose keyset without hesitation.

Handle equal timestamps completely with a tie-breaker column: If published_at can tie, use ORDER BY published_at DESC, article_id DESC, index (category, published_at DESC, article_id DESC), and row-value comparison (published_at, article_id) < ('2026-06-02', 6). With all three aligned, keyset pagination eliminates omissions and duplicate displays completely. In practice, adding one unique column last is standard.

ANTI-PATTERNS

Run COUNT(*) every time to display the total page count: Even after adopting keyset, executing COUNT(*) on every request for a pager such as “12,345 total · go to last page” brings back a full-table scan. Use an estimate (such as pg_class.reltuples) or a cache, and shape the UI around “next” navigation. Sometimes a performance problem is cheaper to solve by reviewing the screen specification, not only the query.

Flood the schema with one index per category: There is no need to multiply partial indexes for WHERE category = 'tech', 'life', and so on. One composite index covers the interval for every category. Use a partial index such as WHERE deleted = false only when the condition is fixed and the matching rows are a minority.

Practical note: Design list-screen SQL as a three-part set

The requirements for a list screen always arrive as a three-part set: filtering (WHERE), sorting (ORDER BY), and pagination (LIMIT + boundary). The advanced perspective is to put all three on one composite index instead of optimizing them separately. The design procedure is (1) put equality-condition columns first, (2) follow with sort columns including direction, (3) finish with a tie-breaker column, and (4) use keyset for pagination. Conversely, requirements such as “sort by any column” or “free search across many conditions” cannot be supported by indexes alone, so negotiating a constrained sort order at the screen-specification stage is also performance design. In Q9, we extend this technique of “reading only a small part of an ordered interval” across multiple tables.

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

Latest N Across Monthly Tables — Per-Branch Top-N and Merge Append

UNION ALLPer-branch LIMITMerge AppendAvoiding full-table Sort

Background ▼

In Basic 2 Q9, we learned that UNION ALL is for combining disjoint sources. The advanced step is what comes next: retrieving only the latest N rows from the combined result. The naive form “connect everything, then sort the whole result” is expensive. If each table has an index on created_at, take Top-N in each branch first (at most N × number of branches), then reorder the outer result and keep N rows. With the right conditions PostgreSQL can even use Merge Append to merge already ordered streams.

-- ✗ Append everything → Sort 6 million rows → 3 rows
Limit → Sort (6 million rows) → Append → Seq Scan ×2

-- ✓ 3 rows from each branch index → reorder at most 6 rows and keep 3
Limit → Sort/Merge (≤6 rows) → Append
        ├─ Limit → Index Scan Backward (logs_202605)
        └─ Limit → Index Scan Backward (logs_202606)

Why the result is correct: The overall top 3 must be included in the union of each branch’s top 3; a row ranked fourth or lower within one branch cannot be in the overall top 3. Therefore, branch-local LIMIT 3 compresses the sort input to 6 rows without changing the result. Parenthesize each UNION ALL branch to give it its own ORDER BY / LIMIT.

Problem ▼

Logs are split into monthly tables logs_202605 / logs_202606 (3 million actual rows each; log ID ranges are disjoint; each table has a descending index on created_at). Retrieve the three latest logs across both tables in a form that does not cause a full-table sort. Return log_id, message, created_at (created_at descending).

Source tables

- logs_202605 (created_at DESC index exists)

log_id	message	created_at
101	deploy ok	2026-05-28
102	cache warm	2026-05-30
103	batch done	2026-05-15

- logs_202606 (created_at DESC index exists)

log_id	message	created_at
201	deploy ok	2026-06-10
202	index rebuilt	2026-06-03
203	backup done	2026-06-11

※ Imagine an “Latest logs” widget for an operations dashboard. The requirement is to handle month boundaries correctly without sorting 6 million rows.

Expected Output ▼

Expected output (created_at descending, 3 rows):

log_id	message	created_at
203	backup done	2026-06-11
201	deploy ok	2026-06-10
202	index rebuilt	2026-06-03

The message is the same (deploy ok) in both months, but log_id and created_at differ, so UNION would not remove these two rows. With disjoint sources, use UNION ALL to avoid unnecessary duplicate-elimination cost.

Model Answer ▼

SELECT log_id, message, created_at
FROM (
  (SELECT log_id, message, created_at
   FROM   logs_202605
   ORDER BY created_at DESC LIMIT 3)  -- Branch-local Top-N: read only 3 rows through the index
  UNION ALL                            -- Disjoint sources → zero duplicate-elimination cost
  (SELECT log_id, message, created_at
   FROM   logs_202606
   ORDER BY created_at DESC LIMIT 3)  -- Same idea (parentheses attach ORDER/LIMIT to the branch)
) u
ORDER BY created_at DESC               -- At most 6 rows need reordering
LIMIT  3;

/*
  Logical execution order:
  1. Read only the first 3 entries from the created_at index of logs_202605 (Index Scan + Limit)
  2. Read 3 entries from logs_202606 in the same way
  3. Append the 2 ordered streams (Merge Append when the conditions allow it)
  4. Order at most 6 rows by created_at descending and stop after the first 3
  → Result: 3 rows × 3 columns (only 6 index entries + 6 heap rows read out of 6 million)
*/

Explanation (table transitions & key points) ▼

SELECT log_id, message, created_at
FROM (
  (SELECT … FROM logs_202605
   ORDER BY created_at DESC LIMIT 3)
  UNION ALL
  (SELECT … FROM logs_202606
   ORDER BY created_at DESC LIMIT 3)
) u
ORDER BY created_at DESC
LIMIT  3;

LEGEND ▼

Rows read / loaded

1. Input — two tables split by month ▼

logs_202605 / logs_202606 (3 million rows each, indexed)Logs are partitioned by month, and insertion order does not necessarily equal timestamp order inside each table. The goal is the latest 3 rows across both tables. The naive query feeds 6 million rows into one sort.

1 / 5

Source	log_id	message	created_at
202605	101	deploy ok	2026-05-28
202605	102	cache warm	2026-05-30
202605	103	batch done	2026-05-15
202606	201	deploy ok	2026-06-10
202606	202	index rebuilt	2026-06-03
202606	203	backup done	2026-06-11

3 rows each (3 million actual rows per table)

LEARNING POINTS

Top-N is a distributable aggregate: The overall top N must be contained in the union of the top N from each part; this property means pushing LIMIT into each branch does not change the result. Partial MAX / MIN / COUNT aggregates are distributable in the same way and form a foundation of distributed databases and parallel queries. AVG and median, by contrast, are not naively distributable, so developing a feel for what can be pushed down is practical power.

Parenthesize each UNION ALL branch into a complete query: Parentheses allow each branch to have its own ORDER BY and LIMIT (and, depending on the database, its own optimization). Q8’s “eliminate the sort with an index and STOP after N rows” fires in parallel for each table—a good example of advanced SQL emerging from combining basic patterns. Always rewrite the outer ORDER BY: order inside the branches alone does not guarantee order after UNION ALL.

Partitioned tables automatically take the same shape: With declarative partitioning (PARTITION BY RANGE (created_at)), simply issuing ORDER BY created_at DESC LIMIT 3 usually lets the planner build Merge Append + index Top-N per partition. Hand-written monthly tables reproduce this optimization “by hand,” which makes migration decisions easier to understand.

ANTI-PATTERNS

Stop at an outer ORDER BY / LIMIT alone: Writing the clauses only outside may be correct in result, but the full-table sort quietly grows with the data. In EXPLAIN, inspect the input-row count of Sort and make a habit of asking whether a LIMIT 3 query is feeding millions of rows into it. Branch-local LIMIT is an optimization that the planner cannot always push in for you (especially in hand-written UNION ALL).

Use an all-period view for every cross-period search: Creating CREATE VIEW all_logs AS SELECT … UNION ALL SELECT … for all 12 months and making every screen query it forces even a one-month query to scan 12 tables. Predicate pushdown (pruning) can work through the view, but branch-local LIMIT is not automated. Use dedicated queries for frequent access patterns and treat the general-purpose view as a research tool.

Practical note: Encode an upper bound on how much the query reads

The essence of this question is explicitly encoding a limit such as “at most N × number of branches rows are read” somewhere in the query. Systems with stable performance build screens from bounded queries whose read volume does not change even when the data grows tenfold. Latest feeds (Q8 keyset), cross-table Top-N (this question), and existence checks (one-row STOP with EXISTS) are all representative bounded patterns. In code review, simply asking “How much will this query read when the table is ten times larger?” exposes unbounded queries early. In Q10, we combine all these components into one reporting SQL statement.

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

Capstone — Monthly Union + NULL-Safe Exclusion + FILTER Pivot + HAVING Report SQL

CapstoneUNION ALLNOT EXISTSHAVING × FILTER

Background ▼

This is the final synthesis of Advanced 2. Build a monthly sales report by combining all the components learned so far: ① combine monthly tables with UNION ALL (Q9), ② exclude blocked accounts with NOT EXISTS (Q6: NULL-safe + Anti Join), ③ pivot region × status and calculate amounts with FILTER + COALESCE (Q7), and the new element, ④ select groups with HAVING—aggregate values (including FILTER aggregates!) can be used as conditions.

-- Pattern assembly (data flow = execution order)
FROM ( ... UNION ALL ... ) s        -- ① combine (Append only)
WHERE NOT EXISTS ( ... )            -- ② exclude rows (upstream, before aggregation)
GROUP BY ...                        -- ③ group + FILTER aggregates
HAVING aggregate FILTER (...) >= n       -- ④ select groups (after aggregation)
ORDER BY aggregate_column DESC

Separating WHERE and HAVING is the capstone: Blocking is a row-level condition, so use WHERE (the earlier it reduces rows, the lighter the later stages). “Only regions with at least two paid rows” is knowable only after aggregation, so use HAVING. The sorting question is: “Can this condition be judged by looking at one row?”

Problem ▼

Combine monthly sales sales_202605 / sales_202606 (ID ranges are disjoint; each has several million actual rows), exclude sales from blocked accounts, and aggregate paid count, paid sales total, and refund count by region. Output only regions with at least two paid rows, ordered by paid sales total descending. blocked_accounts.customer_id allows NULL (rows under review may be present). Return region, paid_cnt, paid_amt, refund_cnt.

Source tables

- sales_202605

sale_id	region	status	amount	customer_id
1	east	paid	1000	11
2	west	paid	1500	12
3	east	refund	400	13
4	north	paid	300	15

- sales_202606

sale_id	region	status	amount	customer_id
11	east	paid	2000	11
12	west	refund	700	999
13	east	paid	800	14
14	west	paid	1200	12

- blocked_accounts (customer_id allows NULL)

block_id	customer_id
1	999
2	NULL

※ Also consider the number of scans and what NULL does if you write this as “UNION + NOT IN + a subquery per region × status + an outer count filter.”

Expected Output ▼

Expected output (paid_amt descending):

region	paid_cnt	paid_amt	refund_cnt
east	3	3800	1
west	2	2700	0

sale_id=12 (the refund for blocked customer 999) is excluded. north drops out in HAVING because it has only one paid row. With NOT IN, the NULL in blocked_accounts makes the entire report return zero rows.

Model Answer ▼

SELECT   s.region,
         COUNT(*) FILTER (WHERE s.status = 'paid')               AS paid_cnt,
         COALESCE(SUM(s.amount) FILTER (WHERE s.status = 'paid'), 0) AS paid_amt,
         COUNT(*) FILTER (WHERE s.status = 'refund')             AS refund_cnt
FROM (
  SELECT region, status, amount, customer_id FROM sales_202605
  UNION ALL                          -- Disjoint monthly sources → zero duplicate-elimination cost
  SELECT region, status, amount, customer_id FROM sales_202606
) s
WHERE    NOT EXISTS (                -- Row exclusion before aggregation (WHERE)・NULL-safe
           SELECT 1 FROM blocked_accounts b
           WHERE  b.customer_id = s.customer_id
         )
GROUP BY s.region
HAVING   COUNT(*) FILTER (WHERE s.status = 'paid') >= 2  -- Select groups after aggregation (HAVING)
ORDER BY paid_amt DESC;

/*
  Logical execution order:
  1. Scan sales_202605 / sales_202606 once each and concatenate with Append
  2. Scan blocked_accounts once and build hash table {999} (the NULL row is irrelevant to matching)
  3. Match each row in the concatenated stream against the hash table and exclude matches (Hash Anti Join)
  4. Group by region, evaluating FILTER conditions row by row and updating all aggregates together
  5. After groups are finalized, remove north with HAVING (paid_cnt >= 2)
  6. Sort by paid_amt descending and return
  → Result: 2 rows × 4 columns (one scan per source table・no duplicate elimination・NULL-safe)
*/

Explanation (table transitions & key points) ▼

SELECT   s.region,
  COUNT(*) FILTER (WHERE s.status = 'paid'),
  COALESCE(SUM(s.amount) FILTER (WHERE …), 0),
  COUNT(*) FILTER (WHERE s.status = 'refund')
FROM (
  SELECT … FROM sales_202605
  UNION ALL
  SELECT … FROM sales_202606
) s
WHERE    NOT EXISTS (
  SELECT 1 FROM blocked_accounts b
  WHERE b.customer_id = s.customer_id
)
GROUP BY s.region
HAVING   COUNT(*) FILTER (WHERE s.status = 'paid') >= 2
ORDER BY paid_amt DESC;

LEGEND ▼

Rows read / loaded

1. Input — two disjoint monthly sources + exclusion list ▼

sales_202605 / sales_202606 / blocked_accountsSales are split by month, and blocked_accounts contains customer_id = 999 and NULL (under review). This NULL is the trap that makes the entire monthly report empty the moment NOT IN is used. The background color represents the region.

1 / 5

Source	sale_id	region	status	amount	customer_id
202605	1	east	paid	1000	11
202605	2	west	paid	1500	12
202605	3	east	refund	400	13
202605	4	north	paid	300	15
202606	11	east	paid	2000	11
202606	12	west	refund	700	999
202606	13	east	paid	800	14
202606	14	west	paid	1200	12

May 4 rows + June 4 rows / exclusion list {999, NULL}

LEARNING POINTS

Condition placement has three layers: This query has three kinds of conditions: row-level exclusion belongs in WHERE (match blocked accounts and reduce rows before aggregation), column-direction routing belongs in FILTER (aggregate paid and refund together), and group-level selection belongs in HAVING (at least two paid rows). If you can mechanically ask whether a condition is knowable from a row or only after aggregation, composite report design becomes straightforward.

Scanning stops at three passes: sales_202605 ×1, sales_202606 ×1, blocked_accounts ×1. In a bad example (UNION + NOT IN + a separate subquery per region × status + a later count filter), the sales tables make many round trips and NULL also corrupts the result. The check “Count the tables appearing in FROM in the execution plan” remains useful even in Advanced 2 as the components multiply.

HAVING can contain the aggregate expression directly: You can use an aggregate or a FILTER aggregate that is not selected, such as HAVING COUNT(*) FILTER (WHERE …) >= 2 (in PostgreSQL, SELECT aliases cannot be referenced in HAVING, so repeating the expression is reliable). This pattern is common for removing statistical noise, such as excluding small-sample groups or outlier regions from a report.

ANTI-PATTERNS

Wrap a condition that HAVING can handle in an outer subquery: SELECT * FROM (aggregate query) x WHERE paid_cnt >= 2 produces the same result, but adds nesting for readability and fails to communicate the intent that group selection is declared at the same layer as aggregation. Put conditions that can be written in HAVING into HAVING; shorter queries are easier to optimize and maintain. CTE decomposition is the right choice for some complex multi-stage aggregates, so choose according to the shape.

Push exclusion and selection to the BI tool: Returning every row and asking the dashboard to exclude blocked accounts and apply the count filter creates a triple burden of transfer volume, tool-side memory, and inconsistent numbers. Resolve exclusions and thresholds in SQL, closest to the data, and pass BI only finalized numbers. Keeping one authoritative source of correct numbers is also part of performance management.

Practical note: Advanced 2 summary — five principles for combining patterns

Q6–Q10 in summary: 6. Put every period-conditioned exclusion inside NOT EXISTS (NULL-safe + Anti Join + composite index), 7. Use FILTER + NULLIF / COALESCE for a one-scan pivot across groups × conditions × ratios, 8. Give list screens a composite index for “equality → sort column” plus keyset so every page has constant cost, 9. Put LIMIT inside each branch for cross-table Top-N to bound the read volume, and 10. Stack the layers correctly: combine → exclude (WHERE) → aggregate (FILTER) → select (HAVING). The basic set taught each technique separately; the advanced set focused on combining techniques and deciding where each condition belongs. At this point, most practical SELECT statements become reading problems where you identify “which combination of patterns is this?” In the intermediate set, we move on to the basis for the planner’s choices—statistics, row estimates, and control of JOIN strategies.

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.