GROUP BY & HAVING - Business Analytics

1. Which query correctly identifies qualifying campaigns?
   Answer: 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; 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 $10$ 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 $4$, not $3$, 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 $5$ 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.
2. An analyst groups by both region and segment and retains groups with total revenue greater than $100{,}000$. Which groups remain?
   Answer: North–Retail, North–Enterprise, and South–Enterprise 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{,}000$. North–Enterprise has one row: $130{,}000$. South–Retail has one row: $80{,}000$. South–Enterprise has two rows: $55{,}000 + 60{,}000 = 115{,}000$. Now apply the filter — keep only groups where total revenue exceeds $100{,}000$. North–Retail ($110{,}000$ ✓), North–Enterprise ($130{,}000$ ✓), South–Retail ($80{,}000$ ✗), and South–Enterprise ($115{,}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{,}000$ or $50{,}000$), neither of which clears $100{,}000$ alone. Answer C incorrectly includes South–Retail ($80{,}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.
3. 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?
   Answer: East C1, East C3, and West C4 only 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 $600$ pending order — C5's only completed order is $100$. Now evaluate each remaining customer against both HAVING conditions: total amount $\geq 500$ and order count $\geq 2$. - C1 (East): $300 + 250 = 550 \geq 500$ ✓, 2 orders ✓ → included - C2 (East): $700 \geq 500$ ✓, but only 1 order ✗ → excluded - C3 (East): $200 + 200 + 150 = 550 \geq 500$ ✓, 3 orders ✓ → included - C4 (West): $400 + 150 = 550 \geq 500$ ✓, 2 orders ✓ → included - C5 (West): only $100$ 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 $600$ 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.
4. 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;?
   Answer: One NULL-channel row with n equal to $2$ and total equal to $50$ 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 $20$, $30$, 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 $0$. Next, COUNT(revenue) >= 2 — since COUNT also ignores NULLs, only the two non-null revenues ($20$ and $30$) are counted, giving $2$, 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 $0$, 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.
5. 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);
   Answer: Gold and Bronze, because each contains a customer without an order 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.
6. Which HAVING clause correctly implements management's revenue-based rule?
   Answer: HAVING SUM(CASE WHEN returned THEN amount ELSE 0 END) / NULLIF(SUM(amount), 0) > 0.10 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 $\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.
7. Which query produces the requested customer-level aggregates without inflation from multiple Hardware items?
   Answer: 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; 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.
8. Which query correctly applies both campaign-level requirements?
   Answer: SELECT campaign_id FROM performance GROUP BY campaign_id HAVING SUM(impressions) >= 10000 AND 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) > 0.02; 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 $\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.
9. Which query correctly returns the requested departments?
   Answer: SELECT department FROM employees GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM employees); 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.

Start this quiz