SQL Quiz: Join Duplication Issues

10 questions · exam conditions

0:00

Join Duplication IssuesQuestion 1 of 10

A customer summary joins customers to invoices and support_tickets, then groups by customer. Each customer may have many invoices and many tickets. The output must show the customer's total invoice amount and ticket count.

Which query design prevents either measure from being inflated by the other child table?

Join the detail tables directly, then use SUM(DISTINCT invoice_amount) and COUNT(*)

Preaggregate invoices and tickets separately by customer, then join both summaries

Join the detail tables directly, then count distinct tickets but sum invoice rows normally

Apply DISTINCT to the final customer rows after calculating both grouped aggregates

← Back to quizzes

SQL Quiz

SQL Quiz: Join Duplication Issues

Practice Join Duplication Issues 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 Join Duplication Issues, 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

Which query design prevents either measure from being inflated by the other child table?

Join the detail tables directly, then use SUM(DISTINCT invoice_amount) and COUNT(*)
Preaggregate invoices and tickets separately by customer, then join both summaries (correct answer)
Join the detail tables directly, then count distinct tickets but sum invoice rows normally
Apply DISTINCT to the final customer rows after calculating both grouped aggregates

Explanation: When you join a parent table to two independent child tables simultaneously, you create a Cartesian product between those child tables for each parent row. For example, if a customer has 3 invoices and 4 tickets, a direct join produces 12 rows — every invoice paired with every ticket. Any aggregate you compute on that result set will be inflated. The reliable solution is B: preaggregate each child table down to one row per customer before joining. Compute SUM(invoice_amount) grouped by customer in one subquery, compute COUNT(*) grouped by customer in another, then join both summaries to the customers table. Each customer now has exactly one invoice total row and one ticket count row — no cross-multiplication occurs, and both measures are accurate. A is tempting but flawed. SUM(DISTINCT invoice_amount) deduplicates by value, not by row identity. If two invoices happen to share the same amount (e.g., two $50 invoices), one gets silently dropped, undercounting revenue. COUNT(*) still counts the inflated row set. C partially addresses the problem by counting distinct tickets, but SUM still runs over the duplicated invoice rows caused by the multi-child join, so the invoice total is wrong. D misunderstands where the inflation happens. Applying DISTINCT to final customer rows collapses duplicates at the customer level, but the aggregates (SUM, COUNT) have already been computed over the bloated intermediate rows — the damage is done before DISTINCT runs. Study tip: Whenever a query joins one parent to two or more child tables, always preaggregate the children first. Think of it as "flatten before you join."

Question 2

A fact table is expected to join to exactly one current customer-dimension row per customer_id. Revenue increased after the join, and the team suspects that some customers have multiple rows marked as current.

Which diagnostic most directly identifies dimension keys that violate the assumed join cardinality?

Group current dimension rows by customer_id and retain counts greater than $1$ (correct answer)
Group fact rows by revenue amount and retain amounts occurring more than once
Compare SUM(revenue) with SUM(DISTINCT revenue) after completing the join
Count distinct customer identifiers in the fact table before applying any join

Explanation: When working with star-schema joins, a critical assumption is one-to-one cardinality between the fact table and a filtered dimension (in this case, rows marked as "current"). If that assumption breaks — meaning multiple current rows exist per customer_id — every join will multiply fact rows, inflating aggregates like revenue. Your diagnostic goal is to find which dimension keys violate this uniqueness constraint. Answer A does exactly that. By grouping the dimension table on customer_id where is_current = TRUE and filtering for COUNT(*) > 1, you directly surface the offending keys — the ones that will cause fan-out during the join. This is the most targeted, efficient diagnostic because it interrogates the source of the problem before the join even runs. Answer B is a red herring. Grouping fact rows by revenue amount has nothing to do with join cardinality; duplicate revenue values are perfectly normal and unrelated to dimension key violations. Answer C compares SUM(revenue) with SUM(DISTINCT revenue) post-join. Even if these differ, the comparison doesn't tell you which customer IDs are duplicated — and DISTINCT on a continuous monetary value is rarely meaningful, since legitimate duplicate amounts would also be suppressed. Answer D counts distinct customer IDs in the fact table before the join. This tells you how many unique customers exist in the fact data, not whether the dimension has duplicate current rows — it diagnoses the wrong table entirely. Study tip: When a join produces inflated aggregates, always ask "which side is violating cardinality?" Then query that side in isolation using GROUP BY + HAVING COUNT(*) > 1 — a pattern worth memorizing for data integrity checks.

Question 3

Employee A has salary $100$ and two bonus rows of $10$ and $20$ . Employee B has salary $200$ and no bonus rows. A report left-joins employees to bonuses, then calculates SUM(employee.salary) and SUM(COALESCE(bonus.amount, 0)) across all employees.

Which result and correction are both accurate?

Salary $300$ and bonus $30$ ; adding COALESCE to salary prevents duplication
Salary $400$ and bonus $30$ ; preaggregate bonuses per employee before joining (correct answer)
Salary $400$ and bonus $60$ ; divide both aggregates by the bonus-row count
Salary $300$ and bonus $60$ ; use SUM(DISTINCT salary) for every payroll report

Explanation: Whenever you join a one-to-many relationship before aggregating, you risk fan-out duplication — parent rows get repeated once for every matching child row, inflating aggregates on the parent side. Here, Employee A has two bonus rows, so after the LEFT JOIN, A's salary of

100

appears twice in the result set. Employee B has no bonus rows, but the LEFT JOIN still produces one row with a NULL bonus. The raw joined table looks like this:

Employee	Salary	Bonus
A	100	10
A	100	20
B	200	NULL

So SUM(salary) =

100 + 100 + 200 = 400

(duplicated!), and SUM(COALESCE(bonus.amount, 0)) =

10 + 20 + 0 = 30

. Answer B correctly identifies both values and prescribes the right fix: preaggregate bonuses per employee (e.g., in a subquery or CTE) before joining, so each employee appears exactly once. A is wrong because it states salary =

300

, missing the duplication entirely, and COALESCE on salary doesn't prevent fan-out — it only handles NULLs. C is wrong because bonus =

60

is fabricated; dividing aggregates by a row count is a fragile hack, not a reliable fix. D is wrong because salary =

300

is again incorrect, and SUM(DISTINCT salary) would break any report where two employees legitimately earn the same amount. Study tip: Whenever you see a LEFT JOIN to a detail/child table followed by aggregation, immediately ask yourself: "Will parent rows be duplicated?" If yes, preaggregate the child table first.

Question 4

Orders are connected to categories through a many-to-many bridge. One order totaling $120$ belongs to three categories, and another order totaling $80$ belongs to one category. A category report joins orders through the bridge and sums order totals. Management wants every category represented while requiring category totals to add back to overall order revenue.

Which method meets both requirements?

Assign the entire order total to every category and deduplicate only the final rows
Count distinct order identifiers but leave each category's revenue sum unchanged
Use SUM(DISTINCT order_total) independently within each category grouping
Assign each bridge row the order total divided by that order's category count (correct answer)

Explanation: Whenever a many-to-many join connects orders to categories, the same order row gets duplicated once per category. This duplication is the core trap: if you naively sum order totals across categories, you overcount revenue. Consider the scenario. The

\$120

order appears in three bridge rows; the

\$80

order appears in one. True total revenue is

\$120 + \$80 = \$200

. The requirement is that category totals still reconcile to

\$200

while every category gets a meaningful revenue figure. Option D solves this cleanly: divide each order's total by its category count before assigning it to each bridge row. The

\$120

order contributes

\$40

to each of its three categories (

3 \times \$40 = \$120

), and the

\$80

order contributes the full

\$80

to its one category. Sum across all categories:

\$40 + \$40 + \$40 + \$80 = \$200

. Both requirements satisfied. Option A fails the second requirement immediately — assigning the full

\$120

to all three categories inflates category totals to

\$360 + \$80 = \$440

, and deduplicating final rows doesn't fix revenue math. Option B counting distinct order IDs is a useful diagnostic metric but does nothing to correct the summed revenue figures, leaving overcounting intact. Option C uses SUM(DISTINCT order_total) per category, which drops duplicate values, not duplicate orders — if two different orders happened to share the same total, one would be silently excluded. The study tip: whenever you see a many-to-many join with aggregation, immediately ask yourself "how many times does each fact row appear?" and prorate accordingly.

Question 5

A sales table has two rows. The first has revenue $100$ and cost $60$ for a product with one tag. The second has revenue $200$ and cost $100$ for a product with two tags. The report joins sales to product tags and calculates SUM(revenue - cost) / SUM(revenue) without grouping by tag.

Approximately what gross-margin percentage will the duplicated join report?

$40.00\%$ , because both rows retain their individual margin percentages
$46.67\%$ , because numerator and denominator are duplicated proportionally
$48.00\%$ , because the second sale receives twice the weight of the first (correct answer)
$50.00\%$ , because the two product-level margin percentages are averaged

Explanation: Whenever a join multiplies rows, aggregate functions like SUM operate on the inflated result set — not the original data. That's the core trap here. Walk through the math carefully. The first sale (revenue

100

, cost

60

, one tag) appears once. The second sale (revenue

200

, cost

100

, two tags) appears twice after the join. So the effective dataset becomes:

Revenue	Cost
100	60
200	100
200	100

Now apply the formula:

\frac{\text{SUM(revenue - cost)}}{\text{SUM(revenue)}} = \frac{(40) + (100) + (100)}{100 + 200 + 200} = \frac{240}{500} = 48\%

. That confirms C is correct — the second sale is counted twice, so it carries double the weight. A is wrong because the individual row margins (

40\%

and

50\%

) are never preserved independently when you compute a single aggregate over all rows. B describes a scenario where duplication cancels out proportionally, but that only holds if all rows were duplicated equally — here only the second row is doubled, which skews the result. D reflects a simple average of the two product-level margins:

(40\% + 50\%) / 2 = 45\%

, which ignores both revenue weighting and the join duplication entirely. As a study strategy, whenever you see a query joining a fact table to a many-valued dimension (like tags), immediately ask: could any rows be duplicated? Fan-out joins silently inflate SUM and COUNT results, and that's a classic SQL gotcha on exams and in production.

Question 6

One order has two item rows with line amounts $30$ and $20$ . The same order has three payment rows with amounts $25$ , $25$ , and $50$ . A query joins orders to both order_items and payments on order_id, then calculates SUM(order_items.line_amount) and SUM(payments.amount) for that order.

What values will the query return before any correction for join duplication?

Item total $50$ and payment total $100$
Item total $150$ and payment total $200$ (correct answer)
Item total $100$ and payment total $300$
Item total $300$ and payment total $600$

Explanation: When you join one table to two separate tables on the same key, you create a cross-multiplication effect — every row from the first joined table pairs with every row from the second joined table, inflating your aggregates before any SUM is calculated. Here's how the math works for this order: joining order_items (2 rows) to payments (3 rows) produces

2 \times 3 = 6

combined rows. Each item row gets duplicated 3 times (once per payment), and each payment row gets duplicated 2 times (once per item). So SUM(line_amount) becomes

(30 + 20) \times 3 = 150

, and SUM(payments.amount) becomes

(25 + 25 + 50) \times 2 = 200

. That confirms B is correct. A (

50

and

100

) reflects the true totals with no duplication — this would be correct only if the joins didn't inflate anything, which isn't how SQL works here. C (

100

and

300

) inverts the pattern:

100

would be item total doubled (multiplied by 2 instead of 3) and

300

would be payment total tripled — the multipliers are swapped. D (

300

and

600

) applies an incorrect multiplier across the board, as if each set were multiplied by 6 rather than by the other table's row count. As a study tip, whenever you see a query joining to multiple detail tables on the same parent key, immediately ask: "What is the cross-product row count, and which direction is each column being multiplied?" This fan-out trap is one of the most common sources of subtly wrong aggregate results in SQL.

Question 7

The orders table has one row per order. The order_status_history table can contain several SHIPPED events for an order because shipments may be retried or split. A report uses JOIN order_status_history h ON h.order_id = o.order_id AND h.status = 'SHIPPED' and then calculates SUM(o.order_total).

The report should total each order once if it has at least one SHIPPED event. Which approach is most reliable?

Replace the history join with a correlated EXISTS condition for a shipped event (correct answer)
Change the aggregate to SUM(DISTINCT o.order_total) after the history join
Add DISTINCT to the final grouped result while retaining the history join
Divide each order total by the number of all status-history rows for that order

Explanation: Whenever a JOIN can produce duplicate rows for a single source record, any aggregate over that source will be inflated — this is the core trap being tested here. When an order has multiple SHIPPED rows in order_status_history, the join fans out that order into multiple rows, causing SUM(o.order_total) to count the same order total two, three, or more times. The cleanest fix is A: replace the join with a correlated EXISTS subquery. This checks whether at least one SHIPPED event exists for each order without duplicating the order row itself. The orders table is never fanned out, so each order_total is summed exactly once — precisely what the report requires. B is tempting but unreliable. SUM(DISTINCT o.order_total) deduplicates by value, not by order. If two different orders happen to share the same total (say, both are $49.99), only one of those totals will be counted — silently undercounting revenue. C has a similar weakness: adding DISTINCT to the result set deduplicates by the entire row, but if you're grouping only by order, the fan-out already occurred before grouping and the damage is done. D — dividing by the count of history rows — is fragile and mathematically incorrect in general; the number of history rows varies per order and includes non-SHIPPED statuses, making the divisor meaningless. As a study habit, whenever you see a one-to-many join feeding into SUM or COUNT, immediately ask yourself: can this join duplicate my base rows? If yes, consider EXISTS or a subquery to isolate the aggregation.

Question 8

A transaction table contains one row per transaction, including currency_code and transaction_date. An exchange-rate table contains multiple historical rows per currency, each with valid_from, valid_to, and rate. A report joins the tables only on currency_code and then sums amount * rate. The converted total is unexpectedly high.

Which correction most directly addresses the duplication while preserving historical conversion accuracy?

Join each currency to its latest rate, regardless of the transaction date
Use SUM(DISTINCT amount * rate) after performing the existing currency join
Join by currency and validity period, while enforcing nonoverlapping rate periods (correct answer)
Group duplicated transactions and retain the maximum converted amount for each transaction

Explanation: When joining transaction data to a slowly changing dimension (like historical exchange rates), the core challenge is ensuring each transaction matches exactly one valid rate — not every rate that ever existed for that currency. This is called a temporal join, and it's one of the most common sources of unintended row multiplication in SQL. The reason the converted total is unexpectedly high is that joining only on currency_code creates a many-to-many relationship: each transaction matches every historical rate row for that currency, so amount * rate gets summed multiple times. Option C corrects this by adding a date-range condition — something like transaction_date BETWEEN valid_from AND valid_to — while also enforcing nonoverlapping rate periods to guarantee exactly one rate per transaction. This produces the correct one-to-one match and preserves the historical accuracy the report requires. Option A is tempting but wrong: always using the latest rate ignores the fact that a transaction from two years ago should use the rate that was valid then, not today's rate. This trades duplication for incorrect valuation. Option B, using SUM(DISTINCT amount * rate), is a flawed workaround — it would silently drop legitimate duplicate values (e.g., two transactions with the same product of amount × rate), producing an undercount rather than fixing the root cause. Option D, retaining the maximum converted amount per transaction, arbitrarily picks the highest rate rather than the historically correct one, distorting results in the opposite direction. The study tip here: whenever you see a join to a historical or "versioned" table, immediately ask yourself whether you've constrained the join to a single valid row per record. If not, you almost certainly have a fan-out (row multiplication) problem.

Question 9

On one date, sales contains three rows with amounts $100$ , $50$ , and $25$ . On that same date, refunds contains two rows with amounts $20$ and $10$ . A query joins the two tables on date and computes SUM(sales.amount) - SUM(refunds.amount).

What net amount does the query report for that date?

$145$ , using the independent source totals before the date join
$175$ , because only the sales rows are duplicated by the join
$260$ , after both detail sets are multiplied by the opposing row counts (correct answer)
$320$ , after subtracting the unduplicated refunds from duplicated sales

Explanation: Whenever a SQL query joins two tables without aggregating first, you need to think carefully about how the join multiplies rows before any SUM is applied — this is one of the most common pitfalls in SQL aggregation. Here's what actually happens: joining sales (3 rows) to refunds (2 rows) on date produces a Cartesian-style cross join for that date —

3 \times 2 = 6

combined rows. In each of those 6 rows, every sales amount is paired with every refund amount. So SUM(sales.amount) adds each sales value twice (once per refund row):

100 + 50 + 25 = 175

, then doubled to

350

. Similarly, SUM(refunds.amount) adds each refund value three times (once per sales row):

20 + 10 = 30

, then tripled to

90

. The result is

350 - 90 = 260

, making C correct. A is wrong because it assumes the sums are computed independently before joining (

175 - 30 = 145

) — that's only true if you aggregate in subqueries first, which this query does not do. B incorrectly assumes only the sales side gets duplicated, giving

350 - 30 = 320

— but both sides are inflated by the join. D actually describes B's calculation (

320

) with a misleading label, so it's doubly wrong. As a study tip: whenever you see a join combined with SUM, ask yourself whether aggregation happens before or after the join. If it's after, expect both sides to be multiplied by the opposing table's row count — and consider using subqueries or CTEs to pre-aggregate safely.

Question 10

A regional revenue query joins each order to order_notes because only orders having at least one note should qualify. An order may have several notes. The query groups by region, sums order_amount, and places DISTINCT immediately after SELECT.

Which revision correctly prevents note multiplicity from inflating regional revenue?

Filter orders with EXISTS on order_notes correlated by order_id (correct answer)
Use SUM(DISTINCT order_amount) while retaining the inner join to notes
Retain the join and apply DISTINCT to the region and aggregate output
Use a left join and reject regions having a null note identifier

Explanation: Whenever you join a fact table to a related table that can have multiple matching rows, you risk fan-out: each qualifying row gets duplicated once per match, causing aggregates like SUM to count the same value multiple times. That's exactly the trap here — joining orders to order_notes means one order with three notes appears three times, tripling its order_amount in the regional sum. The cleanest fix is option A: replace the join entirely with an EXISTS subquery correlated on order_id. EXISTS checks whether at least one note exists and returns a simple true/false — the order row never duplicates, so SUM(order_amount) aggregates each order exactly once. This satisfies the original business rule (only orders with at least one note qualify) without distorting revenue. Option B attempts SUM(DISTINCT order_amount) while keeping the inner join. This only deduplicates identical amounts, so two orders that happen to share the same dollar value would be undercounted, while an order duplicated three times with a unique amount would still be overcounted. It solves the wrong problem. Option C suggests applying DISTINCT to the region and aggregate output. DISTINCT on the SELECT level deduplicates output rows, but the SUM is already computed from the duplicated rows before DISTINCT applies — the inflated total is baked in by then. Option D uses a left join and filters on a non-null note ID, which is functionally an inner join again, so fan-out remains. Study tip: Whenever a join is used only to filter (not to retrieve columns from the joined table), prefer EXISTS or IN — it keeps cardinality clean and your aggregates trustworthy.