Group-By Summaries - Business Analytics

1. What is the average order value across all completed orders?
   Answer: $\$98.00$, obtained by weighting each regional average by its order count 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: $\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$, $\$100$, and $\$70$ equally gives $\$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$ doesn't follow from any standard calculation here. D simply grabs the middle regional average ($\$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 $\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.
2. Which conclusion is best supported when the channel-level groups are also aggregated overall?
   Answer: A converts at $50\%$ and B at about $79.2\%$, but channel composition confounds the aggregate comparison 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 = 10$ conversions out of $10 + 10 = 20$ users, giving an overall rate of $50\%$. Variant B: $80 + 0 = 80$ conversions out of $100 + 1 = 101$ users, giving $\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\%$ vs. B's $80\%$ in paid, and $10\%$ vs. B's $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 ($\frac{90\%+10\%}{2} = 50\%$ for A, $\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.
3. Which set of grouped results should the summary return under standard SQL null handling?
   Answer: Average $100$, nonmissing count $3$, and row count $4$ 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 $100$, $200$, and $0$ minutes. AVG(resolution_minutes) sums those three values — $100 + 200 + 0 = 300$ — then divides by the count of non-null values, giving $300 \div 3 = 100$ minutes. COUNT(resolution_minutes) counts only non-null entries, returning $3$. COUNT(*) counts all four rows in the group, returning $4$. This matches answer B. A is wrong on two counts: it divides $300$ by $4$ (incorrectly including the null in the denominator) to get $75$, and reports a nonmissing count of $4$ rather than $3$. C gets the average right but swaps the two counts — it reports the nonmissing count as $4$ and the row count as $3$, which is backwards. D ignores the $0$-minute record entirely, as if zero were also missing; $0$ 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.
4. Which monthly group-by summary correctly measures the requested KPI?
   Answer: Use AVG(ending_inventory) to report $100$ units as average daily ending inventory 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: $\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 $300$ 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 $120$ 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 $80$ 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.
5. Which SQL-like sequence correctly produces the requested group-by summary?
   Answer: WHERE status = 'Completed' GROUP BY region HAVING SUM(profit) > 50000 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$. 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$ — 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.
6. What regional order total will the joined-data summary report?
   Answer: $\$280$ because the first order total appears on two joined rows 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$ order total. Order 102 has one item row, so it appears once with its $\$80$ total. When you call SUM(order_total), you're summing all rows: $\$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$ 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$, 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.
7. How should an analyst group the transactions to obtain accurate Pacific daily sales summaries?
   Answer: Convert each timestamp to Pacific time, extract its local date, and then group by that date 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{:}00$ on March 1 actually occurred at $18{:}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{:}00$ UTC, for example, is $07{:}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.
8. Which statement correctly describes the companywide distinct-customer count?
   Answer: It is $100$ because the $20$ overlapping customers must be subtracted once Whenever you see a question involving counts across overlapping groups, reach for the Inclusion-Exclusion Principle: $|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 $70$ distinct customers and the West has $50$, but $20$ customers appear in both totals. Simply adding gives $70 + 50 = 120$, but that counts those $20$ shared customers twice — once in each regional bucket. Subtracting the overlap once corrects this: $70 + 50 - 20 = 100$. So the true companywide distinct-customer count is 100, making C correct. Choice A reaches $120$ 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 $140$ 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 = 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.
9. What is the model's overall RMSE across all $100$ observations?
   Answer: Approximately $3.69$, found by weighting squared errors by segment size and then taking the square root 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 $\text{MSE}_X = 2^2 = 4$ and Segment Y has $\text{MSE}_Y = 4^2 = 16$. Next, compute the pooled MSE by weighting each segment's MSE by its share of total observations: $\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: $\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 — $\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 $\frac{2+4}{2} = 3.0$. D actually computes the correct pooled MSE ($13.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.
10. Which aggregation most directly calculates the desired conversion rate for each variant?
    Answer: COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) / COUNT(DISTINCT user_id) 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.

Start this quiz