Mastering SQL Interviews: Top 5 Commonly Asked Problems and How to Solve Them

Chapter 5: Aggregating Data with GROUP BY and HAVING

🧠 Introduction

SQL’s power lies in its ability to aggregate and summarize data. The GROUP BY clause is the go-to tool for performing aggregate operations like totals, averages, counts, and more — especially when grouped by specific attributes such as department, date, region, or customer.

Interviewers often ask:

“Find departments with more than 2 employees.”
“List customers who made more than $10,000 in purchases.”
“Find products with an average rating above 4.”

All these rely on GROUP BY and its companion filter: HAVING.

In this chapter, you’ll learn:

• How GROUP BY works
• When and why to use HAVING vs WHERE
• How to use multiple aggregate functions
• Real-world use cases and common interview challenges

🗃️ Sample Table: Orders

🧾 orders

🔍 Problem 1: Total Amount Spent by Each Customer

SELECT customer_id, SUM(amount) AS total_spent

FROM orders

GROUP BY customer_id;

✅ Output:

We’re summarizing (SUM) by grouping on customer_id.

❓ Problem 2: Customers Who Spent More Than 10,000

SELECT customer_id, SUM(amount) AS total_spent

FROM orders

GROUP BY customer_id

HAVING SUM(amount) > 10000;

✅ Output:

Use HAVING to filter grouped results.
🔥 HAVING works after aggregation; WHERE works before.

💡 WHERE vs HAVING

Example:

-- Only orders > 1000

SELECT customer_id, SUM(amount)

FROM orders

WHERE amount > 1000

GROUP BY customer_id;

🔄 Multiple Aggregates Example

Show total quantity and amount for each product.

SELECT product,

       SUM(quantity) AS total_qty,

       SUM(amount) AS total_sales

FROM orders

GROUP BY product;

⚙️ Using GROUP BY with Multiple Columns

Show total sales per customer per product.

SELECT customer_id, product, SUM(amount) AS total_sales

FROM orders

GROUP BY customer_id, product;

🎯 Real-World Use Cases

• Revenue by customer, region, or time period
• Counting number of logins per user
• Average session duration per device
• Product popularity by category

🧑‍💻 Interview Challenge

Find all products that have been ordered more than twice.

SELECT product, COUNT(*) AS order_count

FROM orders

GROUP BY product

HAVING COUNT(*) > 2;

✅ Output:

📊 Combining with ORDER BY and LIMIT

Top 2 customers by total spending

SELECT customer_id, SUM(amount) AS total_spent

FROM orders

GROUP BY customer_id

ORDER BY total_spent DESC

LIMIT 2;

🧠 Common Mistakes

⚡ Performance Tips

• Index the columns used in GROUP BY to speed up queries
• Avoid grouping on unnecessary columns — the more you group, the more work SQL does
• Use LIMIT or TOP when fetching ranked/grouped results

✅ Summary

• GROUP BY is used to summarize data by one or more columns.
• HAVING filters results after aggregation — unlike WHERE.
• You can use multiple aggregation functions in a single query.
• Real-world applications include dashboards, KPIs, reports, and fraud detection.