SQL Quiz: Computing Derived Aggregates
10 questions · exam conditions
0:00
Computing Derived AggregatesQuestion 1 of 10

Each row in orders represents one order and contains order_id and total_amount. Each order can have several matching rows in order_items. A report joins the tables to identify orders containing an item in category 'A', then calculates average order amount among those qualifying orders. An order may contain several category-A items.

Which query avoids bias from join duplication?

SELECT AVG(1.0 * o.total_amount) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A';
SELECT 1.0 * SUM(o.total_amount) / NULLIF(COUNT(DISTINCT o.order_id), 0) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A';
SELECT 1.0 * SUM(DISTINCT o.total_amount) / NULLIF(COUNT(DISTINCT o.order_id), 0) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A';
WITH q AS (SELECT DISTINCT o.order_id, o.total_amount FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A') SELECT 1.0 * SUM(total_amount) / NULLIF(COUNT(*), 0) FROM q;
← Back to quizzes

SQL Quiz

SQL Quiz: Computing Derived Aggregates

Practice Computing Derived Aggregates 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 Computing Derived Aggregates, 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

Each row in orders represents one order and contains order_id and total_amount. Each order can have several matching rows in order_items. A report joins the tables to identify orders containing an item in category 'A', then calculates average order amount among those qualifying orders. An order may contain several category-A items.

Which query avoids bias from join duplication?

  1. SELECT AVG(1.0 * o.total_amount) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A';
  2. SELECT 1.0 * SUM(o.total_amount) / NULLIF(COUNT(DISTINCT o.order_id), 0) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A';
  3. SELECT 1.0 * SUM(DISTINCT o.total_amount) / NULLIF(COUNT(DISTINCT o.order_id), 0) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A';
  4. WITH q AS (SELECT DISTINCT o.order_id, o.total_amount FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.category = 'A') SELECT 1.0 * SUM(total_amount) / NULLIF(COUNT(*), 0) FROM q; (correct answer)
Explanation: Whenever a JOIN can produce duplicate rows for the same parent record, any aggregate you run afterward will silently count — or sum — that record multiple times. This is called join duplication bias, and it's the central trap this question is testing. Here's the scenario: an order with three category-A items produces three rows after the join. If you then average total_amount, that one order contributes three times instead of once, inflating or skewing the result. The safest fix is to deduplicate before aggregating — which is exactly what D does. The CTE uses SELECT DISTINCT o.order_id, o.total_amount to collapse duplicates first, then computes a clean SUM / COUNT(*) on the already-unique rows. This is the correct answer. A is the most dangerous distractor. AVG(o.total_amount) looks clean, but it averages every row, not every order — so a duplicated order is counted multiple times with no correction whatsoever. B improves on A by using COUNT(DISTINCT o.order_id) in the denominator, but the SUM(o.total_amount) in the numerator still adds every duplicate row. For an order appearing three times, you divide by 1 (distinct order) but sum the amount 3 times — the numerator is still inflated. C tries to fix B by using SUM(DISTINCT o.total_amount), but this is subtly broken: if two different orders happen to have the same total_amount, DISTINCT drops one of them entirely, undercounting your sum. Study tip: When you see a JOIN to a child table followed by aggregation on the parent, always ask yourself: did I deduplicate first? A CTE or subquery with DISTINCT on the parent's key is the cleanest, most reliable pattern.

Question 2

A summary table has one row per department with columns successful_cases and attempted_cases. Departments vary greatly in size. Management wants the organization's overall success rate, so every attempted case—not every department—must receive equal weight.

Which aggregate expression produces the required organization-wide rate?

  1. AVG(1.0 * successful_cases / NULLIF(attempted_cases, 0))
  2. 1.0 * SUM(successful_cases) / NULLIF(SUM(attempted_cases), 0) (correct answer)
  3. 1.0 * SUM(successful_cases) / NULLIF(COUNT(attempted_cases), 0)
  4. 1.0 * AVG(successful_cases) / NULLIF(SUM(attempted_cases), 0)
Explanation: When aggregating rates across groups of unequal size, you must decide whether each group or each individual observation gets equal weight. Here, management wants every attempted case weighted equally — meaning you need a true organization-wide rate, not an average of departmental rates. The right approach is to pool all the raw counts first, then divide: \frac{\sum \text{successful_cases}}{\sum \text{attempted_cases}}. Option B does exactly this with 1.0 * SUM(successful_cases) / NULLIF(SUM(attempted_cases), 0). The 1.0 forces floating-point division, and NULLIF(..., 0) safely handles a zero denominator. A large department with 1,000 cases contributes proportionally more than a small department with 10 cases — which is precisely what "equal weight per case" requires. Option A is the classic Simpson's Paradox trap: AVG(rate) computes each department's individual rate and then averages those rates equally across departments, giving a small 10-case department the same influence as a large 1,000-case department. This inflates or deflates the true rate depending on how department size correlates with success. Option C replaces SUM(attempted_cases) with COUNT(attempted_cases), which counts the number of rows (departments), not the total number of attempted cases. This produces a meaningless denominator unless every department happened to have exactly one attempted case. Option D mixes AVG(successful_cases) — an average count, not a sum — with SUM(attempted_cases), creating a numerator and denominator that are not on comparable scales, yielding a distorted fraction. Study tip: Whenever a question involves rates across unequal-sized groups, ask yourself: "Should I pool the totals or average the rates?" Equal weight per event always means pool first, then divide.

Question 3

A report starts with all rows in users and left joins sessions. A user is active if the user has at least one session whose session_date is on or after '2026-01-01'. The required active-user rate is the number of active registered users divided by all registered users. Users can have many qualifying sessions or none.

Which expression correctly computes the active-user rate while preserving users with no sessions?

  1. 1.0 * COUNT(DISTINCT CASE WHEN s.session_date >= '2026-01-01' THEN u.user_id END) / NULLIF(COUNT(DISTINCT u.user_id), 0) (correct answer)
  2. 1.0 * COUNT(CASE WHEN s.session_date >= '2026-01-01' THEN u.user_id END) / NULLIF(COUNT(DISTINCT u.user_id), 0)
  3. 1.0 * COUNT(DISTINCT u.user_id) / NULLIF(COUNT(DISTINCT CASE WHEN s.session_date >= '2026-01-01' THEN u.user_id END), 0)
  4. 1.0 * COUNT(DISTINCT CASE WHEN s.session_date >= '2026-01-01' THEN u.user_id END) / NULLIF(COUNT(s.session_id), 0)
Explanation: When computing a rate from a LEFT JOIN, you need to think carefully about two things: how duplicates inflate counts, and which values end up in the numerator versus denominator. The goal here is distinct users with at least one qualifying sessionall distinct users\frac{\text{distinct users with at least one qualifying session}}{\text{all distinct users}}. Because a user can have many qualifying sessions, the numerator must use COUNT(DISTINCT ...) to avoid counting the same user multiple times. The denominator must also count distinct users so that every registered user appears exactly once, regardless of how many total sessions they have. Option A does exactly this — COUNT(DISTINCT CASE WHEN s.session_date >= '2026-01-01' THEN u.user_id END) returns one count per qualifying user, and NULLIF(COUNT(DISTINCT u.user_id), 0) safely counts all registered users while preventing division-by-zero. The LEFT JOIN ensures users with no sessions still appear (their session columns are NULL, so they simply don't satisfy the CASE condition and don't enter the numerator). Option B drops the DISTINCT in the numerator, so a user with three qualifying sessions gets counted three times, inflating the rate. Option C has the numerator and denominator flipped — it divides all users by qualifying users, which gives values greater than 1 and is the inverse of what's needed. Option D uses COUNT(s.session_id) in the denominator, which counts total session rows (not distinct users), producing a meaningless denominator that shrinks as users have more sessions. A reliable pattern to remember: whenever a LEFT JOIN can produce multiple rows per parent record, always use COUNT(DISTINCT ...) in any count that touches the child table's columns — otherwise duplicates silently corrupt your result.

Question 4

A grouped query calculates total profit divided by total exposure. Individual exposure values can be positive, negative, or zero. Company policy requires the rate to be NULL whenever the summed exposure for a group equals zero.

Which expression applies the zero check at the correct level?

  1. 1.0 * SUM(profit) / SUM(NULLIF(exposure, 0))
  2. 1.0 * SUM(NULLIF(profit, 0)) / SUM(exposure)
  3. 1.0 * SUM(profit) / NULLIF(SUM(exposure), 0) (correct answer)
  4. SUM(1.0 * profit / NULLIF(exposure, 0))
Explanation: When dividing aggregated values in SQL, the level at which you apply a function matters enormously. Here, the business rule is about the summed exposure for a group — not individual row values — so any zero-protection logic must wrap the aggregate, not the raw column. Option C, 1.0 * SUM(profit) / NULLIF(SUM(exposure), 0), is correct because it first computes SUM(exposure) across the entire group, then uses NULLIF to convert that result to NULL if it equals zero. This perfectly matches the policy: if a group's total exposure is zero, the rate becomes NULL. Option A, 1.0 * SUM(profit) / SUM(NULLIF(exposure, 0)), applies NULLIF inside the SUM, which nullifies individual rows where exposure is zero before summing. This is wrong because a group where, say, exposures of +100 and -100 cancel to zero would not be caught — the row-level values aren't zero, only their sum is. Option B applies NULLIF to individual profit values inside SUM, which is irrelevant to the zero-division problem entirely. It protects against nothing meaningful here. Option D, SUM(1.0 * profit / NULLIF(exposure, 0)), computes a ratio per row and sums those ratios — a fundamentally different calculation from total profit divided by total exposure. It also fails to catch a group where individual exposures are nonzero but sum to zero. The key study tip: always match the level of your guard (NULLIF, CASE WHEN) to the level where the condition applies. If the rule is about a group total, protect the aggregate result, not the individual inputs.

Question 5

A financial table stores integer columns current_revenue and prior_revenue. For each division, revenue growth rate is defined as the change in total revenue divided by total prior revenue. If total prior revenue is zero, the result must be NULL.

Which expression correctly computes the division's growth rate?

  1. 1.0 * (SUM(current_revenue) - SUM(prior_revenue)) / NULLIF(SUM(prior_revenue), 0) (correct answer)
  2. 1.0 * (SUM(current_revenue) - SUM(prior_revenue)) / NULLIF(SUM(current_revenue), 0)
  3. AVG(1.0 * (current_revenue - prior_revenue) / NULLIF(prior_revenue, 0))
  4. 1.0 * SUM(current_revenue) - SUM(prior_revenue) / NULLIF(SUM(prior_revenue), 0)
Explanation: When computing a ratio over aggregated data, you need to apply aggregate functions to the entire group first, then divide — not average row-level ratios. You also need operator precedence and the right denominator to handle division by zero. The growth rate formula is: SUM(current)−SUM(prior)SUM(prior)\frac{\text{SUM(current)} - \text{SUM(prior)}}{\text{SUM(prior)}} with NULL returned when the denominator is zero. Answer A nails this exactly. Multiplying by 1.0 forces floating-point division (avoiding integer truncation), NULLIF(SUM(prior_revenue), 0) returns NULL when total prior revenue is zero, and the numerator correctly computes the aggregate change. This is the only option that matches the problem definition precisely. B is wrong because it uses NULLIF(SUM(current_revenue), 0) as the denominator — but the problem defines the denominator as prior revenue, not current revenue. Dividing by current revenue measures something different entirely. C computes an average of row-level growth rates, which is mathematically different from the division-level growth rate. If rows have different prior revenue magnitudes, this produces a distorted result — you'd be weighting each row equally rather than weighting by revenue size. D has an operator precedence trap. Without parentheses around the subtraction, SQL evaluates it as: 1.0 \times \text{SUM(current_revenue)} - \frac{\text{SUM(prior_revenue)}}{\text{NULLIF(SUM(prior_revenue), 0)}} which simplifies incorrectly to SUM(current_revenue) - 1. Always parenthesize your numerator expressions to avoid silent precedence bugs. As a study habit: whenever you write a ratio in SQL, explicitly wrap the full numerator and denominator in parentheses, and always double-check that NULLIF is guarding the correct column.

Question 6

A query groups support tickets by team. The required SLA rate is the number of closed tickets that met SLA divided by the number of closed tickets. Open tickets must not enter either count, and a team with no closed tickets must receive NULL rather than cause an error.

Which expression correctly computes the SLA rate without filtering the team out of the grouped result?

  1. 1.0 * SUM(CASE WHEN status = 'closed' AND met_sla = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0)
  2. AVG(CASE WHEN status = 'closed' AND met_sla = 1 THEN 1.0 ELSE 0.0 END)
  3. 1.0 * SUM(CASE WHEN status = 'closed' AND met_sla = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END), 0) (correct answer)
  4. 1.0 * SUM(CASE WHEN met_sla = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END), 0)
Explanation: When computing a conditional rate inside a GROUP BY query, you need to think carefully about which rows belong in the numerator and which belong in the denominator — and whether those two sets are actually the same. The SLA rate is defined as: SLA Rate=closed tickets that met SLAclosed tickets\text{SLA Rate} = \frac{\text{closed tickets that met SLA}}{\text{closed tickets}} Option C nails both counts precisely. The numerator uses SUM(CASE WHEN status = 'closed' AND met_sla = 1 THEN 1 ELSE 0 END) — only closed, SLA-met tickets. The denominator uses SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) wrapped in NULLIF(..., 0), which counts only closed tickets and returns NULL instead of causing a division-by-zero error when a team has none. This is exactly what the problem requires. Option A fails because its denominator is COUNT(*), which counts all rows — including open tickets — inflating the denominator and producing an artificially low rate. Option B uses AVG, which averages 1s and 0s across all rows in the group, not just closed ones. Open tickets contribute 0s to the average, diluting the result exactly as option A does, just through a different mechanism. Option D has a subtle but critical bug: its numerator sums met_sla = 1 without filtering on status = 'closed', meaning open tickets that happen to have met_sla = 1 would be counted in the numerator even though they're explicitly excluded by the problem. Study tip: Whenever you write a conditional rate in SQL, verify that your numerator and denominator use matching, explicitly stated conditions — never assume COUNT(*) or an unfiltered column aligns with your intended subset.

Question 7

A shipment table stores region, qty_shipped, and qty_ordered. Both quantity columns use an integer data type. For each region, the required fulfillment rate is total quantity shipped divided by total quantity ordered. The result must retain fractional values and return NULL when the total ordered quantity is zero.

Which expression correctly computes the fulfillment rate?

  1. SUM(qty_shipped) / NULLIF(SUM(qty_ordered), 0)
  2. 1.0 * SUM(qty_shipped) / NULLIF(SUM(qty_ordered), 0) (correct answer)
  3. AVG(1.0 * qty_shipped / NULLIF(qty_ordered, 0))
  4. 1.0 * SUM(qty_shipped) / NULLIF(COUNT(qty_ordered), 0)
Explanation: When dividing integers in SQL, the database performs integer division by default, silently truncating any decimal portion. So if you want fractional results, you must force at least one operand to a numeric (non-integer) type. You also need to guard against division by zero — SQL raises an error if you divide by zero, and NULLIF(expr, 0) elegantly returns NULL instead of 0, making the entire division return NULL as required. B is correct because multiplying SUM(qty_shipped) by 1.0 first converts the integer sum to a decimal, so the subsequent division produces a fractional result. The NULLIF(SUM(qty_ordered), 0) then handles the zero-denominator case at the correct level — after aggregation — returning NULL when the total ordered quantity is zero. This satisfies both requirements perfectly. A fails because it divides two plain integers: SUM(qty_shipped) / NULLIF(SUM(qty_ordered), 0). Even though NULLIF handles the zero case, integer ÷ integer still truncates decimals, so a rate of 0.75 would come back as 0. C is semantically wrong at a conceptual level. AVG(qty_shipped / qty_ordered) computes a per-row ratio first, then averages those ratios — which is not the same as total shipped ÷ total ordered. It also applies NULLIF row-by-row rather than after aggregation, distorting the result. D replaces SUM(qty_ordered) with COUNT(qty_ordered), which counts rows rather than summing the ordered quantities — a completely different value that does not represent fulfillment rate. As a quick rule: whenever you see integer columns in division, ask yourself "do I need decimals?" If yes, cast or multiply by 1.0 before dividing.

Question 8

A campaign has two daily records. The first record contains 1 order from 2 visits, and the second contains 9 orders from 90 visits. An analyst needs the campaign's overall conversion rate, with each visit contributing equally.

Which SQL expression produces the required overall conversion rate?

  1. AVG(1.0 * orders / NULLIF(visits, 0))
  2. 1.0 * SUM(orders) / NULLIF(SUM(visits), 0) (correct answer)
  3. 1.0 * AVG(orders) / NULLIF(SUM(visits), 0)
  4. 1.0 * SUM(orders) / NULLIF(AVG(visits), 0)
Explanation: When aggregating rates across groups, you must decide whether each row or each visit should carry equal weight. The question specifies that every visit counts equally — meaning a visit in the 90-visit day should matter just as much as one in the 2-visit day. That's a visit-weighted (or pooled) rate, not a row-weighted average. The correct formula is simply total orders divided by total visits: ∑orders∑visits=1+92+90=1092≈10.9%\frac{\sum orders}{\sum visits} = \frac{1+9}{2+90} = \frac{10}{92} \approx 10.9\% Option B, 1.0 * SUM(orders) / NULLIF(SUM(visits), 0), does exactly this. The 1.0 * forces floating-point division, and NULLIF(..., 0) safely handles a zero-visit edge case. Option A uses AVG(orders/visits), which computes each row's rate first — (1/2+9/90)/2=(0.5+0.1)/2=30%(1/2 + 9/90)/2 = (0.5 + 0.1)/2 = 30\% — then averages those rates equally by row. This over-weights the small 2-visit day and is visit-unweighted. Option C mixes AVG(orders) in the numerator with SUM(visits) in the denominator: (5)/92≈5.4%(5) / 92 \approx 5.4\%. This is mathematically inconsistent — you're averaging one quantity while summing the other, producing a meaningless result. Option D does the reverse: SUM(orders) / AVG(visits) = 10/46≈21.7%10 / 46 \approx 21.7\%. Again, mixing aggregation levels creates a distorted denominator. The key strategy: whenever a question asks for an overall rate "per unit" (per visit, per impression, etc.), always sum both the numerator and denominator separately before dividing — never average the rates directly.

Question 9

After excused assignments have been removed by a WHERE clause, each remaining row represents one required survey assignment. answer_text is NULL when the assignment was not completed. The completion rate is completed assignments divided by all remaining required assignments.

Which expression correctly computes the completion rate?

  1. 1.0 * COUNT(CASE WHEN answer_text IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0)
  2. 1.0 * SUM(CASE WHEN answer_text IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(answer_text), 0)
  3. 1.0 * COUNT(*) / NULLIF(SUM(CASE WHEN answer_text IS NOT NULL THEN 1 ELSE 0 END), 0)
  4. 1.0 * SUM(CASE WHEN answer_text IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) (correct answer)
Explanation: When computing a rate in SQL, you need to think carefully about two things: what each aggregate function actually counts, and what goes in the numerator vs. denominator. The completion rate is: completed assignmentstotal required assignments\frac{\text{completed assignments}}{\text{total required assignments}} A row is "completed" when answer_text IS NOT NULL. The cleanest way to count completed rows is with SUM(CASE WHEN answer_text IS NOT NULL THEN 1 ELSE 0 END) — this adds 1 for each completed row and 0 for each incomplete row. The total required assignments is simply COUNT(*), which counts every row. Multiplying by 1.0 forces floating-point division, and NULLIF(COUNT(*), 0) guards against division by zero. That's exactly what D does, making it correct. A is wrong because it uses COUNT(...) around the CASE expression instead of SUM. COUNT counts any non-NULL value — and since the CASE always returns either 1 or 0 (never NULL), it counts every row, not just completed ones. This would return 1.0 (100%) regardless of actual completions. B is wrong in its denominator: COUNT(answer_text) only counts rows where answer_text IS NOT NULL — the completed ones. So you'd be dividing completed by completed, always returning 1.0. C flips the numerator and denominator entirely — it divides total rows by completed rows, which gives you the inverse of the completion rate, not the rate itself. Study tip: Memorize the difference between COUNT(column) (ignores NULLs), COUNT(*) (counts all rows), and SUM(CASE...) (conditional total). These three behave very differently and are a frequent trap in SQL aggregation questions.

Question 10

A review table contains product_id, score, and response_weight. score can be NULL when no score was supplied, while response_weight is non-NULL. The weighted average must include only weights associated with non-NULL scores and must return NULL if their total weight is zero.

Which expression correctly calculates the weighted average score for each product?

  1. 1.0 * SUM(score * response_weight) / NULLIF(SUM(response_weight), 0)
  2. AVG(1.0 * score * response_weight) / NULLIF(AVG(response_weight), 0)
  3. 1.0 * SUM(score * response_weight) / NULLIF(SUM(CASE WHEN score IS NOT NULL THEN response_weight ELSE 0 END), 0) (correct answer)
  4. 1.0 * SUM(CASE WHEN score IS NULL THEN response_weight ELSE score END) / NULLIF(SUM(response_weight), 0)
Explanation: When calculating a weighted average in SQL, the formula is ∑(score×weight)∑(weight)\frac{\sum(score \times weight)}{\sum(weight)}, but the tricky part is which weights go in the denominator. You should only sum weights that correspond to non-NULL scores — otherwise you're diluting the average with weights that contributed nothing to the numerator. Option C handles this correctly. In the numerator, SUM(score * response_weight) naturally ignores NULL scores because any multiplication involving NULL produces NULL, and aggregate functions skip NULLs. In the denominator, SUM(CASE WHEN score IS NOT NULL THEN response_weight ELSE 0 END) explicitly includes only weights paired with valid scores. Wrapping that in NULLIF(..., 0) ensures the expression returns NULL instead of causing a division-by-zero error when no valid scores exist. Option A is the classic trap: the numerator correctly skips NULL scores, but the denominator uses SUM(response_weight) over all rows — including those with NULL scores. This over-counts the denominator and produces an incorrectly deflated average. Option B misapplies AVG instead of SUM. The weighted average formula requires summing products and dividing by summed weights, not averaging them separately. This produces a mathematically incorrect result in most cases. Option D's CASE expression is logically backwards — when score IS NULL, it substitutes response_weight into what should be a score value, mixing incompatible quantities in the numerator. Study tip: Whenever NULLs can appear in one column but not another, ask yourself whether your denominator needs a CASE filter to stay consistent with what actually entered the numerator.