Business Analytics Quiz: Subqueries And Ctes
10 questions · exam conditions
0:00
Subqueries And CtesQuestion 1 of 10

A retention analyst wants all active customers who have never submitted a support complaint. The table complaints(customer_id) contains several complaint records, and one imported record has a null customer_id.

Which query reliably returns the intended customers despite the null value in complaints.customer_id?

SELECT c.customer_id FROM customers c WHERE c.active = 1 AND c.customer_id NOT IN (SELECT customer_id FROM complaints);
SELECT c.customer_id FROM customers c WHERE c.active = 1 AND NOT EXISTS (SELECT 1 FROM complaints p WHERE p.customer_id = c.customer_id);
SELECT c.customer_id FROM customers c WHERE c.active = 1 AND EXISTS (SELECT 1 FROM complaints p WHERE p.customer_id <> c.customer_id);
SELECT c.customer_id FROM customers c WHERE c.active = 1 AND c.customer_id <> (SELECT MAX(customer_id) FROM complaints);
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Subqueries And Ctes

Practice Subqueries And Ctes in Business Analytics 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 Subqueries And Ctes, giving you a quick way to practice the rules, question types, and explanations that matter most for Business Analytics.

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

A retention analyst wants all active customers who have never submitted a support complaint. The table complaints(customer_id) contains several complaint records, and one imported record has a null customer_id.

Which query reliably returns the intended customers despite the null value in complaints.customer_id?

  1. SELECT c.customer_id FROM customers c WHERE c.active = 1 AND c.customer_id NOT IN (SELECT customer_id FROM complaints);
  2. SELECT c.customer_id FROM customers c WHERE c.active = 1 AND NOT EXISTS (SELECT 1 FROM complaints p WHERE p.customer_id = c.customer_id); (correct answer)
  3. SELECT c.customer_id FROM customers c WHERE c.active = 1 AND EXISTS (SELECT 1 FROM complaints p WHERE p.customer_id <> c.customer_id);
  4. SELECT c.customer_id FROM customers c WHERE c.active = 1 AND c.customer_id <> (SELECT MAX(customer_id) FROM complaints);
Explanation: Whenever you see a question involving NOT IN with a subquery, your first instinct should be to ask: could that subquery return a null? SQL's three-valued logic (true, false, unknown) makes null values a silent query-killer. Here's why this matters: when SQL evaluates x NOT IN (1, 2, NULL), it checks whether x <> 1 AND x <> 2 AND x <> NULL. That last comparison evaluates to unknown, not true — so the entire condition becomes unknown, and the row is filtered out. This means Option A returns zero rows the moment any null exists in complaints.customer_id, silently excluding every active customer. That's exactly the trap the question is describing. Option B is correct because NOT EXISTS works differently. It checks whether a correlated subquery returns any matching rows. Since it compares p.customer_id = c.customer_id directly, the null record simply never matches any real customer ID — it's ignored rather than poisoning the result. Every active customer without a legitimate complaint record is correctly returned. Option C is logically broken in a different way — EXISTS with <> asks "does any complaint exist for a different customer?", which is almost always true and has nothing to do with the target customer's complaint history. Option D compares each customer against only the single maximum complaint ID, which arbitrarily excludes one customer and ignores all others in the complaints table. Study tip: On any exam question involving NOT IN with a subquery, immediately check whether nulls are possible in that subquery. If they are, NOT EXISTS is almost always the safer, correct choice.

Question 2

An order may have several refund transactions. The tables are orders(order_id, customer_id, order_amount) and refunds(refund_id, order_id, refund_amount). An analyst needs net revenue by customer without counting an order amount more than once when that order has multiple refunds.

Which query correctly uses a CTE to avoid duplication of order amounts?

  1. WITH r AS (SELECT order_id, SUM(refund_amount) AS refunded FROM refunds GROUP BY order_id) SELECT o.customer_id, SUM(o.order_amount - COALESCE(r.refunded, 0)) AS net_revenue FROM orders o LEFT JOIN r ON r.order_id = o.order_id GROUP BY o.customer_id; (correct answer)
  2. WITH r AS (SELECT order_id, refund_amount FROM refunds) SELECT o.customer_id, SUM(o.order_amount - COALESCE(r.refund_amount, 0)) AS net_revenue FROM orders o LEFT JOIN r ON r.order_id = o.order_id GROUP BY o.customer_id;
  3. WITH r AS (SELECT customer_id, SUM(order_amount) AS ordered FROM orders GROUP BY customer_id) SELECT r.customer_id, r.ordered - COALESCE(SUM(f.refund_amount), 0) AS net_revenue FROM r LEFT JOIN refunds f ON f.order_id = r.customer_id GROUP BY r.customer_id, r.ordered;
  4. WITH r AS (SELECT order_id, SUM(refund_amount) AS refunded FROM refunds GROUP BY order_id) SELECT o.customer_id, SUM(o.order_amount) - SUM(r.refunded) AS net_revenue FROM orders o INNER JOIN r ON r.order_id = o.order_id GROUP BY o.customer_id;
Explanation: Whenever you see a question involving a one-to-many relationship — like orders with multiple refunds — your first instinct should be to ask: "Will joining these tables cause any row to be duplicated?" If an order has three refunds, a direct join inflates that order's order_amount three times over, destroying your revenue calculation. The solution is to pre-aggregate the many-side table (refunds) down to one row per order before joining. Answer A does exactly this: the CTE r collapses all refunds per order_id into a single SUM(refund_amount). Then the main query joins each order to at most one CTE row, so order_amount is counted exactly once. The COALESCE(..., 0) gracefully handles orders with no refunds, and the outer GROUP BY customer_id correctly rolls everything up to the customer level. Answer B is the classic trap — the CTE doesn't aggregate; it simply selects all refund rows. The subsequent join still creates multiple rows per order, so order_amount gets double- or triple-counted, producing inflated net revenue. Answer C has a logic error in the join condition: it joins refunds on f.order_id = r.customer_id, which conflates two unrelated keys. The CTE also aggregates by customer before accounting for refunds, making it impossible to correctly subtract per-order refunds. Answer D uses an INNER JOIN instead of a LEFT JOIN, which silently drops orders that have no refunds at all — those customers would simply disappear from the results. Study tip: When a fact table has a one-to-many child table, always aggregate the child into a CTE first, then join. This "aggregate-before-join" pattern prevents row multiplication and is one of the most commonly tested SQL pitfalls in business analytics.

Question 3

An analyst runs two SQL statements together in a system where a nonrecursive CTE has standard statement-level scope: WITH high_value AS (SELECT customer_id FROM customers WHERE lifetime_value > 5000) SELECT COUNT(*) FROM high_value; SELECT customer_id FROM high_value;

Assuming the statements are executed in order, which outcome should the analyst expect?

  1. Both statements succeed because the CTE remains available for the entire database session.
  2. Both statements succeed because the first statement materializes high_value as a temporary table.
  3. The first statement succeeds, but the second cannot resolve high_value as that CTE. (correct answer)
  4. The first statement fails because a CTE cannot be referenced in an aggregate query.
Explanation: When you see SQL questions involving CTEs, the first thing to anchor on is scope: a nonrecursive CTE is bound to the single statement it's defined in — nothing more. It is not a persistent object like a view or temporary table; it evaporates the moment its associated statement finishes executing. In this scenario, the WITH high_value AS (...) definition is attached to the first SELECT COUNT(*) FROM high_value statement. That query runs successfully, resolving high_value within its own statement boundary. The second statement — SELECT customer_id FROM high_value — is a completely separate SQL statement with no WITH clause of its own. By the time it executes, high_value no longer exists in any form, so the database engine cannot resolve the name and throws an error. That makes C the correct answer. A is wrong because CTEs do not persist for the duration of a session. That's the behavior of session-scoped temporary tables, not CTEs. Confusing these two is a very common trap. B is wrong for a similar reason: a CTE is never materialized as a temporary table (unless the database engine does so internally as an optimization, which is invisible to the user and does not extend the CTE's scope). D is wrong because there is absolutely nothing preventing a CTE from being used inside an aggregate query — COUNT(*) over a CTE result set is completely valid SQL. Your study tip: always ask "which statement owns this CTE?" If the referencing query isn't part of the same statement that defines the WITH clause, the CTE is out of scope.

Question 4

An A/B test records one row per assigned visitor in experiment_results(experiment_id, variant, converted), where converted is one for a conversion and zero otherwise. Both variants A and B are present. The analyst must return B when B's conversion rate is higher; otherwise, the analyst must return A.

Which query compares conversion rates rather than raw conversion totals?

  1. WITH m AS (SELECT variant, AVG(converted * 1.0) AS rate FROM experiment_results WHERE experiment_id = 42 GROUP BY variant) SELECT CASE WHEN (SELECT rate FROM m WHERE variant = 'B') > (SELECT rate FROM m WHERE variant = 'A') THEN 'B' ELSE 'A' END AS winner; (correct answer)
  2. WITH m AS (SELECT variant, SUM(converted) AS conversions FROM experiment_results WHERE experiment_id = 42 GROUP BY variant) SELECT CASE WHEN (SELECT conversions FROM m WHERE variant = 'B') > (SELECT conversions FROM m WHERE variant = 'A') THEN 'B' ELSE 'A' END AS winner;
  3. WITH m AS (SELECT variant, AVG(converted * 1.0) AS rate FROM experiment_results WHERE experiment_id = 42 GROUP BY variant) SELECT MAX(variant) AS winner FROM m WHERE rate = (SELECT MAX(rate) FROM m);
  4. WITH m AS (SELECT variant, AVG(converted * 1.0) AS rate FROM experiment_results WHERE experiment_id = 42 GROUP BY variant) SELECT CASE WHEN MAX(rate) > MIN(rate) THEN 'B' ELSE 'A' END AS winner FROM m;
Explanation: When evaluating A/B tests in SQL, always ask yourself: does this query compute a rate (conversions ÷ visitors) or just a raw count? Unequal group sizes make raw counts misleading — if variant B had twice as many visitors, it could have more total conversions while actually converting at a lower rate. AVG(converted) is the key idiom here. Because converted is 0 or 1, averaging it gives you exactly the conversion rate (total conversions ÷ total rows). Multiplying by 1.0 ensures floating-point division rather than integer truncation. Answer A correctly computes AVG(converted * 1.0) as rate for each variant, then uses a direct scalar subquery comparison — rate of B > rate of A — to declare the winner. This is both semantically correct and precise. Answer B is the classic trap: it uses SUM(converted), which counts raw conversions. If variant B has 500 conversions from 10,000 visitors (5%) and variant A has 400 conversions from 5,000 visitors (8%), B wins the sum comparison but actually has the lower conversion rate. Answer C also computes rates correctly but uses MAX(variant) to retrieve the winner. With only variants 'A' and 'B', MAX(variant) always returns 'B' regardless of which rate is higher — it's selecting alphabetically, not analytically. Answer D computes rates correctly too, but MAX(rate) > MIN(rate) is almost always true (unless both rates are identical), so it would return 'B' nearly unconditionally — it never actually identifies which variant owns the higher rate. As a study tip: whenever a question involves rates vs. totals, immediately check whether the aggregation is AVG (rate) or SUM/COUNT (volume). That single distinction separates correct A/B analysis from a misleading one.

Question 5

A campaign analyst uses customers(customer_id) and email_events(customer_id, event_type). A customer can have many email_events rows. The analyst wants each customer listed once if that customer has at least one event whose type is purchase.

Which query most directly satisfies the requirement without needing duplicate elimination?

  1. SELECT c.customer_id FROM customers c WHERE c.customer_id = (SELECT e.customer_id FROM email_events e WHERE e.event_type = 'purchase');
  2. SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM email_events e WHERE e.event_type = 'purchase');
  3. SELECT c.customer_id FROM customers c JOIN email_events e ON e.customer_id = c.customer_id WHERE e.event_type = 'purchase';
  4. SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM email_events e WHERE e.customer_id = c.customer_id AND e.event_type = 'purchase'); (correct answer)
Explanation: When filtering rows based on a related table, your instinct might be to use a JOIN — but that choice has consequences. The core concept here is correlated subqueries vs. JOINs, and specifically how EXISTS guarantees at-most-one-row-per-customer behavior without extra work. EXISTS returns true the moment its subquery finds any matching row, then moves on. In D, the subquery checks for a matching customer_id and event_type = 'purchase' — the correlation e.customer_id = c.customer_id ties the subquery to each specific customer being evaluated. This means each customer from customers appears exactly once in the result, satisfying the "listed once" requirement directly. Here's why the other options fail: A uses = instead of IN or EXISTS, which means it breaks the moment more than one purchase event exists — a subquery returning multiple rows with = throws an error. B is a classic trap: it uses EXISTS but forgets the correlation. Because there's no e.customer_id = c.customer_id condition, the subquery simply checks whether any purchase exists anywhere in the table — if so, every single customer is returned, regardless of whether they personally made a purchase. C uses a JOIN, which is valid for finding matches but will produce duplicate rows when a customer has multiple purchase events, requiring a DISTINCT or GROUP BY to clean up — extra steps the question explicitly asks you to avoid. Study tip: Whenever you need "each record from Table A where at least one match exists in Table B," reach for EXISTS with a correlated subquery — it's duplicate-safe by design. Watch for uncorrelated EXISTS subqueries (like B) as a common distractor.

Question 6

A dashboard query defines a CTE as follows: WITH ranked_sales AS (SELECT salesperson_id, revenue FROM sales ORDER BY revenue DESC) SELECT salesperson_id, revenue FROM ranked_sales; The database accepts this syntax, but the outer query has no ORDER BY clause.

Which statement best describes the ordering of the final dashboard result?

  1. The final result is guaranteed to remain in descending revenue order because the CTE was sorted.
  2. The final result has no guaranteed order unless the outer query specifies its own ORDER BY. (correct answer)
  3. The final result is guaranteed to use ascending salesperson order because the outer query omits sorting.
  4. The final result must preserve CTE order only when the database materializes the CTE.
Explanation: Whenever you see a question about SQL query results and ordering, anchor yourself to one core principle: the only guaranteed row order in a result set is the one explicitly declared in the outermost ORDER BY clause of the final query. A CTE (Common Table Expression) is essentially a named subquery — a temporary, logical view of data. Even if you include ORDER BY inside a CTE, most database engines treat that clause as advisory at best, or simply ignore it entirely, because relational databases operate on sets, not sequences. The CTE produces an unordered set of rows that the outer query then consumes. Since the outer query in this example has no ORDER BY, the database is free to return rows in any physical order it finds convenient — whether that's a heap scan, index order, or something else entirely. Answer B is correct for exactly this reason: without an ORDER BY on the outer query, no ordering is guaranteed. A is wrong because it assumes the CTE's internal sort propagates outward. It doesn't — the CTE boundary resets any ordering guarantees. C is wrong because omitting a sort clause doesn't default to ascending salesperson order; it defaults to no defined order at all. D is a tempting half-truth: while materialized CTEs might happen to preserve order on some engines in some circumstances, this is implementation-specific behavior, never a guarantee — so you cannot rely on it. Your study tip: whenever you see an ORDER BY buried inside a subquery or CTE, mentally flag it as "cosmetic." If ordering matters in your final output, it must appear in the final SELECT's own ORDER BY clause.

Question 7

A retail analyst has sales(store_id, sale_amount) and needs the average of the stores' total sales. Each store must contribute one total to the final average, regardless of how many sale rows it has.

Which query correctly calculates this metric?

  1. SELECT AVG(sale_amount) AS avg_store_sales FROM sales;
  2. SELECT AVG(SUM(sale_amount)) AS avg_store_sales FROM sales GROUP BY store_id;
  3. WITH store_totals AS (SELECT store_id, SUM(sale_amount) AS total_sales FROM sales GROUP BY store_id) SELECT AVG(total_sales) AS avg_store_sales FROM store_totals; (correct answer)
  4. WITH store_totals AS (SELECT store_id, AVG(sale_amount) AS total_sales FROM sales GROUP BY store_id) SELECT SUM(total_sales) AS avg_store_sales FROM store_totals;
Explanation: When a question asks for an "average of totals," your first instinct should be to recognize that this requires two levels of aggregation: first collapse each group into one value, then average those values. Trying to do both in a single aggregation step is where most mistakes happen. Option C handles this correctly by using a CTE (Common Table Expression) to first compute each store's total sales with SUM(sale_amount) GROUP BY store_id, producing exactly one row per store. The outer query then runs AVG() over those per-store totals — precisely the metric requested. Here's why the other options fail: A computes AVG(sale_amount) across every individual sale row. If Store 1 has 10 transactions and Store 2 has 1, Store 1 gets 10× the influence on the result. This is an average of transactions, not an average of store totals — a fundamentally different number. B attempts to nest AVG(SUM(...)), which is invalid SQL. You cannot wrap one aggregate function directly around another in a single SELECT clause. Most databases will throw an error here. D uses the right CTE structure but swaps the functions: it computes AVG(sale_amount) per store (giving each store's average transaction, not its total), then sums those averages with SUM(). Both the inner and outer aggregations are wrong for what's being asked. A useful pattern to remember: whenever you need an aggregate of an aggregate, reach for a CTE or subquery. If you find yourself nesting two aggregate functions in a single clause, that's a red flag — restructure your query into two steps instead.

Question 8

A pricing dashboard stores products in products(product_id, category_id, price). More than one category currently has an average product price above one hundred dollars.

What is the most likely outcome of running the following query? SELECT product_id FROM products WHERE price > (SELECT AVG(price) FROM products GROUP BY category_id HAVING AVG(price) > 100);

  1. It returns products priced above the greatest qualifying category average.
  2. It returns products priced above at least one qualifying category average.
  3. It fails because the scalar comparison receives multiple subquery rows. (correct answer)
  4. It returns products only from categories whose average exceeds one hundred.
Explanation: When a subquery appears on the right side of a scalar comparison operator (like >, =, <), SQL expects it to return exactly one row. If the subquery returns multiple rows, most database engines throw a runtime error rather than silently choosing a value. Here, the subquery SELECT AVG(price) FROM products GROUP BY category_id HAVING AVG(price) > 100 groups by category and filters for averages above $100. Since the passage explicitly tells you more than one category qualifies, this subquery returns multiple rows — one average per qualifying category. The outer query then tries to evaluate price > [multiple values], which is an illegal scalar comparison. The engine cannot determine which value to compare against, so the query fails with an error, making C the correct answer. Answer A describes the behavior of > ALL(subquery), which would require the price to exceed every returned value — effectively the greatest. That's a different, explicit construct. Answer B describes the behavior of > ANY(subquery) or > SOME(subquery), which returns true if the condition holds for at least one returned value. Both ALL and ANY are valid multi-row comparison operators that SQL supports, but they must be written explicitly — a bare > cannot handle multiple rows. Answer D is a logical misread; the outer WHERE clause filters on price, not on which category a product belongs to, so it could never isolate products by category membership. Your study tip: whenever you see a subquery next to a plain comparison operator (=, >, <, etc.), immediately ask yourself "could this return more than one row?" If yes, the query likely fails unless ANY, ALL, or IN is used instead.

Question 9

A human-resources analyst has an employees table with columns employee_id, department_id, and salary. The analyst wants employees whose salary is greater than the average non-null salary in their own department.

Which query correctly produces the requested result?

  1. SELECT e.employee_id FROM employees e WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.department_id = e.department_id); (correct answer)
  2. SELECT e.employee_id FROM employees e WHERE e.salary > (SELECT AVG(x.salary) FROM employees x);
  3. SELECT e.employee_id FROM employees e WHERE e.salary > (SELECT MAX(x.salary) FROM employees x WHERE x.department_id = e.department_id);
  4. SELECT e.employee_id FROM employees e WHERE e.salary > ALL (SELECT AVG(x.salary) FROM employees x GROUP BY x.department_id);
Explanation: Whenever you see a question asking to compare each row against an aggregate within the same group, your instinct should jump to correlated subqueries — subqueries that reference the outer query's current row. Here's the core logic: for each employee in the outer query, you need the average salary of that employee's department specifically, not some global or cross-department figure. Answer A does exactly this. The subquery SELECT AVG(x.salary) FROM employees x WHERE x.department_id = e.department_id is correlated — the e.department_id reference ties it back to the outer row, recalculating a department-specific average for every employee evaluated. Since AVG() ignores NULL values by default in SQL, this also satisfies the "non-null salary" requirement automatically. B is the most tempting distractor: it computes a single company-wide average rather than a per-department average. Every employee is compared to the same number, so employees in low-paid departments are unfairly penalized and those in high-paid departments get an easier threshold. C uses MAX() instead of AVG(), which asks "is this salary greater than the highest salary in the department?" — an almost impossible condition that would return virtually no rows. D uses > ALL (...) against a list of every department's average, meaning an employee must exceed every department's average, not just their own. This is far too restrictive and crosses department boundaries incorrectly. Study tip: When a problem says "compared to their own group," always look for a correlated subquery that filters by the group identifier from the outer query — that linking condition is the key differentiator.

Question 10

A regional KPI table snapshots(region_id, snapshot_date, revenue) may contain several rows for a region on the same date. Different regions may have different latest dates. An analyst wants every row occurring on the latest available date for that row's region.

Which query returns the requested rows?

  1. SELECT s.region_id, s.snapshot_date, s.revenue FROM snapshots s WHERE s.snapshot_date = (SELECT MAX(x.snapshot_date) FROM snapshots x);
  2. SELECT s.region_id, s.snapshot_date, s.revenue FROM snapshots s WHERE s.snapshot_date = (SELECT MAX(x.snapshot_date) FROM snapshots x WHERE x.region_id = s.region_id); (correct answer)
  3. SELECT s.region_id, MAX(s.snapshot_date), s.revenue FROM snapshots s GROUP BY s.region_id, s.revenue;
  4. SELECT s.region_id, s.snapshot_date, s.revenue FROM snapshots s WHERE s.snapshot_date IN (SELECT MAX(x.snapshot_date) FROM snapshots x GROUP BY x.region_id);
Explanation: When a question asks you to filter rows by the "latest date per group," that's a signal you need a correlated subquery — one that references the outer query's current row to compute a group-specific value. B is correct because its subquery (SELECT MAX(x.snapshot_date) FROM snapshots x WHERE x.region_id = s.region_id) is tied to each region individually via the WHERE x.region_id = s.region_id condition. For every row in the outer query, the subquery computes that region's maximum date and compares it to the current row's date. This correctly returns all rows — including duplicates on the same date — that fall on each region's own latest snapshot date. A looks similar but its subquery has no WHERE clause, so it computes a single global maximum across all regions. Regions with earlier latest dates get excluded entirely — exactly the problem the question warns you about with "different regions may have different latest dates." C uses GROUP BY region_id, revenue, which means different revenue values on the same date create separate groups. MAX(snapshot_date) is then computed per revenue bucket, not per region, producing misleading results. Worse, it aggregates rows rather than returning the original rows. D is subtler: it pulls one MAX date per region via GROUP BY, but uses IN to match dates across all regions. If Region A's latest date happens to equal Region B's non-latest date, Region B's older rows could incorrectly appear. The correlated approach in B avoids this cross-region contamination. Study tip: When you need a per-group maximum to filter rows, a correlated subquery with WHERE outer.id = inner.id is safer than IN (SELECT MAX(...) GROUP BY ...), which can produce false matches across groups.