SQL Quiz: Common Table Expressions Ctes
10 questions · exam conditions
0:00
Common Table Expressions CtesQuestion 1 of 10

The customers table contains every customer. The orders table contains order_id, customer_id, and order_date. A report must show every customer and the number of orders placed during 2025, including customers with zero such orders.

Which query correctly structures the calculation with a CTE?

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;
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 INNER JOIN order_counts AS o ON o.customer_id = c.customer_id;
WITH joined_orders AS ( SELECT c.customer_id, o.order_id, o.order_date FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id ) SELECT customer_id, COUNT(*) AS order_count FROM joined_orders WHERE order_date >= DATE '2025-01-01' AND order_date < DATE '2026-01-01' GROUP BY customer_id;
WITH joined_orders AS ( SELECT c.customer_id, o.order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.order_date >= DATE '2025-01-01' AND o.order_date < DATE '2026-01-01' ) SELECT customer_id, COUNT(*) AS order_count FROM joined_orders GROUP BY customer_id;
← Back to quizzes

SQL Quiz

SQL Quiz: Common Table Expressions Ctes

Practice Common Table Expressions Ctes in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Common Table Expressions Ctes, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The customers table contains every customer. The orders table contains order_id, customer_id, and order_date. A report must show every customer and the number of orders placed during 2025, including customers with zero such orders.

Which query correctly structures the calculation with a CTE?

  1. 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; (correct answer)
  2. 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 INNER JOIN order_counts AS o ON o.customer_id = c.customer_id;
  3. WITH joined_orders AS ( SELECT c.customer_id, o.order_id, o.order_date FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id ) SELECT customer_id, COUNT(*) AS order_count FROM joined_orders WHERE order_date >= DATE '2025-01-01' AND order_date < DATE '2026-01-01' GROUP BY customer_id;
  4. WITH joined_orders AS ( SELECT c.customer_id, o.order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.order_date >= DATE '2025-01-01' AND o.order_date < DATE '2026-01-01' ) SELECT customer_id, COUNT(*) AS order_count FROM joined_orders GROUP BY customer_id;
Explanation: 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.

Question 2

A table named orders contains order_id, region, and amount. Management wants each region whose total order amount is greater than the average of the regional totals. The average must give each region equal weight, regardless of its number of orders.

Which query correctly produces the requested result?

  1. 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); (correct answer)
  2. WITH regional AS ( SELECT region, SUM(amount) AS total FROM orders GROUP BY region ) SELECT region, total FROM regional WHERE total > (SELECT AVG(amount) FROM orders);
  3. WITH regional AS ( SELECT region, SUM(amount) AS total FROM orders GROUP BY region ) SELECT region, total FROM regional WHERE total > (SELECT SUM(total) FROM regional);
  4. 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 GROUP BY region);
Explanation: 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.

Question 3

The orders table contains customer_id, category_id, and amount. The report must identify customers whose total spending within a category is greater than the average customer total for that same category. The category average must be calculated after each customer's orders have been combined.

Which CTE structure calculates the comparison at the required levels of detail?

  1. WITH category_averages AS ( SELECT category_id, AVG(amount) AS avg_total FROM orders GROUP BY category_id ) SELECT o.customer_id, o.category_id, SUM(o.amount) AS total FROM orders AS o JOIN category_averages AS a ON a.category_id = o.category_id GROUP BY o.customer_id, o.category_id, a.avg_total HAVING SUM(o.amount) > a.avg_total;
  2. 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; (correct answer)
  3. 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 customer_id, AVG(total) AS avg_total FROM customer_totals GROUP BY customer_id ) SELECT c.customer_id, c.category_id, c.total FROM customer_totals AS c JOIN category_averages AS a ON a.customer_id = c.customer_id WHERE c.total > a.avg_total;
  4. WITH customer_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), category_averages AS ( SELECT category_id, AVG(amount) AS avg_total FROM orders GROUP BY category_id ) SELECT c.customer_id, a.category_id, c.total FROM customer_totals AS c CROSS JOIN category_averages AS a WHERE c.total > a.avg_total;
Explanation: 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.

Question 4

A table named status_history contains ticket_id, status, changed_at, and a unique history_id. The required result contains exactly one latest status row per ticket. If two changes have the same changed_at, the row with the larger history_id is considered later.

Which query correctly uses a CTE to return the required rows?

  1. 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; (correct answer)
  2. WITH ranked AS ( SELECT h.*, ROW_NUMBER() OVER ( PARTITION BY status 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;
  3. WITH ranked AS ( SELECT h.*, RANK() OVER ( PARTITION BY ticket_id ORDER BY changed_at DESC ) AS rn FROM status_history AS h ) SELECT ticket_id, status, changed_at FROM ranked WHERE rn = 1;
  4. WITH ranked AS ( SELECT h.*, ROW_NUMBER() OVER ( PARTITION BY ticket_id ORDER BY changed_at, history_id ) AS rn FROM status_history AS h ) SELECT ticket_id, status, changed_at FROM ranked WHERE rn = 1;
Explanation: 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).

Question 5

The sales table can contain many rows per product, and the returns table can also contain many rows per product. A report must show one row per product with total units sold and total units returned. Joining both detail tables before aggregation would multiply rows for products having multiple records in each table.

Which query correctly uses CTEs to avoid inflated totals?

  1. WITH sold AS ( SELECT product_id, quantity AS sold_qty FROM sales ), returned AS ( SELECT product_id, quantity AS returned_qty FROM returns ) SELECT s.product_id, SUM(s.sold_qty), SUM(r.returned_qty) FROM sold AS s JOIN returned AS r ON r.product_id = s.product_id GROUP BY s.product_id;
  2. WITH activity AS ( SELECT s.product_id, s.quantity AS sold_qty, r.quantity AS returned_qty FROM sales AS s JOIN returns AS r ON r.product_id = s.product_id ) SELECT product_id, SUM(sold_qty), SUM(returned_qty) FROM activity GROUP BY product_id;
  3. 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; (correct answer)
  4. WITH activity AS ( SELECT p.product_id, s.quantity AS sold_qty, r.quantity AS returned_qty FROM products AS p LEFT JOIN sales AS s ON s.product_id = p.product_id LEFT JOIN returns AS r ON r.product_id = p.product_id ) SELECT product_id, SUM(sold_qty), SUM(returned_qty) FROM activity GROUP BY product_id;
Explanation: 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.

Question 6

A report uses a CTE named active_products and must return product IDs that appear in either sales or returns. Both branches of a UNION must join to the same CTE. The database supports a WITH clause applied to an entire compound query.

Which query declares the CTE with the correct scope and uses it in both branches?

  1. 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 products AS a ON a.product_id = r.product_id;
  2. SELECT s.product_id FROM sales AS s JOIN active_products AS a ON a.product_id = s.product_id UNION WITH active_products AS ( SELECT product_id FROM products WHERE active_flag = 'Y' ) SELECT r.product_id FROM returns AS r JOIN active_products AS a ON a.product_id = r.product_id;
  3. 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;
  4. 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; (correct answer)
Explanation: 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.

Question 7

A query must first calculate total sales per salesperson and then calculate total sales per department from those salesperson totals. The database supports multiple nonrecursive CTEs, and a later CTE may reference an earlier CTE.

Which query has the correct CTE declaration and dependency structure?

  1. 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; (correct answer)
  2. WITH person_totals AS ( SELECT salesperson_id, department_id, SUM(amount) AS total FROM sales GROUP BY salesperson_id, department_id ) WITH department_totals AS ( SELECT department_id, SUM(total) AS total FROM person_totals GROUP BY department_id ) SELECT * FROM department_totals;
  3. WITH department_totals AS ( SELECT department_id, SUM(total) AS total FROM person_totals GROUP BY department_id ), person_totals AS ( SELECT salesperson_id, department_id, SUM(amount) AS total FROM sales GROUP BY salesperson_id, department_id ) SELECT * FROM department_totals;
  4. WITH person_totals AS ( SELECT salesperson_id, department_id, SUM(amount) AS total FROM sales GROUP BY salesperson_id, department_id ), WITH department_totals AS ( SELECT department_id, SUM(amount) AS total FROM sales GROUP BY department_id ) SELECT * FROM department_totals;
Explanation: 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.

Question 8

The employees table contains department_id and salary. A report must use an explicit CTE column list to name the two output columns dept and payroll, then display departments whose payroll exceeds 500000.

Which query correctly assigns and references the CTE column names?

  1. WITH department_payroll (dept, payroll, employee_count) AS ( SELECT department_id, SUM(salary) FROM employees GROUP BY department_id ) SELECT dept, payroll FROM department_payroll WHERE payroll > 500000;
  2. WITH department_payroll (payroll, dept) AS ( SELECT department_id, SUM(salary) FROM employees GROUP BY department_id ) SELECT dept, payroll FROM department_payroll WHERE payroll > 500000;
  3. WITH department_payroll (dept, payroll) AS ( SELECT department_id, SUM(salary) FROM employees GROUP BY department_id ) SELECT department_id, salary FROM department_payroll WHERE salary > 500000;
  4. 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; (correct answer)
Explanation: 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.

Question 9

The order_lines table contains order_id, order_date, quantity, and unit_price. An order's gross value is the sum of its line values. Orders with gross value of at least 1000 receive a 10% discount; other orders receive no discount. A report must show the average discounted order value by calendar month. Each order, not each line, must have equal weight in the average.

Which query correctly uses CTEs to perform the transformations in the required order?

  1. WITH discounted_lines AS ( SELECT EXTRACT(YEAR FROM order_date) AS yr, EXTRACT(MONTH FROM order_date) AS mon, CASE WHEN quantity * unit_price >= 1000 THEN quantity * unit_price * 0.90 ELSE quantity * unit_price END AS net FROM order_lines ) SELECT yr, mon, AVG(net) AS avg_order_value FROM discounted_lines GROUP BY yr, mon;
  2. 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; (correct answer)
  3. 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 WHERE quantity * unit_price >= 1000 GROUP BY order_id, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) ) SELECT yr, mon, AVG(gross * 0.90) AS avg_order_value FROM order_totals GROUP BY yr, mon;
  4. WITH monthly_totals AS ( SELECT 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 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 monthly_totals ) SELECT yr, mon, AVG(net) AS avg_order_value FROM discounted GROUP BY yr, mon;
Explanation: 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.

Question 10

The employees table contains employee_id and department_id. The blocked_departments table contains department_id, and that column may contain nulls. A report must return employees whose department does not match any non-null blocked department. Employees with a null department_id must remain in the result.

Which query correctly uses a CTE without introducing null-related filtering errors?

  1. WITH blocked AS ( SELECT department_id FROM blocked_departments WHERE department_id IS NOT NULL ) SELECT e.employee_id FROM employees AS e WHERE e.department_id NOT IN ( SELECT department_id FROM blocked );
  2. WITH blocked AS ( SELECT department_id FROM blocked_departments ) SELECT e.employee_id FROM employees AS e WHERE e.department_id NOT IN ( SELECT department_id FROM blocked );
  3. 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 ); (correct answer)
  4. WITH blocked AS ( SELECT department_id FROM blocked_departments WHERE department_id IS NOT NULL ) SELECT e.employee_id FROM employees AS e WHERE EXISTS ( SELECT 1 FROM blocked AS b WHERE b.department_id <> e.department_id );
Explanation: 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.