- Which diagnostic most directly identifies dimension keys that violate the assumed join cardinality?
Answer: Group current dimension rows by customer_id and retain counts greater than 1
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. - Which result and correction are both accurate?
Answer: Salary 400 and bonus 30; preaggregate bonuses per employee before joining
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 |
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. - What net amount does the query report for that date?
Answer: 260, after both detail sets are multiplied by the opposing row counts
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×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. - What values will the query return before any correction for join duplication?
Answer: Item total 150 and payment total 200
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×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)×3=150, and SUM(payments.amount) becomes (25+25+50)×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. - The report should total each order once if it has at least one
SHIPPED event. Which approach is most reliable? Answer: Replace the history join with a correlated EXISTS condition for a shipped event
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.
- Which method meets both requirements?
Answer: Assign each bridge row the order total divided by that order's category count
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×$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. - Which correction most directly addresses the duplication while preserving historical conversion accuracy?
Answer: Join by currency and validity period, while enforcing nonoverlapping rate periods
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.
- Approximately what gross-margin percentage will the duplicated join report?
Answer: 48.00%, because the second sale receives twice the weight of the first
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 |
SUM and COUNT results, and that's a classic SQL gotcha on exams and in production. - Which revision correctly prevents note multiplicity from inflating regional revenue?
Answer: Filter orders with EXISTS on order_notes correlated by order_id
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.
- Which query design prevents either measure from being inflated by the other child table?
Answer: Preaggregate invoices and tickets separately by customer, then join both summaries
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."
