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

An analyst verifies revenue using two queries. Every order has at least one item. Query 1 reads only orders and returns SUM(order_total). Query 2 joins orders to order_items on order_id and also returns SUM(order_total). Query 2 produces a larger total, and orders can contain several items.

Which change most directly reconciles Query 2 to the order-level aggregate while retaining the join for later item-based analysis?

Replace the expression with SUM(DISTINCT order_total) after joining the two tables.
Group the joined rows by order_id, retain one order_total per group, and then sum those values.
Divide the joined total by the average number of items associated with an order.
Group the joined rows by customer, sum order_total there, and then sum the customer totals.
← Back to quizzes

SQL Quiz

SQL Quiz: Reconciling Aggregates

Practice Reconciling 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 Reconciling 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

An analyst verifies revenue using two queries. Every order has at least one item. Query 1 reads only orders and returns SUM(order_total). Query 2 joins orders to order_items on order_id and also returns SUM(order_total). Query 2 produces a larger total, and orders can contain several items.

Which change most directly reconciles Query 2 to the order-level aggregate while retaining the join for later item-based analysis?

  1. Replace the expression with SUM(DISTINCT order_total) after joining the two tables.
  2. Group the joined rows by order_id, retain one order_total per group, and then sum those values. (correct answer)
  3. Divide the joined total by the average number of items associated with an order.
  4. Group the joined rows by customer, sum order_total there, and then sum the customer totals.
Explanation: Whenever you join a one-to-many relationship in SQL, rows from the "one" side get duplicated — one copy per matching row on the "many" side. That duplication is exactly the trap here: joining orders to order_items causes each order_total to appear once per item in that order, inflating any aggregate you compute on it. The clean fix, which makes B correct, is to collapse the duplicated rows before summing. You can do this with a subquery or CTE that groups by order_id and picks one order_total per order (using MAX, MIN, or any single-row aggregate), then sums those deduplicated values in an outer query. This preserves the join so item-level columns remain accessible for further analysis — you simply aggregate at the right grain first. A is tempting but unreliable. SUM(DISTINCT order_total) removes duplicate values, not duplicate rows tied to a specific order. If two different orders happen to share the same total, one of them gets silently dropped, understating revenue. It fixes the wrong problem in the wrong way. C is a statistical workaround, not a SQL solution. Dividing by the average item count only approximates the correct answer; any variation in items per order will leave the result wrong. D groups by customer before summing, but if a customer has multiple orders, each order's order_total is still duplicated within the customer group, so the inflation persists. As a rule of thumb: whenever a join multiplies rows, aggregate before you sum, not after. Identify the correct grain (order-level here), deduplicate at that grain, then compute your totals.

Question 2

A regional report must show every customer region, including regions with no completed orders. Query 1 uses LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed' and calculates COUNT(o.order_id). Query 2 puts only o.customer_id = c.customer_id in the join and applies WHERE o.status = 'completed' before grouping.

Why can Query 2 fail to reconcile with Query 1, and what is the correct remedy?

  1. The WHERE predicate removes unmatched customer rows; place the status condition in ON and count o.order_id. (correct answer)
  2. The ON predicate removes completed orders too early; place the status condition in WHERE and count all rows.
  3. The left join duplicates regions without orders; use an inner join and count distinct customer regions.
  4. The status comparison ignores null customer IDs; coalesce both join keys and retain the WHERE predicate.
Explanation: Whenever you see a LEFT JOIN paired with a WHERE clause filtering on the right-side table, pause — this is a classic trap that silently converts your outer join into an inner join. Here's why: a LEFT JOIN preserves all rows from the left table (customers), filling right-side columns with NULL when no match exists. But when you add WHERE o.status = 'completed', SQL evaluates that filter after the join. Rows where no order exists produce o.status = NULL, which fails the WHERE condition, so those customer rows are eliminated entirely. You've lost exactly the regions you were trying to preserve. Query 1 avoids this by placing o.status = 'completed' directly in the ON clause, which filters during the join. Customers with no completed orders still appear, just with NULL for all order columns — and COUNT(o.order_id) correctly returns 0 for them (since COUNT ignores NULLs). Answer A correctly identifies both the problem and the fix. Answer B gets the direction exactly backwards — moving the condition into WHERE is precisely what breaks Query 2, not what fixes it. Answer C misunderstands the goal entirely; switching to an inner join would permanently discard regions with no orders, which is the opposite of what the report requires. Answer D invents a NULL coalescing problem with join keys that isn't described and wouldn't resolve the WHERE-clause elimination issue anyway. Your study tip: memorize this rule — filter conditions on the right table belong in ON, not WHERE, whenever you need a true LEFT JOIN result. If it's in WHERE, you've secretly written an inner join.

Question 3

A revenue pipeline combines current_transactions with transaction_archive. The archive overlaps the current table by seven days. A transaction can appear in both tables with the same transaction_id, and a later correction may change its amount and updated_at timestamp.

Which approach most reliably reconciles the combined revenue total without discarding valid corrected records?

  1. Use UNION, relying on full-row duplicate elimination, and then sum every remaining transaction amount.
  2. Use UNION ALL, retain the latest row per transaction_id by updated_at, and then sum the amounts. (correct answer)
  3. Sum each source independently and subtract the archive's entire seven-day revenue from the result.
  4. Use UNION ALL, group by amount rather than transaction ID, and retain one row per amount.
Explanation: When combining tables that share overlapping records, your goal is to keep exactly one authoritative version of each transaction — the most recently corrected one — before aggregating. That's the core challenge this question tests. The reliable path is B: use UNION ALL to pull every row from both tables (preserving all duplicates), then deduplicate by selecting the row with the maximum updated_at per transaction_id. This ensures corrections made after the original insert are honored, not silently discarded. Only after deduplication do you sum the amounts, giving you a clean, accurate revenue total. A fails because UNION eliminates exact duplicate rows — but a corrected transaction has a different amount and updated_at, so it is not an exact duplicate. Both versions survive the UNION, and summing them double-counts that transaction with no way to know which value is correct. C is dangerously imprecise. Subtracting the archive's entire seven-day revenue removes transactions that may not exist in current_transactions at all, or removes the wrong version of corrected records. It conflates "overlapping period" with "fully redundant data," which isn't guaranteed. D groups by amount instead of transaction_id, which is logically backwards. Two unrelated transactions can share the same dollar amount, so grouping this way merges distinct transactions and produces nonsensical deduplication. A useful rule of thumb: prefer UNION ALL over UNION when you need control over deduplication logic — UNION's automatic row-level elimination is too blunt for business data where corrections change individual fields.

Question 4

Two March event reports query a timestamp column. Query 1 filters with event_ts >= '2026-03-01' AND event_ts < '2026-04-01'. Query 2 filters with event_ts BETWEEN '2026-03-01' AND '2026-03-31'. The database converts date-only literals to timestamps at midnight.

Why is Query 2 lower, and which boundary convention should be used to reconcile the reports?

  1. Query 2 excludes most events on March 31; use the half-open interval from March 1 through April 1. (correct answer)
  2. Query 2 excludes all events on March 1; use inclusive boundaries from March 1 through March 31.
  3. Query 1 includes all events on April 1; use BETWEEN with March 31 as the final literal.
  4. Query 1 duplicates midnight events at both boundaries; subtract events occurring exactly at midnight.
Explanation: Whenever you work with timestamp comparisons in SQL, you need to think carefully about what a "date-only literal" actually means when the column stores full timestamps. The database treats '2026-03-31' as '2026-03-31 00:00:00' — exactly midnight, nothing later. This is the trap in Query 2. BETWEEN is fully inclusive on both ends, so event_ts BETWEEN '2026-03-01' AND '2026-03-31' translates to event_ts >= '2026-03-01 00:00:00' AND event_ts <= '2026-03-31 00:00:00'. Any event on March 31 after midnight — say, 9:00 AM or 11:59 PM — falls outside this range and gets silently dropped. That's why Query 2 returns a lower count. The fix is to use a half-open interval like Query 1: event_ts >= '2026-03-01' AND event_ts < '2026-04-01', which captures every moment of March 31 right up to (but not including) April 1 midnight. Answer A correctly identifies this problem and prescribes the right solution. Answer B is wrong because Query 2 does not exclude March 1 — midnight on March 1 is captured by the >= boundary. The exclusion happens at the upper end, not the lower. Answer C is wrong because Query 1 does not include April 1 events; < '2026-04-01' is a strict less-than, stopping before April 1 midnight. Answer D is wrong because there is no double-counting of midnight events — >= and < never overlap. As a rule of thumb, always prefer half-open intervals (>= start AND < next_period_start) when filtering timestamp columns by date ranges to avoid this boundary truncation trap.

Question 5

An inventory table stores periodic snapshots by product_id, location_id, snapshot_ts, and quantity_on_hand. One report sums every snapshot recorded during a month. A finance report requests inventory on hand as of the month's closing time. Some product-location combinations do not receive a snapshot every day.

Which approach best reconciles the inventory aggregate to the finance definition?

  1. Select only rows on the globally latest snapshot date and sum the available product quantities.
  2. Sum all snapshots during the month and divide by the number of calendar days in that month.
  3. For each product-location pair, select its latest snapshot at or before closing time and sum those quantities. (correct answer)
  4. Take the maximum monthly quantity for each product-location pair and sum those maximum quantities.
Explanation: When a finance report asks for inventory "as of" a specific point in time, you're dealing with a point-in-time query, not an aggregation problem. The key question to ask yourself is: what quantity was actually on hand at closing time? — not what moved through the system during the month. Option C is correct because it mirrors exactly how inventory valuation works in practice. For each product-location pair, you find the most recent snapshot at or before the closing timestamp, then sum those quantities across all pairs. This respects the fact that not every product-location combination gets a daily snapshot — you use whatever the last known state was before the cutoff. The result reflects a real, defensible balance that finance can stand behind. Option A fails because using the globally latest snapshot date forces all product-location pairs onto the same date, which may not exist for every combination. Products with no snapshot on that exact date would be silently excluded or misrepresented. Option B is a trap that confuses a time-weighted average calculation with an ending balance — dividing summed snapshots by calendar days gives you something closer to an average inventory level, which is useful for carrying cost analysis but meaningless as a closing balance. Option D picks the maximum quantity per pair, which has no financial or logical basis for a closing balance report — high-water marks don't represent what was actually on hand at month-end. The study tip here: whenever a question mentions "as of" a date or closing time, think WHERE snapshot_ts <= :closing_time with a MAX(snapshot_ts) per group — that's the point-in-time lookup pattern.

Question 6

Two reports use the same FROM invoices clause and the same filter WHERE status = 'posted'. Report 1 uses COUNT(*); Report 2 uses COUNT(paid_at). Report 2 returns 12 fewer records. There are no joins, and each invoice occupies one row.

If the intended metric is the number of posted invoices, which interpretation and correction are most appropriate?

  1. Twelve posted invoices have a null paid_at; both reports should use COUNT(*) for this metric. (correct answer)
  2. Twelve posted invoices are duplicated; both reports should use COUNT(DISTINCT paid_at) for this metric.
  3. Twelve posted invoices have identical payment times; both reports should use COUNT(DISTINCT invoice_id) only.
  4. Twelve posted invoices have invalid dates; both reports should use COUNT(COALESCE(paid_at, CURRENT_DATE)).
Explanation: Whenever you compare COUNT(*) with COUNT(column), the key distinction is how each handles NULL values. COUNT(*) counts every row, while COUNT(column) counts only rows where that column is not null. The gap between the two tells you exactly how many NULLs exist in that column. In this scenario, COUNT(*) returns 12 more records than COUNT(paid_at) — which means exactly 12 posted invoices have a NULL value in paid_at. These invoices exist and are legitimately posted; they simply haven't been paid yet (or the payment date wasn't recorded). Since the goal is counting posted invoices, COUNT(*) is the correct function for both reports, because it counts every row that passes the WHERE status = 'posted' filter, regardless of whether paid_at is populated. Answer A captures this perfectly. Answer B incorrectly diagnoses the problem as row duplication and prescribes COUNT(DISTINCT paid_at), which would actually exclude NULLs and collapse rows sharing the same timestamp — making the count even less accurate for this metric. Answer C assumes the discrepancy comes from duplicate invoice IDs, but the passage explicitly states each invoice occupies one row, ruling out duplication entirely. COUNT(DISTINCT invoice_id) is unnecessary here. Answer D misidentifies NULLs as "invalid dates" and attempts to patch them with COALESCE, substituting today's date for missing values. This doesn't fix the count — it just disguises the NULLs — and introduces misleading data. Study tip: When you see two COUNT expressions returning different values over the same dataset, immediately ask yourself: "What column is being counted, and could it contain NULLs?" That single question resolves most of these scenarios.

Question 7

The sales table has a non-null amount and a channel. Query 1 calculates AVG(CASE WHEN channel = 'web' THEN amount END). Query 2 calculates AVG(CASE WHEN channel = 'web' THEN amount ELSE 0 END). Query 2 returns a lower value.

Which explanation correctly reconciles the two averages if the intended metric is average amount per web sale?

  1. Query 2 excludes web rows whose amounts are zero; use COUNT(*) as the aggregate denominator instead.
  2. Query 1 includes non-web rows as null amounts; replace nulls with zero so every sale affects the average.
  3. Query 2 includes non-web rows as zero amounts; remove ELSE 0 or filter to web rows before averaging. (correct answer)
  4. Query 1 counts each channel only once; group by channel and average the channel-level averages instead.
Explanation: Whenever you see AVG() applied to a CASE expression, ask yourself: what rows are contributing to the denominator? SQL's AVG ignores NULL values entirely, but it counts zeros as real values. In Query 1, non-web rows produce NULL (no ELSE clause), so AVG only divides by the count of web rows — exactly what you want for "average amount per web sale." In Query 2, the ELSE 0 forces non-web rows into the calculation as zero amounts. Now the denominator includes all rows, dragging the average down. If half your sales are non-web, you're essentially averaging: sum of web amountstotal row count\frac{\text{sum of web amounts}}{\text{total row count}} instead of dividing by web-only count. That's why Query 2 returns a lower value — it's answering a different question than intended. C is correct because it accurately identifies the problem (non-web rows enter as zeros) and offers two valid fixes: drop the ELSE 0, or pre-filter with WHERE channel = 'web' before averaging. A is wrong because zeros from non-web rows aren't excluded — they're the cause of the problem. Switching to COUNT(*) doesn't fix the flawed logic either. B is wrong because it misattributes the issue to Query 1. Query 1's NULL behavior is actually correct for this metric; replacing those nulls with zero would introduce the same flaw Query 2 already has. D is wrong and introduces a completely unrelated technique (averaging channel-level averages) that doesn't address the ELSE 0 issue at all. Study tip: Memorize this rule — AVG skips NULL but counts 0. When you want a conditional average over a subset, let non-qualifying rows return NULL, not zero.

Question 8

For one month, a report calculates COUNT(DISTINCT user_id) separately for each day and then sums the daily results, producing 4,200. Another query calculates COUNT(DISTINCT user_id) once across the entire month and produces 3,100. Users may be active on multiple days.

What is the best explanation and reconciliation method if the requested metric is monthly unique users?

  1. The monthly query includes inactive dates; average the daily distinct counts over the number of days.
  2. The monthly query loses valid daily activity; use SUM(DISTINCT user_id) across all daily result sets.
  3. The daily query is correct because distinct counts are additive; replace the monthly result with 4,200.
  4. The daily sum counts recurring users once per active day; use one monthly COUNT(DISTINCT user_id). (correct answer)
Explanation: Whenever you see a question about aggregating distinct counts across time periods, the key concept to remember is that distinct counts are not additive. A user active on 10 different days contributes 10 to a sum of daily distinct counts, but only 1 to a monthly distinct count. That's exactly what's happening here. The daily approach counts each user once per active day, so a user active on 15 days inflates the sum by 15. Summing those daily results yields 4,200 — but this overstates unique users because recurring activity is counted repeatedly. The single monthly COUNT(DISTINCT user_id) correctly deduplicates across the entire period, counting each user exactly once regardless of how many days they were active. That's why 3,100 is the right metric for monthly unique users, and why D is correct: you should use one monthly COUNT(DISTINCT user_id). A is wrong because averaging daily distinct counts doesn't fix the deduplication problem — it just rescales an already inflated number. B introduces SUM(DISTINCT user_id), which isn't a meaningful SQL operation for this purpose and doesn't deduplicate users across days. C contains the core misconception this question is testing: distinct counts are not additive. You cannot replace the monthly result with the sum of daily results and claim it represents unique users. As a study tip, watch for any question where distinct counts are summed across time buckets — this is almost always a trap. Ask yourself: can the same entity appear in multiple buckets? If yes, summing distinct counts will overcount, and a single COUNT(DISTINCT ...) over the full range is the correct approach.

Question 9

A regional summary reports an average sale of 80 for 20 sales in the North and an average sale of 100 for 80 sales in the South. A dashboard averages the two regional averages and displays 90, while a query over all sale rows displays 96.

Which calculation correctly reconciles the regional summary to the row-level overall average?

  1. Use the unweighted regional calculation (80+100)/2=90(80 + 100) / 2 = 90 because there are two regions.
  2. Use the weighted calculation (80×20+100×80)/100=96(80 × 20 + 100 × 80) / 100 = 96 based on sale counts. (correct answer)
  3. Use the count-weighted calculation (80×80+100×20)/100=84(80 × 80 + 100 × 20) / 100 = 84 by reversing the counts.
  4. Use the combined calculation (80+100)/(20+80)=1.8(80 + 100) / (20 + 80) = 1.8 from the reported values.
Explanation: Whenever you see a question about aggregating grouped data, ask yourself: do the groups have equal sizes? If not, a simple average of the averages will mislead you — you must weight each group's average by its count. Here's why B is correct: the North contributes 20 sales at an average of 80, and the South contributes 80 sales at an average of 100. To find the true overall average, multiply each regional average by its count, sum those products, then divide by the total number of sales: (80×20)+(100×80)20+80=1600+8000100=9600100=96\frac{(80 \times 20) + (100 \times 80)}{20 + 80} = \frac{1600 + 8000}{100} = \frac{9600}{100} = 96 This matches the row-level query result exactly, confirming B reconciles the two figures correctly. A is wrong because it treats both regions as equally important, ignoring that the South has four times as many sales. Averaging the averages only works when group sizes are identical — they aren't here, so you get a misleading 90 instead of 96. C reverses the counts, pairing the North's average (80) with the South's count (80) and vice versa. This produces 84, which is mathematically coherent but factually backwards — a classic trap when you memorize a formula without anchoring each count to the correct group. D divides the sum of the averages by the sum of the counts (80+100)/(20+80)=1.8(80 + 100) / (20 + 80) = 1.8, which is dimensionally nonsensical — you'd be dividing dollars by a count, not computing an average sale at all. Your study tip: whenever you see pre-aggregated summary data in SQL (think GROUP BY results or views), remember that AVG(avg_column) ≠ the true mean. Always reach for the weighted formula: sum of (average × count) divided by total count.

Question 10

A sales fact table contains one row per sale with customer_id, sale_ts, and amount. A type-2 customer dimension contains several historical rows per customer with valid_from and valid_to. A report joins the tables only on customer_id and obtains more revenue than a direct sum of the fact table.

Which validation and query change best reconcile revenue while still allowing analysis by the customer's historical attributes?

  1. Group by every dimension attribute before summing, then add the resulting historical attribute totals.
  2. Join only on customer, then calculate SUM(DISTINCT amount) to remove repeated dimension matches.
  3. Join each sale to the customer's newest dimension version, regardless of when the sale occurred.
  4. Join on customer and the effective-date interval, then verify that each sale matches exactly one dimension version. (correct answer)
Explanation: Whenever you join a fact table to a slowly changing dimension (Type 2), you must account for the fact that each customer has multiple rows covering different time periods. Joining only on customer_id creates a fan-out: every sale row matches several dimension rows, causing each sale's amount to be summed multiple times — which is exactly why the report shows inflated revenue. The correct fix, D, adds an effective-date condition to the join: sale_ts BETWEEN valid_from AND valid_to. This ensures each sale matches exactly one dimension version — the one that was current when the sale occurred. You can then validate that no sale is double-counted (each returns exactly one dimension row) while still preserving the historical attribute values at the time of sale, enabling accurate historical analysis. A is wrong because grouping by every dimension attribute doesn't eliminate fan-out duplication — it just redistributes the inflated numbers across more groups. The total revenue is still overcounted. B is wrong because SUM(DISTINCT amount) removes duplicate values, not duplicate rows. If two sales have the same dollar amount, one gets dropped entirely — a data loss problem, not a fix. C is wrong because joining to the newest dimension version ignores historical context. A customer's attributes at sale time may differ from their current attributes, corrupting both revenue accuracy and historical analysis. As a study tip: whenever you see a Type 2 dimension join, immediately look for an effective-date predicate. Its absence is the most common cause of inflated metrics in dimensional data warehouse queries.