Business Analytics Quiz: Group By Summaries
10 questions · exam conditions
0:00
Group By SummariesQuestion 1 of 10

An Orders dataset has one row for order 101101 with an order total of $100\$100 and one row for order 102102 with an order total of $80\$80. In an OrderItems dataset, order 101101 has two item rows and order 102102 has one item row. An analyst joins the datasets on order ID, groups by sales region, and calculates SUM(order_total) without first restoring one row per order.

What regional order total will the joined-data summary report?

$180\$180 because each distinct order contributes its total exactly once
$90\$90 because the two order totals are averaged after the join
$200\$200 because only the order with multiple items is duplicated
$280\$280 because the first order total appears on two joined rows
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Group By Summaries

Practice Group By Summaries 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 Group By Summaries, 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

An Orders dataset has one row for order 101101 with an order total of $100\$100 and one row for order 102102 with an order total of $80\$80. In an OrderItems dataset, order 101101 has two item rows and order 102102 has one item row. An analyst joins the datasets on order ID, groups by sales region, and calculates SUM(order_total) without first restoring one row per order.

What regional order total will the joined-data summary report?

  1. $180\$180 because each distinct order contributes its total exactly once
  2. $90\$90 because the two order totals are averaged after the join
  3. $200\$200 because only the order with multiple items is duplicated
  4. $280\$280 because the first order total appears on two joined rows (correct answer)
Explanation: Whenever you join tables in analytics, you need to track how many rows each record produces in the result — because aggregate functions like SUM operate on rows, not on logical entities. Here's what happens in this scenario. Order 101 has two item rows in OrderItems, so after joining, it appears twice in the result, each time carrying its $100\$100 order total. Order 102 has one item row, so it appears once with its $80\$80 total. When you call SUM(order_total), you're summing all rows: $100+$100+$80=$280\$100 + \$100 + \$80 = \$280. That confirms D is correct — the duplication of order 101 inflates the sum. A describes the intended outcome if you had deduplicated first (e.g., by aggregating order totals before joining, or using DISTINCT carefully). $100+$80=$180\$100 + \$80 = \$180 is the correct business answer, but it's not what this flawed query produces. B introduces averaging, which no part of the query performs — SUM and AVG are entirely different aggregations, and nothing here divides by two. C gets the duplication concept partially right but claims only $200\$200, as if order 102 were also doubled — it wasn't, because it has only one item row and therefore appears only once. As a study habit, whenever you see a join followed by an aggregation, always ask yourself: "How many rows does each source record produce after the join?" Fan-out from one-to-many relationships is one of the most common sources of inflated metrics in real-world data pipelines.

Question 2

An orders dataset contains one row per order, including region, status, and profit. A manager wants regions whose combined profit from completed orders exceeds $50,000\$50{,}000. Individual completed orders need not exceed that amount.

Which SQL-like sequence correctly produces the requested group-by summary?

  1. WHERE status = 'Completed' GROUP BY region HAVING SUM(profit) > 50000 (correct answer)
  2. GROUP BY region HAVING status = 'Completed' AND SUM(profit) > 50000
  3. WHERE status = 'Completed' AND profit > 50000 GROUP BY region
  4. WHERE status = 'Completed' GROUP BY region HAVING AVG(profit) > 50000
Explanation: When working with SQL aggregation, the order of clauses matters enormously — and so does understanding when each clause filters data. The key framework here is: WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. Keep that sequence in mind and this question becomes straightforward. The manager wants regions where the total profit from completed orders exceeds $50,000\$50{,}000. Answer A handles this perfectly. First, WHERE status = 'Completed' removes any non-completed orders from consideration. Then GROUP BY region bundles the remaining rows by region. Finally, HAVING SUM(profit) > 50000 checks whether each region's combined profit clears the threshold — exactly what was requested. B is invalid because HAVING cannot filter on a non-aggregated column like status after grouping has already collapsed the rows. The status filter must happen before grouping, using WHERE. C applies profit > 50000 in the WHERE clause, which incorrectly eliminates individual orders under $50,000\$50{,}000 — but the passage explicitly says individual orders need not exceed that amount. This would exclude valid orders and undercount regional totals. D uses AVG(profit) instead of SUM(profit), which answers a completely different question (average order profitability per region, not total), making it the wrong aggregate function for the stated goal. A useful memory aid: think of the clause order as a pipeline — WHERE narrows the raw rows, GROUP BY bundles them, HAVING filters the bundles. Any answer that disrupts this pipeline or uses the wrong aggregate is a trap.

Question 3

An experiment is summarized by acquisition channel. In the paid channel, variant A has 99 conversions among 1010 users, while variant B has 8080 among 100100 users. In the organic channel, variant A has 11 conversion among 1010 users, while variant B has 00 among 11 user.

Which conclusion is best supported when the channel-level groups are also aggregated overall?

  1. A converts at 50%50\% and B at 40%40\%, because the channel rates should be averaged with equal weight
  2. A converts at 100%100\% and B at 80%80\%, because only the paid-channel denominators are used in the overall rate
  3. A converts at 50%50\% and B at about 79.2%79.2\%, proving that B causes higher overall conversion
  4. A converts at 50%50\% and B at about 79.2%79.2\%, but channel composition confounds the aggregate comparison (correct answer)
Explanation: Whenever you see aggregated data split by subgroups, your first instinct should be to check for Simpson's Paradox — a situation where a trend that appears in combined data reverses or disappears when you examine the subgroups separately. Here's what the numbers actually show. Variant A: 9+1=109 + 1 = 10 conversions out of 10+10=2010 + 10 = 20 users, giving an overall rate of 50%50\%. Variant B: 80+0=8080 + 0 = 80 conversions out of 100+1=101100 + 1 = 101 users, giving 80101≈79.2%\frac{80}{101} \approx 79.2\%. So D's arithmetic is exactly right. But here's the critical insight: B's aggregate rate looks higher only because B was disproportionately exposed to the paid channel, where both variants convert well. Within each channel, A is actually competitive or superior — A converts at 90%90\% vs. B's 80%80\% in paid, and 10%10\% vs. B's 0%0\% in organic. The channel mix, not the variant itself, drives the aggregate gap. That's confounding, not causation. A is wrong because averaging the channel-level rates with equal weight (90%+10%2=50%\frac{90\%+10\%}{2} = 50\% for A, 80%+0%2=40%\frac{80\%+0\%}{2} = 40\% for B) ignores the actual user counts — you must weight by sample size. B is wrong because it fabricates a rule about using only paid-channel denominators, which has no statistical basis. C gets the math right but draws a causal conclusion from a confounded aggregate, which is the core mistake Simpson's Paradox warns against. When you see aggregate vs. subgroup comparisons on the exam, always ask: are the groups compositionally balanced? If not, aggregate rates can be deeply misleading.

Question 4

A forecasting model is evaluated in two customer segments. Segment X contains 2020 observations and has an RMSE of 22. Segment Y contains 8080 observations and has an RMSE of 44. Both segment RMSE values were calculated from the same type of prediction error.

What is the model's overall RMSE across all 100100 observations?

  1. 3.603.60, found by weighting the two RMSE values directly by segment size before summing
  2. Approximately 3.693.69, found by weighting squared errors by segment size and then taking the square root (correct answer)
  3. 3.003.00, found by treating both segments as equally sized and averaging the two RMSE values
  4. 13.6013.60, found by weighting the mean squared errors by segment size without applying a square root
Explanation: Whenever you combine error metrics across groups of different sizes, you must respect what those metrics actually represent. RMSE is the square root of an average of squared errors, so combining RMSEs requires you to work in the squared-error space, weight by observations, then return to the original scale. Here's the correct approach for answer B: first convert each RMSE back to its Mean Squared Error (MSE). Segment X has MSEX=22=4\text{MSE}_X = 2^2 = 4 and Segment Y has MSEY=42=16\text{MSE}_Y = 4^2 = 16. Next, compute the pooled MSE by weighting each segment's MSE by its share of total observations: MSEpooled=20(4)+80(16)100=80+1280100=1360100=13.6\text{MSE}_{\text{pooled}} = \frac{20(4) + 80(16)}{100} = \frac{80 + 1280}{100} = \frac{1360}{100} = 13.6. Finally, take the square root to return to RMSE units: 13.6≈3.69\sqrt{13.6} \approx 3.69. That's your overall model RMSE. A is wrong because you cannot average RMSE values directly with size weights — 20(2)+80(4)100=3.6\frac{20(2)+80(4)}{100} = 3.6 skips the critical squaring step, producing a number with no valid statistical meaning. C is wrong because it ignores the size imbalance entirely, treating an 80-observation segment the same as a 20-observation one — a simple unweighted average of 2+42=3.0\frac{2+4}{2} = 3.0. D actually computes the correct pooled MSE (13.613.6) but forgets to take the square root, leaving the answer in squared-error units rather than original units. Remember: RMSE → square → weight → average → unsquare. Any shortcut that skips a step in this chain will trap you.

Question 5

A retailer summarizes completed orders by region. North has 2525 orders with an average order value of $140\$140, Central has 3535 orders with an average of $100\$100, and South has 4040 orders with an average of $70\$70.

What is the average order value across all completed orders?

  1. $94.00\$94.00, obtained by giving the largest region the greatest adjustment
  2. $103.33\$103.33, obtained by averaging the three regional averages equally
  3. $98.00\$98.00, obtained by weighting each regional average by its order count (correct answer)
  4. $100.00\$100.00, obtained by using the middle regional average as the overall value
Explanation: Whenever you see a question combining multiple group averages, your instinct should be to ask: are all groups the same size? If not, a simple average of the averages will mislead you — you need a weighted average. Here, the three regions have different order counts (25, 35, and 40), so each regional average must be weighted by how many orders it represents. Multiply each average by its order count, sum the results, then divide by total orders: (25×140)+(35×100)+(40×70)25+35+40=3500+3500+2800100=9800100=$98.00\frac{(25 \times 140) + (35 \times 100) + (40 \times 70)}{25 + 35 + 40} = \frac{3500 + 3500 + 2800}{100} = \frac{9800}{100} = \$98.00 That confirms C is correct. B is the classic trap: averaging $140\$140, $100\$100, and $70\$70 equally gives $103.33\$103.33, but this treats a 25-order region identically to a 40-order region, which distorts the result by overweighting smaller groups. A is a fabricated method — "giving the largest region the greatest adjustment" is not a real statistical technique, and $94.00\$94.00 doesn't follow from any standard calculation here. D simply grabs the middle regional average ($100\$100) and calls it the overall average, which ignores both the other regions and the order counts entirely. Study tip: On any question involving combined averages, check whether group sizes differ. If they do, always weight — the formula is ∑(group average×group size)total size\frac{\sum(\text{group average} \times \text{group size})}{\text{total size}}. Unweighted averaging of averages is one of the most common traps in business analytics questions.

Question 6

A customer-service dataset contains four records for one agent. Their resolution times are 100100 minutes, a missing value, 200200 minutes, and 00 minutes. A SQL summary calculates AVG(resolution_minutes), COUNT(resolution_minutes), and COUNT(*) for the agent.

Which set of grouped results should the summary return under standard SQL null handling?

  1. Average 7575, nonmissing count 44, and row count 44
  2. Average 100100, nonmissing count 33, and row count 44 (correct answer)
  3. Average 100100, nonmissing count 44, and row count 33
  4. Average 150150, nonmissing count 22, and row count 44
Explanation: Whenever you see a SQL aggregation question involving nulls, your first instinct should be to recall how each function treats missing values differently — this is one of the most frequently tested distinctions in business analytics. Under standard SQL, AVG() and COUNT(column_name) both ignore null values, while COUNT(*) counts every row regardless of nulls. With that framework in mind, walk through the data: the three non-null resolution times are 100100, 200200, and 00 minutes. AVG(resolution_minutes) sums those three values — 100+200+0=300100 + 200 + 0 = 300 — then divides by the count of non-null values, giving 300÷3=100300 \div 3 = 100 minutes. COUNT(resolution_minutes) counts only non-null entries, returning 33. COUNT(*) counts all four rows in the group, returning 44. This matches answer B. A is wrong on two counts: it divides 300300 by 44 (incorrectly including the null in the denominator) to get 7575, and reports a nonmissing count of 44 rather than 33. C gets the average right but swaps the two counts — it reports the nonmissing count as 44 and the row count as 33, which is backwards. D ignores the 00-minute record entirely, as if zero were also missing; 00 is a valid value and must be included in calculations. A quick memory anchor: think of COUNT(column) as "count what's actually there" and COUNT(*) as "count the seats at the table, empty or not." On exam questions, always check whether a zero value is being confused with a null — they are fundamentally different in SQL.

Question 7

A company reports daily sales using Pacific business dates, but its transaction timestamps are stored in UTC. A transaction recorded at 02:0002{:}00 UTC on March 11 occurred at 18:0018{:}00 Pacific time on February 2828.

How should an analyst group the transactions to obtain accurate Pacific daily sales summaries?

  1. Convert each timestamp to Pacific time, extract its local date, and then group by that date (correct answer)
  2. Extract the UTC date, group by that date, and then label each group as Pacific time
  3. Group by the UTC timestamp hour, sum each hour, and assign all hours to the UTC date
  4. Subtract one calendar day from every UTC date and then group without using the timestamp
Explanation: When working with time zone conversions in business analytics, your goal is always to ensure each transaction is counted in the time period as experienced by the business, not as recorded by the server clock. The passage gives you a perfect illustration: a UTC timestamp of 02:0002{:}00 on March 1 actually occurred at 18:0018{:}00 Pacific time on February 28 — a full calendar day earlier. If you trust the UTC date at face value, that sale gets counted on the wrong day, distorting your daily summaries. The correct approach, answer A, is to convert every timestamp to Pacific time first, then extract the local date, and finally group by that date. This guarantees each transaction lands in the Pacific business day where it truly belongs. B is a common trap: grouping by UTC date and simply relabeling the groups as "Pacific" doesn't move the data — it just renames incorrectly assigned buckets. The underlying mismatch remains. C compounds the error further by aggregating at the UTC hour level and assigning everything to a UTC date, which ignores time zone offsets entirely and produces summaries that are meaningless for Pacific business reporting. D sounds like a shortcut but fails because subtracting one day from every UTC date is wrong — not all UTC timestamps fall on a different Pacific calendar day. A transaction at 15:0015{:}00 UTC, for example, is 07:0007{:}00 Pacific time on the same date, so blindly subtracting a day would shift it incorrectly. As a study habit, whenever you see time zone questions, ask yourself: "In whose local time does this event actually fall?" Convert first, then aggregate — never the reverse.

Question 8

An A/B test event dataset contains one row per website event. Each user is assigned to exactly one variant, may generate many events, and may generate multiple purchase events. The desired grouped metric is the percentage of assigned users in each variant who made at least one purchase.

Which aggregation most directly calculates the desired conversion rate for each variant?

  1. SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) / COUNT(*)
  2. COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) / COUNT(DISTINCT user_id) (correct answer)
  3. COUNT(DISTINCT user_id) / COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END)
  4. COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) / COUNT(*)
Explanation: When working with event-level data, the biggest trap is confusing event counts with user counts. Since one user can generate many rows, raw row counts don't represent people — and this question is explicitly asking about the percentage of users who converted, not the percentage of events that were purchases. The correct approach, answer B, directly maps to the definition: take the number of distinct users who made at least one purchase, divide by the total number of distinct users in the variant. COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) returns each purchasing user exactly once regardless of how many purchase events they generated, and COUNT(DISTINCT user_id) gives the true denominator — all assigned users. That ratio is precisely the conversion rate you want. Answer A divides purchase events by total rows, so a user with five purchases inflates the numerator and a user with many non-purchase events inflates the denominator — the result has no clean interpretation as a user-level conversion rate. Answer C flips the fraction entirely, putting purchasing users in the denominator and all users in the numerator, which would give a number greater than 1 whenever fewer than all users converted — mathematically inverted. Answer D correctly identifies purchasing users in the numerator but divides by COUNT(*), the total number of rows, not total users — mixing user-level counting with row-level counting produces a deflated, uninterpretable rate. A reliable rule of thumb: whenever your desired metric is "percentage of users who did X," both your numerator and denominator should use COUNT(DISTINCT user_id) — one filtered, one not.

Question 9

A warehouse records one ending-inventory snapshot on each of three operating days in a month. The snapshots are 100100, 120120, and 8080 units. Management requests average daily ending inventory, not units sold or inventory-days.

Which monthly group-by summary correctly measures the requested KPI?

  1. Use SUM(ending_inventory) to report 300300 units as the monthly inventory level
  2. Use MAX(ending_inventory) to report 120120 units as the representative daily level
  3. Use AVG(ending_inventory) to report 100100 units as average daily ending inventory (correct answer)
  4. Use MIN(ending_inventory) to report 8080 units as the typical monthly inventory level
Explanation: When a question asks for an average daily value, your job is to identify which aggregation function mathematically produces a mean — not a total, not an extreme. This question tests whether you can match the right SQL aggregate to the right business KPI. Since management wants average daily ending inventory, you need to sum the observed values and divide by the number of days: 100+120+803=100\frac{100 + 120 + 80}{3} = 100 units. That's exactly what AVG(ending_inventory) computes, making C the correct answer. It directly answers the question as stated. The distractors each represent a common analytic mistake. A uses SUM, which produces 300300 units — a cumulative total across all snapshots. This would be meaningful if you were measuring total inventory-days or warehouse throughput, but summing ending snapshots doesn't yield a representative daily level; it overstates the figure by a factor of three. B uses MAX, returning 120120 units — the single peak day. This might be useful for capacity planning, but it ignores the lower-inventory days entirely and distorts the typical picture. D uses MIN, returning 8080 units — the lowest observation. Like MAX, it discards most of the data and represents a worst-case floor, not a typical level. A useful rule of thumb: match the aggregation function to the business question word-for-word. "Average" → AVG. "Total" → SUM. "Peak" → MAX. "Floor" → MIN. On business-analytics exams, wrong answers often use plausible-sounding functions that measure something related but fundamentally different from what was requested.

Question 10

A customer may purchase in more than one region. A grouped summary reports 7070 distinct purchasing customers in the East and 5050 in the West. Of these customers, 2020 purchased in both regions.

Which statement correctly describes the companywide distinct-customer count?

  1. It is 120120 because distinct counts remain additive across regional groups
  2. It is 140140 because cross-region customers contribute once to each regional total
  3. It is 100100 because the 2020 overlapping customers must be subtracted once (correct answer)
  4. It is 8080 because the overlap must be subtracted from both regional totals
Explanation: Whenever you see a question involving counts across overlapping groups, reach for the Inclusion-Exclusion Principle: ∣A∪B∣=∣A∣+∣B∣−∣A∩B∣|A \cup B| = |A| + |B| - |A \cap B|. This formula prevents you from double-counting members who belong to both groups simultaneously. Here, the East group has 7070 distinct customers and the West has 5050, but 2020 customers appear in both totals. Simply adding gives 70+50=12070 + 50 = 120, but that counts those 2020 shared customers twice — once in each regional bucket. Subtracting the overlap once corrects this: 70+50−20=10070 + 50 - 20 = 100. So the true companywide distinct-customer count is 100, making C correct. Choice A reaches 120120 by treating the two regional counts as if they contain entirely separate people, which would only be valid if no customer ever purchased in both regions. Choice B arrives at 140140 with no clear mathematical basis — it seems to add the overlap rather than account for it, which has no grounding in set logic. Choice D subtracts the overlap twice (once from each regional total), producing 70−20+50−20=8070 - 20 + 50 - 20 = 80, but that over-corrects: the overlap only needs to be removed once from the combined sum, not once per region. A useful pattern to remember: any time a business-analytics question mentions customers (or orders, transactions, etc.) who qualify under multiple segments, flag it as an Inclusion-Exclusion scenario. The trap is almost always simple addition — your instinct to subtract the overlap once is what separates the right answer from the most tempting distractor.