Business Analytics Quiz: Merging Datasets
10 questions · exam conditions
0:00
Merging DatasetsQuestion 1 of 10

A company is building a customer-service dashboard. The CRM dataset contains 800800 distinct customer IDs, and the support-ticket summary contains 500500 distinct customer IDs. Exactly 320320 customer IDs occur in both datasets. Each dataset has at most one row per customer ID.

If the datasets are full-outer-joined on customer ID, how many customer-level rows will the result contain?

320320 rows, because only customer IDs appearing in both datasets can be aligned
500500 rows, because the smaller dataset determines the maximum number of matched records
980980 rows, because shared customer IDs are counted only once in the combined result
1,3001{,}300 rows, because a full outer join retains every row from both inputs separately
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Merging Datasets

Practice Merging Datasets 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 Merging Datasets, 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 company is building a customer-service dashboard. The CRM dataset contains 800800 distinct customer IDs, and the support-ticket summary contains 500500 distinct customer IDs. Exactly 320320 customer IDs occur in both datasets. Each dataset has at most one row per customer ID.

If the datasets are full-outer-joined on customer ID, how many customer-level rows will the result contain?

  1. 320320 rows, because only customer IDs appearing in both datasets can be aligned
  2. 500500 rows, because the smaller dataset determines the maximum number of matched records
  3. 980980 rows, because shared customer IDs are counted only once in the combined result (correct answer)
  4. 1,3001{,}300 rows, because a full outer join retains every row from both inputs separately
Explanation: Whenever you see a question involving database joins, your first move should be to recall what each join type keeps. A full outer join retains every row from both datasets — matched or not — filling in NULLs where a match doesn't exist on one side. The key formula here is the inclusion-exclusion principle: ∣A∪B∣=∣A∣+∣B∣−∣A∩B∣|A \cup B| = |A| + |B| - |A \cap B|. Plugging in the numbers, you get 800+500−320=980800 + 500 - 320 = 980. The 320320 shared customer IDs produce single merged rows (one row per customer, not two), while the unmatched customers from each dataset contribute their own rows with NULLs on the missing side. Answer C is correct. Choice A describes an inner join, which keeps only the 320320 rows where both datasets agree. If you wanted exclusively matched records, that's the join to use — but "full outer" explicitly means the opposite. Choice B reflects a misunderstanding that the smaller dataset acts as a ceiling, which applies to neither full outer nor inner joins; no single dataset's size alone determines the output. Choice D double-counts the shared IDs — if every row from both inputs were kept separately, you'd get 800+500=1,300800 + 500 = 1{,}300 rows, but that would mean 320320 customers appear twice, which violates the one-row-per-customer logic of a join on a unique key. As a study habit, memorize the four join types by what they exclude: inner excludes all non-matches, left/right excludes non-matches from one side, and full outer excludes nothing — but still deduplicates on the join key.

Question 2

A marketing team stores daily campaign spend with one row per campaign and date. A separate dataset stores daily conversions at the same grain. Campaign IDs recur on many dates in both datasets. An analyst joins the datasets using only campaign ID and observes substantially inflated row counts.

Which change most directly corrects the merge while preserving daily campaign performance?

  1. Join on both campaign ID and date after confirming that each pair is unique in both datasets (correct answer)
  2. Join on date alone after sorting both datasets in ascending order by campaign ID
  3. Use a full outer join on campaign ID so unmatched dates are retained rather than duplicated
  4. Remove duplicate campaign IDs from both datasets before joining, keeping the first observed date
Explanation: When joining two datasets, inflated row counts almost always signal a many-to-many join — meaning the join key isn't unique in one or both tables. Here, campaign ID alone isn't unique because the same campaign appears on multiple dates. When you join on a non-unique key, each row in the left table matches every row in the right table sharing that campaign ID, multiplying rows unintentionally. The fix is A: join on both campaign ID and date. Together, these two columns form a composite key that uniquely identifies each row in both datasets (one row per campaign per day). This ensures each spend record matches exactly one conversion record, preserving the daily grain without duplication. B is wrong because sorting by campaign ID before joining on date alone doesn't help — date isn't unique across campaigns either, and sorting has no effect on which rows get matched in a join. C is a trap: switching from inner to full outer join changes which unmatched rows are retained, but it doesn't fix the duplication problem caused by the non-unique key. You'd still get inflated counts for rows that do match. D is wrong because removing duplicates by keeping only the first date discards real data — every campaign legitimately has multiple dates, and deleting them destroys the daily performance history you're trying to analyze. Study tip: Whenever you see "inflated row counts after a join," immediately ask: Is the join key unique in both tables? If not, you need a composite key. This is one of the most common data-wrangling pitfalls tested in business analytics.

Question 3

A retention analyst must identify customers who have never made a purchase. The Customers dataset has a non-null customer ID, while the Purchases dataset may contain a few rows whose customer ID is null because of ingestion errors.

Which SQL-style approach most reliably returns only customers with no matching purchase?

  1. Use NOT EXISTS with a correlated subquery checking for a Purchases row with the same customer ID (correct answer)
  2. Use NOT IN with a subquery returning the full customer-ID column from Purchases
  3. Inner-join Customers to Purchases and retain rows where the purchase customer ID is null
  4. Left-join Customers to Purchases, then retain rows where the purchase customer ID is null after filtering to status = 'valid'
Explanation: Whenever you see a question about filtering for "no match" in SQL, the critical hidden trap is NULL propagation — and it changes everything about which approach you should trust. When you use NOT EXISTS with a correlated subquery (answer A), SQL checks whether any matching row exists in Purchases for each customer. Crucially, NOT EXISTS evaluates to TRUE when zero rows are found, regardless of whether any NULLs are present in the Purchases table. This makes it robust against the ingestion-error NULLs described in the passage, and it correctly returns every customer with no real purchase record. Answer B is the classic trap. NOT IN fails silently when the subquery returns even a single NULL value. Because NULL represents an unknown, SQL cannot confirm that any customer ID is definitively "not in" a set containing an unknown. The entire NOT IN condition evaluates to UNKNOWN, meaning no rows are returned at all — a catastrophic, silent failure for the analyst. Answer C describes an inner join, which only returns rows that have a match in both tables. Customers with no purchases are excluded entirely from an inner join result, so looking for nulls there is nonsensical. Answer D is closer to valid logic (a left join does preserve unmatched customers), but adding a filter like status = 'valid' in the WHERE clause effectively converts the left join into an inner join, eliminating the unmatched rows you actually want. Study tip: Memorize this rule — NOT IN breaks on NULLs; NOT EXISTS does not. On any exam question involving "no match" filtering with potentially dirty data, NOT EXISTS is almost always the safest choice.

Question 4

Dataset L contains three rows whose account IDs are 1, 2, and null. Dataset R contains two rows whose account IDs are 2 and null. Non-null account IDs are unique in each dataset. The analyst performs a standard SQL inner join using L.account_id = R.account_id.

How many rows will the join return, and why?

  1. Zero rows, because the presence of a null key prevents the equality join from being evaluated
  2. One row, because only account ID 2 satisfies the equality condition on both sides (correct answer)
  3. Two rows, because account ID 2 matches and the two null account IDs also match
  4. Three rows, because an inner join retains every distinct account ID found in the left dataset
Explanation: Whenever you see a SQL join question, your first instinct should be to think carefully about how NULL values behave — they are one of the most commonly misunderstood concepts in database logic. In SQL, NULL represents an unknown value. The equality operator = compares two known values, so the expression NULL = NULL does not evaluate to TRUE — it evaluates to NULL (unknown). Because an inner join only retains rows where the join condition evaluates to TRUE, rows with null keys on either side are silently excluded. That means only account ID 2 appears in both datasets with a non-null, matchable value, producing exactly one row — confirming that B is correct. Choice A is wrong because the presence of a null key doesn't prevent the join from running at all — it simply means those particular rows fail to match. The join executes normally and returns the rows that do satisfy the condition. Choice C reflects a common intuition trap: students assume "null equals null" the way a blank field might match another blank field in everyday logic, but SQL deliberately treats nulls as incomparable unknowns, not equal values. Choice D is wrong on two fronts — inner joins don't retain every row from the left dataset (that's a LEFT JOIN behavior), and even if they did, three rows would be illogical since R only contains two rows. As a study habit, remember this rule: NULL is never equal to anything, including itself. On any exam question involving joins or filtering, ask yourself whether nulls are present and how the specific join type handles them.

Question 5

A planning dataset contains one sales target per region and month. An actual-sales dataset contains one row per store and month, with several stores in each region. After joining on region and month, a dashboard sums both actual sales and targets. Actual sales are correct, but regional targets are multiplied by the number of stores.

Which modification best prevents target inflation while retaining a valid region-month comparison?

  1. Join on month only, allowing the dashboard to combine stores before displaying regional totals
  2. Replace the join with a full outer join so unmatched targets and stores remain in the output
  3. Divide each regional target by the total number of stores across all regions before joining
  4. Aggregate actual sales to region-month first, then join to targets at the same region-month grain (correct answer)
Explanation: Whenever you see a join between datasets at different levels of detail — one row per region versus one row per store — your first instinct should be to ask: what grain am I joining on, and will that create duplicate values? This is the classic fan-out problem: joining a summary-level value (one target per region-month) to a detail-level table (many stores per region-month) causes that summary value to repeat for every matching detail row. When you then sum it, you're summing duplicates. Option D solves this cleanly by collapsing the actual-sales table to the region-month grain before the join. Once both tables share the same grain — one row per region-month — the join is one-to-one, and summing produces correct totals for both actuals and targets. Option A is wrong because joining on month alone makes the mismatch worse, not better — stores from different regions would incorrectly match against every target regardless of region. Option B is a red herring; a full outer join preserves unmatched rows but does nothing to fix the duplication that already occurs on matched rows. The inflation problem remains entirely intact. Option C applies the wrong correction — dividing by the total number of stores globally ignores that each region has a different store count, so most regional targets would still be wrong after the adjustment. A useful rule of thumb: always resolve grain mismatches before aggregating. When joining datasets, confirm they share the same level of detail first. If they don't, pre-aggregate the more granular table up to match the summary table's grain — never the other way around.

Question 6

An online retailer has an experiment-assignment dataset containing exactly one row for each of 120120 assigned customers. Its purchases dataset contains 150150 purchase rows made by 7070 of those customers; the remaining assigned customers made no purchases. The analyst left-joins assignments to purchases on customer ID.

Assuming every purchase customer ID matches an assigned customer, how many rows will the merged dataset contain, and what will represent customers with no purchases?

  1. 150150 rows, with customers who made no purchases omitted from the merged dataset
  2. 200200 rows, with one null-extended row for each customer who made no purchases (correct answer)
  3. 220220 rows, with all assignment and purchase rows retained as separate observations
  4. 270270 rows, with every assignment row appended to every purchase row by customer
Explanation: When working with SQL-style joins in data analysis, always ask yourself: which table drives the row count, and how does the join handle non-matches? A left join keeps every row from the left table (here, the 120 assigned customers) and attaches matching rows from the right table (purchases). The critical multiplier is that when one customer has multiple matching rows in the right table, the left table row fans out to match each one. So the row count depends on how many purchases each customer made, not just how many customers exist. Here, 70 customers made 150 total purchases. Each purchase creates one merged row, accounting for all 150 purchase rows. The remaining 120−70=50120 - 70 = 50 customers made no purchases — but because it's a left join, they are retained with null values filling in the purchase columns. That gives 150+50=200150 + 50 = 200 rows, confirming B is correct. A is wrong because it describes an inner join, which drops non-matching left-table rows entirely — a left join never omits them. C's figure of 220 has no logical basis; it seems to add 120 + 100 arbitrarily, confusing a union-style operation with a join. D describes a cross join (Cartesian product), where every assignment row pairs with every purchase row, yielding 120×150=18,000120 \times 150 = 18{,}000 rows — not 270. The number 270 itself is simply 120+150120 + 150, which would be a row-stack (union), not a join at all. For any join question, memorize the mantra: left join = all left rows, matched or not; row count scales with right-table matches.

Question 7

A customer dataset is left-joined to an orders dataset. Management wants every customer retained, but order columns should be populated only for completed orders. Some customers have no orders, and some have only canceled orders.

Which query design meets the requirement without unintentionally dropping customers?

  1. Perform the left join on customer ID, then apply WHERE o.status = 'completed' to the merged rows
  2. Perform an inner join on customer ID and include o.status = 'completed' in the join condition
  3. Perform the left join, then apply WHERE o.status = 'completed' OR o.status IS NULL to the merged rows
  4. Perform the left join with o.status = 'completed' moved into the ON clause alongside the customer ID condition (correct answer)
Explanation: When filtering on a column from the right table in a left join, the placement of your filter condition determines whether non-matching rows survive. This is one of the most commonly tested SQL design traps in business analytics. In a left join, every row from the left table (customers) is preserved. Rows with no match in the right table (orders) receive NULL for all order columns. The critical insight is this: if you filter on a right-table column after the join using WHERE, you implicitly convert the left join into an inner join — because NULL values fail equality checks and get dropped. Moving that filter condition into the ON clause avoids this entirely. The join engine sets order columns to NULL when the condition isn't met, but the customer row itself is kept. That's exactly what D does — it places o.status = 'completed' inside the ON clause, so customers without completed orders still appear, just with NULL order data. A is the classic trap. Filtering with WHERE o.status = 'completed' after the join silently removes customers whose orders are canceled or absent, since NULL ≠ 'completed'. B makes it worse by switching to an inner join entirely, which only returns customers who have at least one order on record — dropping any customer with no orders whatsoever. C is a reasonable attempt at fixing A, but it's fragile. It retains customers with no orders (NULL rows pass) yet still drops customers who only have canceled orders, because those rows have a non-NULL status that fails the filter. As a rule of thumb: when you need to filter a right table's column while keeping all left-table rows, always push that condition into the ON clause, not WHERE.

Question 8

A retailer maintains a product-price history with one row whenever a new price becomes effective. A sales dataset records each transaction's product ID and transaction timestamp. The analyst must attach the price that was in effect when each sale occurred.

Which merge logic most accurately satisfies this requirement?

  1. Join on product ID only and retain the lowest historical price associated with each transaction
  2. Join on product ID and require the transaction timestamp to equal the price-effective timestamp
  3. For each sale, match the latest price record whose effective timestamp is not after the sale (correct answer)
  4. For each sale, match the earliest price record whose effective timestamp follows the sale
Explanation: When working with time-varying data like price histories, you're being tested on a concept called an as-of join (also called a temporal join). The core challenge is identifying which version of a slowly changing record was active at a specific point in time — not just which records share a common key. The right approach, captured in C, is to find the most recent price record whose effective date does not exceed the transaction date. Think of it like looking backward in time from the moment of the sale: you want the last price that "kicked in" before or exactly when the sale happened. This correctly handles scenarios like a product being repriced multiple times — you'll always land on the price the customer actually saw. A is tempting if you think "historical" means you should aggregate, but taking the lowest historical price is arbitrary and analytically meaningless. There's no business reason the minimum past price reflects what was in effect at the time of purchase. B requires an exact timestamp match between the transaction and the price-effective date. This would fail almost every time in practice, since customers rarely purchase the exact moment a new price is entered into the system. Equality joins work for static attributes, not time-bounded ones. D goes in the wrong direction entirely — matching the earliest price record after the sale would give you a future price that wasn't yet active, which is the opposite of what you need. As a study tip: whenever a question involves event timestamps paired with slowly changing reference data (prices, tax rates, exchange rates), immediately think "as-of join — match the latest record that precedes or equals the event."

Question 9

Dataset A contains three rows with key K1, one row with key K2, and two rows with key K3. Dataset B contains two rows with key K1, four rows with key K2, and one row with key K4. An analyst left-joins A to B on the key without deduplicating either dataset.

How many rows will the merged dataset contain?

  1. 77 rows, because a left join retains all six rows from A plus the one unmatched K4 row from B
  2. 1010 rows, because only the matched K1 and K2 combinations are produced and the K3 rows are dropped
  3. 1212 rows, because matched keys multiply row counts and the two unmatched K3 rows are retained once each (correct answer)
  4. 1313 rows, because all rows from both datasets are retained in addition to the repeated matched combinations
Explanation: When working with joins on non-unique keys, the critical concept is row multiplication: when both datasets have multiple rows sharing the same key, a join produces the Cartesian product of those rows for that key. Here's how to work through this problem key by key. For K1, Dataset A has 3 rows and Dataset B has 2 rows, producing 3×2=63 \times 2 = 6 rows. For K2, A has 1 row and B has 4 rows, producing 1×4=41 \times 4 = 4 rows. For K3, A has 2 rows but B has no matching rows — because this is a left join, those rows are retained once each with nulls filled in for B's columns, contributing 22 rows. K4 exists only in B, so it is dropped entirely in a left join. The total is 6+4+2=126 + 4 + 2 = 12 rows, confirming C is correct. A is wrong because it misunderstands which side controls retention in a left join — B's unmatched K4 rows are dropped, not added. It also ignores row multiplication entirely. B is wrong on two counts: it drops the K3 rows (which a left join preserves) and it miscalculates matched combinations, suggesting only the matched rows themselves are counted rather than their cross product. D is wrong because it implies all rows from both datasets survive, which describes a full outer join, not a left join. The 1313 figure has no valid calculation path. As a study rule: whenever you see non-unique keys, write out rowsA×rowsB\text{rows}_A \times \text{rows}_B per key before adding anything up — row explosion is the most commonly missed trap in join questions.

Question 10

An Orders dataset has one row per order and includes total order revenue. An OrderItems dataset has several rows per order. After joining the datasets, an analyst calculates average order revenue from the merged rows and obtains a value higher than the average calculated directly from Orders. Larger orders tend to contain more item rows.

Which approach best supports item-related analysis while preventing order revenue from being weighted by the number of items?

  1. Use an inner join and average the repeated order-revenue field separately within each product category
  2. Aggregate item information to one row per order, then join that result to the Orders dataset (correct answer)
  3. Use a full outer join and replace missing order-revenue values with the overall order average
  4. Join the detail rows first, then divide the resulting average by the mean number of items
Explanation: When joining a one-to-many dataset (like Orders joined to OrderItems), a critical concept is fan-out inflation — every time an order has multiple item rows, its revenue figure gets repeated, artificially inflating any average you calculate on the merged result. Questions like this test whether you know how to preserve metric integrity during a join. The cleanest solution is option B: aggregate the item-level data first (counts, sums, etc.) down to one row per order, then join that summary to the Orders dataset. Now each order appears exactly once, so averaging revenue produces the same result as averaging directly from Orders — no distortion. You also retain all item-level summaries for analysis. Option A is tempting but flawed. Averaging a repeated revenue field within product categories still counts large orders multiple times (once per item), so the inflation persists inside every category rather than disappearing. Option C introduces a different problem entirely. A full outer join addresses missing orders or items, not the fan-out issue. Replacing nulls with the overall average is a data-imputation technique, not a remedy for row multiplication — the inflated averaging problem remains for matched rows. Option D attempts a mathematical correction: dividing the inflated average by the mean item count. This is conceptually shaky because order sizes vary; a single global divisor won't correctly undo per-order inflation without strong distributional assumptions. Strategy tip: Whenever you see a one-to-many join followed by an aggregation, immediately ask yourself, "Will my metric be counted once per logical unit, or once per detail row?" If the latter, aggregate before joining.