- Which query correctly structures the calculation with a CTE?
Answer: WITH order_counts AS (
SELECT customer_id, COUNT(*) AS cnt
FROM orders
WHERE order_date >= DATE '2025-01-01'
AND order_date < DATE '2026-01-01'
GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(o.cnt, 0) AS order_count
FROM customers AS c
LEFT JOIN order_counts AS o
ON o.customer_id = c.customer_id;
When a question asks you to show all rows from one table while optionally matching rows from another, you're being tested on two compounding skills: choosing the right join type and applying filters in the right place.
The cleanest approach is to pre-aggregate the filtered orders inside a CTE, then left-join that summary back to customers. That's exactly what A does — it filters orders to 2025 and counts them before the join, grouping by customer_id. Then the outer query left-joins customers to those counts, so every customer appears. COALESCE(o.cnt, 0) handles customers with no 2025 orders by converting the NULL (produced by the unmatched left join) into a zero. This is the correct answer.
B makes a critical mistake by using INNER JOIN instead of LEFT JOIN. Any customer with zero 2025 orders simply won't appear in order_counts, so the inner join silently drops them — violating the requirement to include every customer.
C builds the CTE with a left join (good), but then applies the WHERE filter on order_date in the outer query. A WHERE clause on the right-side table's column after a left join effectively turns it into an inner join, because NULL dates (from unmatched customers) fail the date comparison and those rows are eliminated.
D is closer — it correctly moves the date filter into the ON clause of the left join, which preserves all customers. However, it uses COUNT(*) rather than COUNT(order_id). For customers with no qualifying orders, order_id is NULL, and COUNT(*) counts that NULL row as 1 instead of 0.
Strategy tip: Whenever you filter a left-joined table, ask yourself where the filter lives. Filters in WHERE eliminate non-matching rows; filters in ON preserve them. Move date conditions into the join's ON clause (or pre-filter in a CTE) to keep all base-table rows intact.
- Which query correctly uses CTEs to perform the transformations in the required order?
Answer: WITH order_totals AS (
SELECT order_id,
EXTRACT(YEAR FROM order_date) AS yr,
EXTRACT(MONTH FROM order_date) AS mon,
SUM(quantity * unit_price) AS gross
FROM order_lines
GROUP BY order_id,
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
),
discounted AS (
SELECT yr, mon,
CASE WHEN gross >= 1000
THEN gross * 0.90 ELSE gross END AS net
FROM order_totals
)
SELECT yr, mon, AVG(net) AS avg_order_value
FROM discounted
GROUP BY yr, mon;
When working with multi-step aggregation problems in SQL, always ask yourself: at what grain does each transformation need to happen? Here, the discount applies per order, so you must fully aggregate each order before checking the threshold — not before, not after.
Option B handles this correctly. The first CTE, order_totals, groups by order_id (plus year and month) to compute each order's gross value via SUM(quantity * unit_price). Only once the full order total exists can the CASE expression in the second CTE, discounted, correctly evaluate whether that total meets the 1000 threshold and apply the 10% discount. The final SELECT then averages one row per order within each month — exactly what the problem requires.
Option A applies the discount check to individual line values (quantity * unit_price) rather than the aggregated order total. An order could have a gross of 1500 spread across multiple lines, yet no single line reaches 1000, so it would incorrectly receive no discount.
Option C compounds two errors: the WHERE clause filters out lines (not orders) below 1000, discarding data before aggregation, and it applies the 10% discount to all remaining orders unconditionally, ignoring orders whose totals actually fall below the threshold.
Option D aggregates by month without including order_id, collapsing all orders in a month into one row. This destroys the per-order grain, making a true per-order average impossible and applying the discount threshold to a monthly total rather than individual order totals.
Study tip: In multi-step SQL problems, sketch the required grain at each stage before writing any code. If a rule applies at the order level, you must aggregate to the order level first — always in a CTE before the final aggregation.
- Which query correctly uses a CTE without introducing null-related filtering errors?
Answer: WITH blocked AS (
SELECT department_id
FROM blocked_departments
WHERE department_id IS NOT NULL
)
SELECT e.employee_id
FROM employees AS e
WHERE NOT EXISTS (
SELECT 1
FROM blocked AS b
WHERE b.department_id = e.department_id
);
Whenever you see NOT IN or NOT EXISTS combined with nullable columns, your first instinct should be to ask: what happens when a null sneaks into the comparison? In SQL, comparing any value to NULL using = or <> produces UNKNOWN, not TRUE or FALSE — and NOT IN is particularly dangerous because a single null in the subquery causes the entire condition to return UNKNOWN for every row, silently eliminating results you expected to keep.
Option C is correct because it uses two complementary safeguards together. The CTE filters out null department_id values from blocked_departments upfront (WHERE department_id IS NOT NULL), ensuring the subquery contains only concrete values. Then NOT EXISTS is used instead of NOT IN. The NOT EXISTS pattern checks whether a matching row exists and handles nulls gracefully — if e.department_id is null, the inner WHERE b.department_id = e.department_id simply finds no match, so NOT EXISTS returns TRUE, correctly keeping that employee in the result.
Option A looks close but fails for employees with a null department_id. Even though the CTE strips nulls from the blocked list, NOT IN still evaluates NULL NOT IN (...) as UNKNOWN, dropping those employees from the output — violating the requirement to keep them.
Option B compounds the problem: it skips the null filter on the CTE entirely. Any null in blocked_departments causes NOT IN to return UNKNOWN for all rows, potentially emptying the result set.
Option D uses EXISTS instead of NOT EXISTS, which inverts the logic entirely — it returns employees who have at least one non-matching blocked department, not employees who are absent from the blocked list.
Study tip: Default to NOT EXISTS over NOT IN whenever nulls might be present in either table — it's the safer, more predictable pattern.
- Which query declares the CTE with the correct scope and uses it in both branches?
Answer: WITH active_products AS (
SELECT product_id
FROM products
WHERE active_flag = 'Y'
)
SELECT s.product_id
FROM sales AS s
JOIN active_products AS a ON a.product_id = s.product_id
UNION
SELECT r.product_id
FROM returns AS r
JOIN active_products AS a ON a.product_id = r.product_id;
When working with CTEs in SQL, the key principle to understand is scope: a WITH clause must appear once, at the very beginning of the entire compound query, making the CTE available to every subsequent SELECT — including all branches of a UNION.
Answer D is correct because it places the WITH active_products AS (...) definition at the top, before any SELECT statement. This single declaration scopes the CTE across the entire query, so both the sales branch and the returns branch can reference active_products freely and correctly.
Answer A looks almost right, but examine the second branch closely — it joins to products instead of active_products. The CTE is properly declared, but the second UNION branch defeats the whole purpose by bypassing the CTE entirely, meaning unfiltered products could slip through.
Answer B tries to insert the WITH clause between the two SELECT statements, right after the UNION keyword. SQL simply does not allow this syntax — a WITH clause cannot appear mid-query. The first SELECT also references active_products before it's ever defined, compounding the error.
Answer C has a subtle but fatal punctuation mistake: there's a **semicolon after the first SELECT**, which terminates the entire query at that point. Everything after the semicolon — including the UNION and the second branch — becomes a separate, broken statement, so the CTE's scope never reaches the returns branch.
As a study tip, always remember: **one WITH, one place — the very top**. Any stray semicolons or mid-query WITH placements will break scope immediately.
- Which query correctly uses a CTE to return the required rows?
Answer: WITH ranked AS (
SELECT h.*,
ROW_NUMBER() OVER (
PARTITION BY ticket_id
ORDER BY changed_at DESC, history_id DESC
) AS rn
FROM status_history AS h
)
SELECT ticket_id, status, changed_at
FROM ranked
WHERE rn = 1;
When you see a question asking for "one row per group based on some ordering," you're being tested on window functions — specifically how ROW_NUMBER(), RANK(), PARTITION BY, and ORDER BY work together inside a CTE.
The goal here is to isolate the latest status per ticket_id, where "latest" means highest changed_at, and if tied, highest history_id. Answer A does this correctly: it partitions by ticket_id (one group per ticket), orders by changed_at DESC, history_id DESC (most recent first, tie-broken by the larger ID), and uses ROW_NUMBER() which always assigns a unique 1 to exactly one row per partition. Filtering WHERE rn = 1 then retrieves precisely that latest row per ticket.
B is wrong because it partitions by status instead of ticket_id. This groups rows by their status value, so rn = 1 gives you the latest row per status — not the latest row per ticket, which is what the problem requires.
C uses RANK() instead of ROW_NUMBER(), and crucially omits history_id from the ORDER BY. If two rows share the same changed_at, RANK() assigns them both rn = 1, meaning you'd get multiple rows back for a single ticket — violating the "exactly one row" requirement.
D orders by changed_at and history_id in ascending order, so rn = 1 picks the oldest row, not the latest. The DESC keyword is essential and its absence here is a subtle but critical bug.
Study tip: Always verify three things in ranking window functions — the correct PARTITION BY column, DESC vs ASC ordering, and whether ROW_NUMBER() vs RANK() is appropriate (use ROW_NUMBER() when you need exactly one result per group).
- Which query correctly uses CTEs to avoid inflated totals?
Answer: WITH sold AS (
SELECT product_id, SUM(quantity) AS sold_qty
FROM sales
GROUP BY product_id
),
returned AS (
SELECT product_id, SUM(quantity) AS returned_qty
FROM returns
GROUP BY product_id
)
SELECT p.product_id,
COALESCE(s.sold_qty, 0) AS sold_qty,
COALESCE(r.returned_qty, 0) AS returned_qty
FROM products AS p
LEFT JOIN sold AS s ON s.product_id = p.product_id
LEFT JOIN returned AS r ON r.product_id = p.product_id;
Whenever you join two tables that each have a many-to-many relationship with a third entity (products), you risk a fan-out problem: rows from one table multiply against rows from the other, inflating your aggregates. The safe pattern is to pre-aggregate each source table independently before joining.
Option C does exactly this. Each CTE (sold and returned) collapses its respective table down to one row per product_id using SUM and GROUP BY before any join occurs. The final LEFT JOIN then combines already-summarized values — no row multiplication is possible. The COALESCE also handles products with no sales or returns, making the result complete and accurate.
Option A looks structured but doesn't pre-aggregate — the CTEs simply rename columns without grouping. When the two detail tables are joined, a product with 3 sales rows and 4 return rows produces 12 joined rows, so SUM in the outer query counts quantities multiple times.
Option B commits the same mistake in a single CTE: it joins the raw sales and returns tables first, creating the inflated cross-product, then aggregates — too late to fix the duplication.
Option D joins raw sales and returns to the products table inside one CTE before aggregating. While a products anchor helps, joining both detail tables simultaneously still causes fan-out for any product with multiple records in both tables, and the outer SUM then overcounts.
Study tip: When two tables both have a one-to-many relationship with a shared key, always aggregate each table to one row per key first, then join the summaries — never join the details together before aggregating.
- Which query correctly produces the requested result?
Answer: WITH regional AS (
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
)
SELECT region, total
FROM regional
WHERE total > (SELECT AVG(total) FROM regional);
When a question asks for regions above the average of regional totals, you need to think carefully about two layers: first aggregating by region, then computing an average across those region-level totals — not across individual rows.
Option A handles this correctly. The CTE regional computes one total per region (e.g., North: 500, South: 300, East: 700). The subquery SELECT AVG(total) FROM regional then averages those three region-level values equally — exactly what "equal weight per region" means. Regions where total exceeds that average are returned.
Option B is tempting but wrong. SELECT AVG(amount) FROM orders computes the average of individual order rows, not regional totals. A region with many small orders would disproportionately drag that average down, violating the equal-weight requirement.
Option C replaces AVG with SUM, so it compares each region's total against the grand total of all regional totals. Nearly every region would fail this comparison, making the condition logically nonsensical for the stated goal.
Option D adds a GROUP BY region inside the subquery that computes AVG(total). This causes the subquery to return multiple rows (one average per region, which is just the region's own total), making the WHERE clause either error out or behave unpredictably depending on the database engine — it doesn't produce a single scalar for comparison.
Study tip: Whenever you see "average of group totals," build a CTE that aggregates first, then apply AVG to that CTE's result. Applying AVG directly to the base table will almost always conflate row-level and group-level averages.
- Which query has the correct CTE declaration and dependency structure?
Answer: WITH person_totals AS (
SELECT salesperson_id, department_id, SUM(amount) AS total
FROM sales
GROUP BY salesperson_id, department_id
),
department_totals AS (
SELECT department_id, SUM(total) AS total
FROM person_totals
GROUP BY department_id
)
SELECT * FROM department_totals;
When working with Common Table Expressions (CTEs), two things matter: syntax structure and declaration order. A WITH clause introduces one or more CTEs separated by commas under a single WITH keyword, and each CTE can only reference CTEs declared before it.
Answer A is correct because it follows both rules perfectly. A single WITH keyword introduces person_totals first, then department_totals is declared using a comma separator and references person_totals — which already exists at that point. The final SELECT then pulls from department_totals. The dependency flows cleanly: raw sales → salesperson totals → department totals.
Answer B fails on syntax: it uses two separate WITH keywords, one for each CTE. SQL does not allow chained WITH statements like this. All CTEs in a query must live under a single WITH keyword, separated by commas — not repeated WITH declarations.
Answer C gets the order backwards. It declares department_totals first, but that CTE tries to query person_totals, which hasn't been defined yet. Since CTEs can only reference previously declared CTEs, this creates an unresolvable dependency.
Answer D has a subtle but fatal punctuation error: it places a WITH keyword before department_totals after the comma (), WITH department_totals). You cannot reuse WITH mid-declaration. Additionally, D queries directly from sales in department_totals rather than building on person_totals, which breaks the required two-step aggregation logic.
A helpful rule of thumb: think of CTEs like variable declarations in code — define dependencies first, use one declaration block, and separate entries with commas only.
- Which query correctly assigns and references the CTE column names?
Answer: WITH department_payroll (dept, payroll) AS (
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
)
SELECT dept, payroll
FROM department_payroll
WHERE payroll > 500000;
When working with CTEs (Common Table Expressions), pay close attention to two rules: the explicit column list in the CTE definition must match the number of columns returned by the inner query, and the outer SELECT must reference the alias names defined in that list — not the original column names from the base table.
Option D gets both rules right. The CTE declares exactly two aliases (dept, payroll) matching the two columns selected inside (department_id, SUM(salary)). The outer query then correctly uses dept and payroll — both in the SELECT list and the WHERE clause — because those are now the only names the CTE exposes.
Option A is a trap for students who miscount. The alias list declares three names (dept, payroll, employee_count), but the inner SELECT only returns two columns. This mismatch causes an error — the number of aliases must exactly equal the number of selected columns.
Option B has the aliases in the wrong order: (payroll, dept) means payroll maps to department_id and dept maps to SUM(salary). Even though the outer SELECT looks correct, the data would be swapped, and the WHERE clause would filter on department IDs rather than salary totals.
Option C defines the CTE aliases correctly as (dept, payroll), but then the outer query references department_id and salary — the original column names that no longer exist once the CTE renames them. This produces a column-not-found error.
Study tip: Think of an explicit CTE column list like a function signature — the count must match, the order determines mapping, and only the new names are visible outside.
- Which CTE structure calculates the comparison at the required levels of detail?
Answer: WITH customer_totals AS (
SELECT customer_id, category_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id, category_id
),
category_averages AS (
SELECT category_id, AVG(total) AS avg_total
FROM customer_totals
GROUP BY category_id
)
SELECT c.customer_id, c.category_id, c.total
FROM customer_totals AS c
JOIN category_averages AS a
ON a.category_id = c.category_id
WHERE c.total > a.avg_total;
When working with multi-level aggregation problems in SQL, your first instinct should be to map out the required "grain" at each step. Here, you need two distinct levels: (1) each customer's total per category, and (2) the average of those totals per category. This two-step logic is exactly what chained CTEs are designed to handle.
Option B nails both levels correctly. The first CTE (customer_totals) aggregates orders down to one row per customer_id + category_id pair. The second CTE (category_averages) then takes the average of those combined totals, grouped by category — not by raw order amounts. This ensures the average reflects "average customer spending per category," not "average order size." The final SELECT simply filters where a customer's total exceeds that category average.
Option A skips the customer-total step entirely, computing AVG(amount) directly from raw order rows. This averages individual order amounts rather than per-customer totals, producing a fundamentally different — and incorrect — comparison value. Grouping with HAVING doesn't fix the flawed average.
Option C makes a critical join error: it averages totals grouped by customer_id instead of category_id, then joins on customer_id. This produces a per-customer average across all their categories, not a per-category average across all customers — the comparison is completely misaligned with the requirement.
Option D drops category_id from customer_totals altogether, losing category-level detail. A CROSS JOIN then nonsensically pairs every customer total against every category average, producing meaningless comparisons.
Study tip: When you see "average of aggregated values," always build a separate CTE to pre-aggregate first — never apply AVG directly to the raw table if the denominator should be customers, not rows.
