Business Analytics Quiz: Group By And Having
9 questions · exam conditions
0:00
Group By And HavingQuestion 1 of 9

An A/B testing dataset stores daily impressions and clicks for each campaign. Daily impression counts vary substantially. A campaign should be retained only if it accumulated at least 10,00010{,}000 impressions and its overall click-through rate exceeded 2%2\%.

Which query correctly applies both campaign-level requirements?

SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 AND 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) > 0.02;
SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 AND AVG(1.0 * clicks / NULLIF(impressions, 0)) > 0.02;
SELECT campaign_id FROM performance WHERE SUM(impressions) >= 10000 GROUP BY campaign_id HAVING SUM(clicks) / SUM(impressions) > 0.02;
SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 OR 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) > 0.02;
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Group By And Having

Practice Group By And Having 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 And Having, 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 A/B testing dataset stores daily impressions and clicks for each campaign. Daily impression counts vary substantially. A campaign should be retained only if it accumulated at least 10,00010{,}000 impressions and its overall click-through rate exceeded 2%2\%.

Which query correctly applies both campaign-level requirements?

  1. SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 AND 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) > 0.02; (correct answer)
  2. SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 AND AVG(1.0 * clicks / NULLIF(impressions, 0)) > 0.02;
  3. SELECT campaign_id FROM performance WHERE SUM(impressions) >= 10000 GROUP BY campaign_id HAVING SUM(clicks) / SUM(impressions) > 0.02;
  4. SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 OR 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) > 0.02;
Explanation: When filtering aggregated data in SQL, your first instinct should be: does this condition apply to individual rows or to the group as a whole? Aggregate conditions — like totals and rates — belong in HAVING, not WHERE. Additionally, when computing a ratio from summed components, you must aggregate first, then divide. Option A is correct because it aggregates at the campaign level, applies both filters in HAVING, and computes the overall CTR as ∑clicks∑impressions\frac{\sum \text{clicks}}{\sum \text{impressions}} — which correctly pools all daily data before dividing. The NULLIF(..., 0) guard also prevents division-by-zero errors, a best practice when denominators might be zero. Option B contains a subtle but critical flaw: AVG(1.0 * clicks / impressions) computes each day's CTR and then averages those daily rates. Because impression counts vary day to day, this is an unweighted average and does not equal the true overall CTR. For example, a day with 9,000 impressions and 180 clicks gets equal weight to a day with 100 impressions and 3 clicks, distorting the result. Option C places SUM(impressions) >= 10000 inside WHERE, which is illegal in SQL — aggregate functions cannot appear in WHERE clauses. This query would throw a syntax/runtime error. Option D uses OR instead of AND, meaning a campaign passes if it meets either condition alone. The business rule requires both to be satisfied simultaneously, so OR is logically incorrect. Study tip: Whenever you see a CTR or ratio computed from variable-volume data, always sum the components separately before dividing — never average the per-row rates, as that ignores volume weighting.

Question 2

An orders table has one row per order, including customer_id and shipping_fee. An order-items table may have several Hardware items for the same order. An analyst wants, for each customer, the number of orders containing at least one Hardware item and the total shipping fees for those orders, counting each qualifying order once.

Which query produces the requested customer-level aggregates without inflation from multiple Hardware items?

  1. SELECT o.customer_id, COUNT(*) AS n, SUM(o.shipping_fee) AS fees FROM orders o JOIN order_items i ON o.order_id = i.order_id WHERE i.category = 'Hardware' GROUP BY o.customer_id;
  2. SELECT o.customer_id, COUNT(DISTINCT o.order_id) AS n, SUM(o.shipping_fee) AS fees FROM orders o JOIN order_items i ON o.order_id = i.order_id WHERE i.category = 'Hardware' GROUP BY o.customer_id;
  3. SELECT o.customer_id, COUNT(DISTINCT o.order_id) AS n, SUM(DISTINCT o.shipping_fee) AS fees FROM orders o JOIN order_items i ON o.order_id = i.order_id WHERE i.category = 'Hardware' GROUP BY o.customer_id;
  4. SELECT o.customer_id, COUNT(*) AS n, SUM(o.shipping_fee) AS fees FROM orders o WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.order_id AND i.category = 'Hardware') GROUP BY o.customer_id; (correct answer)
Explanation: Whenever a JOIN can produce duplicate rows, you must decide whether to de-duplicate before aggregating or inside the aggregate functions — and that choice determines whether your counts and sums are accurate. Here, an order containing three Hardware items produces three joined rows. Any aggregation that runs directly on those rows will count and sum the order three times instead of once. The cleanest fix is to prevent duplicates from ever entering the aggregation by using a semi-join — specifically, EXISTS. Option D filters the orders table to only rows where at least one Hardware item exists, then aggregates the already-clean orders rows. Because no join multiplies the rows, COUNT(*) and SUM(shipping_fee) each touch every qualifying order exactly once. This is the correct answer. Option A joins first and aggregates second with no de-duplication safeguard. An order with three Hardware items contributes three rows, so both the count and the sum are inflated by a factor equal to the number of Hardware line items per order. Option B fixes the count with COUNT(DISTINCT order_id) — smart — but SUM(shipping_fee) still operates on the duplicated rows, so the shipping total remains over-counted. Getting one aggregate right while leaving another wrong is a classic half-fix trap. Option C tries to rescue the sum using SUM(DISTINCT shipping_fee), but DISTINCT de-duplicates by value, not by order. If two different orders happen to share the same shipping fee, one of them gets silently dropped from the total — potentially under-counting rather than over-counting. Study tip: When you see a one-to-many join feeding an aggregation, ask yourself: "Are my rows already at the grain I want to count?" If not, prefer filtering with EXISTS/IN over patching aggregates with DISTINCT.

Question 3

A lead-source dataset has two Online rows with non-null revenues of 5050 and 7070, one Store row with revenue of 8080, and three rows whose channel is NULL. The three NULL-channel rows have revenues of 2020, 3030, and NULL, respectively.

What is returned by SELECT channel, COUNT(revenue) AS n, SUM(revenue) AS total FROM leads GROUP BY channel HAVING COUNT(channel) = 0 AND COUNT(revenue) >= 2;?

  1. The Online group with n equal to 22 and total equal to 120120
  2. Three separate NULL-channel rows, each retaining its original revenue value
  3. One NULL-channel row with n equal to 22 and total equal to 5050 (correct answer)
  4. No rows, because NULL values cannot form a SQL group
Explanation: When working with GROUP BY and HAVING in SQL, you need to think carefully about how NULLs are handled at every stage: grouping, aggregation, and filtering. Here's what happens step by step. SQL groups NULL channel values together into a single group — contrary to how NULLs behave in comparisons elsewhere, GROUP BY treats all NULLs as one bucket. That NULL group contains three rows with revenues of 2020, 3030, and NULL. Now apply the HAVING clause: COUNT(channel) = 0 is satisfied because COUNT ignores NULLs, so counting a NULL channel field across all three rows yields 00. Next, COUNT(revenue) >= 2 — since COUNT also ignores NULLs, only the two non-null revenues (2020 and 3030) are counted, giving 22, which satisfies >= 2. Both conditions pass, so this group is returned with n = 2 and total = 50. Answer C is correct. Answer A is wrong because the Online group has COUNT(channel) = 2, not 00, so it fails the first HAVING condition and is excluded entirely. Answer B confuses GROUP BY behavior with a row-level SELECT — grouped queries return one row per group, never individual original rows. Answer D reflects a common misconception: SQL absolutely can group NULL values together using GROUP BY; NULLs only cause issues in WHERE comparisons and JOIN conditions, not in grouping itself. Your study tip: always mentally separate the three stages — GROUP BY (NULLs cluster together), aggregate functions (NULLs are ignored), and HAVING (filters on those aggregates). Mixing up any stage is the most common trap on SQL aggregation questions.

Question 4

A campaign-sales table records campaign_id, product_category, and customer_id. A campaign qualifies if it sold all three required categories—Software, Services, and Training—and reached at least 1010 distinct customers. Rows may include other categories and repeat purchases.

Which query correctly identifies qualifying campaigns?

  1. SELECT campaign_id FROM campaign_sales GROUP BY campaign_id HAVING COUNT(DISTINCT product_category) = 3 AND COUNT(DISTINCT customer_id) >= 10; (no category filter applied before grouping)
  2. SELECT campaign_id FROM campaign_sales WHERE product_category IN ('Software', 'Services', 'Training') GROUP BY campaign_id HAVING COUNT(DISTINCT product_category) = 3 AND COUNT(DISTINCT customer_id) >= 10; (correct answer)
  3. SELECT campaign_id FROM campaign_sales WHERE product_category IN ('Software', 'Services', 'Training') GROUP BY campaign_id HAVING COUNT(product_category) = 3 AND COUNT(customer_id) >= 10; (non-distinct counts used)
  4. SELECT campaign_id FROM campaign_sales WHERE product_category = 'Software' AND product_category = 'Services' AND product_category = 'Training' GROUP BY campaign_id HAVING COUNT(DISTINCT customer_id) >= 10; (all three equality conditions on one row)
Explanation: When filtering and aggregating in SQL, the order of operations matters enormously. Here, you need to confirm that a campaign covered exactly the three required categories—Software, Services, and Training—and reached at least 1010 distinct customers. The key insight is that you must first isolate the relevant categories before counting them, otherwise your counts can be contaminated by unrelated data. Option B is correct because the WHERE clause pre-filters rows to only the three target categories. Then, HAVING COUNT(DISTINCT product_category) = 3 confirms all three are present, and HAVING COUNT(DISTINCT customer_id) >= 10 confirms the reach threshold. Both conditions are necessary and work together cleanly. Option A skips the WHERE filter entirely, meaning other categories in the table count toward COUNT(DISTINCT product_category). A campaign selling four categories—including the three required ones—would show a count of 44, not 33, and fail the = 3 check even though it qualifies. This is the subtlest trap. Option C uses the same correct WHERE filter but drops DISTINCT from both COUNT calls. COUNT(product_category) counts every row, not unique categories, so a campaign with 55 rows of the same category could pass. Without DISTINCT, you're measuring volume, not variety or uniqueness. Option D applies contradictory equality conditions: product_category = 'Software' AND product_category = 'Services' AND product_category = 'Training' can never be true simultaneously for a single row, so this query returns no results. Study tip: When a question involves "all of these specific values" across rows, always pair a WHERE IN (...) filter with COUNT(DISTINCT ...) in HAVING—filtering narrows the pool, and DISTINCT ensures you're counting unique membership, not occurrences.

Question 5

A transaction table records store_id, amount, and a Boolean returned field. Management wants stores for which returned merchandise represents more than 10%10\% of total transaction revenue. Stores may have transactions of different sizes.

Which HAVING clause correctly implements management's revenue-based rule?

  1. HAVING AVG(CASE WHEN returned THEN 1.0 ELSE 0.0 END) > 0.10
  2. HAVING SUM(CASE WHEN returned THEN amount ELSE 0 END) > 0.10
  3. HAVING SUM(CASE WHEN returned THEN amount ELSE 0 END) / NULLIF(SUM(amount), 0) > 0.10 (correct answer)
  4. HAVING SUM(CASE WHEN returned THEN amount ELSE 0 END) / NULLIF(COUNT(*), 0) > 0.10
Explanation: Whenever you see a question about filtering aggregated groups by a ratio, your first instinct should be to build that ratio explicitly: numerator divided by denominator. Here, management wants stores where returned revenuetotal revenue>0.10\frac{\text{returned revenue}}{\text{total revenue}} > 0.10, so you need both pieces constructed correctly in SQL. Option C nails this. SUM(CASE WHEN returned THEN amount ELSE 0 END) captures total revenue from returned transactions (the numerator), and SUM(amount) captures all transaction revenue (the denominator). Wrapping the denominator in NULLIF(..., 0) prevents a division-by-zero error if a store somehow has zero total revenue — a best practice worth memorizing. The result is a true revenue-weighted return rate compared against 0.10. Option A computes the proportion of transactions that were returned, not the proportion of revenue. Because transactions vary in size, a store could have many small returns (high transaction count ratio) but low returned revenue, or vice versa — making A's metric flatly wrong for management's rule. Option B calculates only the raw dollar amount of returned merchandise with no division, so it compares an absolute dollar figure against 0.10. This would flag virtually no stores (almost any return exceeds $0.10) and ignores the ratio entirely. Option D divides returned revenue by the count of transactions rather than total revenue. This produces an average return amount per transaction — a completely different metric that doesn't represent a percentage of revenue. Study tip: When a business rule says "more than X% of [something]," always translate it into a ratio of two SUM expressions, and always guard the denominator with NULLIF to handle edge cases safely.

Question 6

A sales dataset contains these revenue rows: North–Retail has 60,00060{,}000 and 50,00050{,}000; North–Enterprise has 130,000130{,}000; South–Retail has 80,00080{,}000; and South–Enterprise has 55,00055{,}000 and 60,00060{,}000.

An analyst groups by both region and segment and retains groups with total revenue greater than 100,000100{,}000. Which groups remain?

  1. North–Retail, North–Enterprise, and South–Enterprise (correct answer)
  2. North–Enterprise and South–Enterprise only
  3. North–Retail, North–Enterprise, and South–Retail
  4. North and South as two region-level groups
Explanation: When you see a question involving grouping and filtering in data analysis, think about the SQL-style logic of GROUP BY followed by HAVING — you first aggregate each group's total, then apply the filter condition to those totals. Here's how to work through this problem. The dataset has six rows, so start by summing revenue within each region–segment combination. North–Retail has two rows: 60,000+50,000=110,00060{,}000 + 50{,}000 = 110{,}000. North–Enterprise has one row: 130,000130{,}000. South–Retail has one row: 80,00080{,}000. South–Enterprise has two rows: 55,000+60,000=115,00055{,}000 + 60{,}000 = 115{,}000. Now apply the filter — keep only groups where total revenue exceeds 100,000100{,}000. North–Retail (110,000110{,}000 ✓), North–Enterprise (130,000130{,}000 ✓), South–Retail (80,00080{,}000 ✗), and South–Enterprise (115,000115{,}000 ✓). That gives you three surviving groups, confirming answer A. Answer B is wrong because it drops North–Retail, likely because students forget to sum its two rows and mistakenly evaluate only one of them (60,00060{,}000 or 50,00050{,}000), neither of which clears 100,000100{,}000 alone. Answer C incorrectly includes South–Retail (80,00080{,}000), which falls below the threshold, while excluding South–Enterprise — the opposite error. Answer D reflects a complete misreading of the grouping level; the analyst grouped by both region and segment, so individual region-level totals are irrelevant here. The key study tip: always aggregate all rows belonging to a group before testing the filter condition. A common trap is evaluating individual rows instead of group totals — especially when a group has multiple rows that only qualify together.

Question 7

A retailer records one row per order. Completed orders are as follows: East customer C1 has orders of 300300 and 250250; East customer C2 has one order of 700700; East customer C3 has orders of 200200, 200200, and 150150; West customer C4 has orders of 400400 and 150150; and West customer C5 has one completed order of 100100 plus one pending order of 600600.

The analyst runs: SELECT region, customer_id, SUM(amount) AS total_amount FROM orders WHERE status = 'Completed' GROUP BY region, customer_id HAVING SUM(amount) >= 500 AND COUNT(*) >= 2; Which customer groups are returned?

  1. East C1, East C2, East C3, and West C4
  2. East C1, East C3, and West C4 only (correct answer)
  3. East C1, East C3, West C4, and West C5
  4. East C2 and West C5 only
Explanation: When you see a SQL query combining WHERE, GROUP BY, and HAVING, your job is to apply each clause in sequence: filter rows first, then group, then apply the HAVING conditions to each group. Start by filtering for status = 'Completed', which eliminates C5's 600600 pending order — C5's only completed order is 100100. Now evaluate each remaining customer against both HAVING conditions: total amount ≥500\geq 500 and order count ≥2\geq 2.
  • C1 (East): 300+250=550≥500300 + 250 = 550 \geq 500 ✓, 2 orders ✓ → included
  • C2 (East): 700≥500700 \geq 500 ✓, but only 1 order ✗ → excluded
  • C3 (East): 200+200+150=550≥500200 + 200 + 150 = 550 \geq 500 ✓, 3 orders ✓ → included
  • C4 (West): 400+150=550≥500400 + 150 = 550 \geq 500 ✓, 2 orders ✓ → included
  • C5 (West): only 100100 completed, fails both conditions → excluded
This confirms answer B — East C1, East C3, and West C4 only. Answer A incorrectly includes C2, forgetting that COUNT(*) >= 2 eliminates single-order customers regardless of their total. Answer C incorrectly includes C5, ignoring that the WHERE clause strips the 600600 pending order before any aggregation occurs. Answer D selects only C2 and C5, which actually fail one or both HAVING conditions. A useful habit: treat WHERE as a pre-aggregation gatekeeper and HAVING as a post-aggregation filter — both conditions must pass independently. When a HAVING clause uses AND, one strong metric cannot compensate for a failing one.

Question 8

A customer table contains Gold customers G1 and G2, Silver customers S1 and S2, and Bronze customer B1. In the orders table, G1 has two orders, G2 has none, S1 and S2 each have one, and B1 has none. The tables are left joined from customers to orders.

What does the following query return? SELECT c.tier FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.tier HAVING COUNT(*) > COUNT(o.order_id);

  1. Gold and Bronze, because each contains a customer without an order (correct answer)
  2. Gold and Silver, because each tier is represented by multiple joined rows
  3. Bronze only, because it is the only tier with no orders at all
  4. Gold, Silver, and Bronze, because the join preserves every customer
Explanation: When you see HAVING COUNT(*) > COUNT(o.order_id) after a LEFT JOIN, your instinct should be to think about NULLs. In a left join, customers without matching orders still appear, but their order columns are NULL. COUNT(*) counts every row including those with NULLs, while COUNT(o.order_id) only counts non-NULL values — so the difference between them reveals how many customers in each tier have no orders. Let's trace through the data. After the left join, Gold has two rows (G1×2 orders, G2×NULL), Silver has two rows (S1×1, S2×1), and Bronze has one row (B1×NULL). Grouping by tier gives: Gold has COUNT(*) = 3 (two order rows + one NULL row) and COUNT(o.order_id) = 2 — the condition 3 > 2 is true. Bronze has COUNT(*) = 1 and COUNT(o.order_id) = 0 — the condition 1 > 0 is true. Silver has COUNT(*) = 2 and COUNT(o.order_id) = 2 — the condition 2 > 2 is false. So the correct answer is A, Gold and Bronze. Answer B is wrong because Silver's joined rows all have real order IDs, so both counts are equal and the HAVING filter eliminates it. Answer C is wrong because Bronze isn't the only qualifying tier — Gold also passes the filter due to G2 having no orders. Answer D is wrong because the HAVING clause actively filters out Silver. As a study tip: whenever you see COUNT(*) vs COUNT(column) in a HAVING clause after a LEFT JOIN, immediately ask yourself which groups contain NULL rows — those are the ones that will pass the filter.

Question 9

A human-resources analyst wants the names of departments whose average salary exceeds the average salary across the entire company. The output should contain only department names. Assume standard SQL grouping rules.

Which query correctly returns the requested departments?

  1. SELECT department FROM employees HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
  2. SELECT department FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) GROUP BY department;
  3. SELECT department FROM employees GROUP BY department HAVING salary > (SELECT AVG(salary) FROM employees);
  4. SELECT department FROM employees GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM employees); (correct answer)
Explanation: Whenever SQL asks you to filter groups based on an aggregate condition, you need two things working together: GROUP BY to define the groups, and HAVING to filter them. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Keeping that distinction clear unlocks this entire question. Option D is correct because it follows this logic precisely. It groups all employees by department, then uses HAVING AVG(salary) > (SELECT AVG(salary) FROM employees) to keep only departments whose group-level average exceeds the company-wide average. The subquery computes one scalar value — the overall mean salary — and HAVING compares each department's aggregate against it. Clean, complete, and logically sound. Option A skips GROUP BY entirely. Without grouping, the database treats the entire table as one group, so there's nothing meaningful to filter — you'd either get all rows or none, not a per-department breakdown. Option B uses WHERE salary > ..., which filters out individual employees whose salary is below average before grouping. This means some departments lose rows unfairly, so the subsequent GROUP BY produces distorted averages — you're not answering "which department averages high?" but rather "which department has many high earners?" Option C uses HAVING salary > ... without an aggregate function. Since salary is a row-level column, not a group-level expression, this violates SQL grouping rules — most engines will throw an error or return unpredictable results. Your go-to rule: WHERE touches rows, HAVING touches groups. Any time a filter involves AVG(), SUM(), or another aggregate, it belongs in HAVING, paired with a GROUP BY.