You have a transactions table with columns customer_id, month, and amount. You want to find the average transaction amount per customer per month, but only for months where the customer made more than 3 transactions. Which query correctly achieves this?
ASELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id, month HAVING COUNT(*) > 3;
BSELECT customer_id, month, AVG(amount) FROM transactions WHERE COUNT(*) > 3 GROUP BY customer_id, month;
CSELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id HAVING COUNT(*) > 3;
DSELECT customer_id, month, AVG(amount) FROM transactions GROUP BY month HAVING COUNT(*) > 3;
Step-by-Step Solution
Solution:
Step 1: Understand filtering groups with HAVING
HAVING filters groups after grouping. To filter groups with more than 3 transactions, use HAVING COUNT(*) > 3.
Step 2: Group by both customer_id and month
To get average per customer per month, group by both columns.
Step 3: Check each option
SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id, month HAVING COUNT(*) > 3; correctly uses GROUP BY customer_id, month and HAVING COUNT(*) > 3. SELECT customer_id, month, AVG(amount) FROM transactions WHERE COUNT(*) > 3 GROUP BY customer_id, month; misuses WHERE with COUNT(). SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id HAVING COUNT(*) > 3; groups only by customer_id, missing month. SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY month HAVING COUNT(*) > 3; groups only by month, missing customer_id.
Final Answer:
SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id, month HAVING COUNT(*) > 3; -> Option A
Quick Check:
Use HAVING to filter grouped counts [OK]
Quick Trick:Use HAVING to filter groups after GROUP BY [OK]
Common Mistakes:
MISTAKES
Using WHERE with aggregate functions
Grouping by only one column when two needed
Filtering before grouping instead of after
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
