Coding Interview Preparation on Telegram: “📊 SQL SATURDAY #3

📊 SQL SATURDAY #3 - GROUP BY and Aggregates


orders
+----+-------------+--------+------------+
| id | customer_id | amount | order_date |
+----+-------------+--------+------------+
| 1  | 1           | 250    | 2024-01-05 |
| 2  | 1           | 100    | 2024-02-14 |
| 3  | 2           | 75     | 2024-01-20 |
| 4  | 3           | 500    | 2024-03-01 |
| 5  | 2           | 200    | 2024-03-15 |

Question: Find the total spend per customer, only for customers who spent more than $150 total.

sql
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 150;

⚠️ The classic trap: using WHERE instead of HAVING here.

sql
-- WRONG:
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE SUM(amount) > 150   -- ❌ ERROR
GROUP BY customer_id;

WHERE filters rows before grouping happens - it has no idea what SUM(amount) even means yet, since that's calculated during grouping. HAVING filters after the aggregation, which is exactly what you need for conditions on aggregate functions.

Simple rule to remember: WHERE filters rows, HAVING filters groups.

What SQL clause order still confuses you sometimes? (No shame - even senior engineers mix this up under pressure) 👇