🛢️ Week 6 – SQL for Data Science · Day 33
Day 33: SQL Joins, GROUP BY & HAVING
⏱ 2.5 hours📶 Intermediate🎯 100% self-study
Day 33 / 60
✅ Today you will learn
- Aggregate functions and GROUP BY
- HAVING vs WHERE
- INNER, LEFT, RIGHT/FULL joins
- Self-join, UNION and sub-queries
- Translate between SQL and pandas
🏢 In the real world
The HR head asks: “Show me head-count and average salary per department and office location – and list anyone not mapped to a department.” Finance asks: “Which cities crossed ₹6 lakh in prepaid revenue?” Both are GROUP BY + JOIN questions – the bread and butter of every SQL interview.
Python – setup
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
pd.read_csv("sales_data.csv").to_sql("sales", con, if_exists="replace", index=False)
pd.read_csv("employees.csv").to_sql("employees", con, if_exists="replace", index=False)
pd.read_csv("departments.csv").to_sql("departments", con, if_exists="replace", index=False)
print([r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type = 'table'")])['sales', 'employees', 'departments']
Aggregates and GROUP BY
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT COUNT(*) AS orders,
COUNT(rating) AS rated_orders,
COUNT(DISTINCT city) AS cities,
ROUND(SUM(quantity * unit_price * (1 - discount))) AS revenue,
ROUND(AVG(rating), 2) AS avg_rating,
MIN(order_date) AS first_day, MAX(order_date) AS last_day
FROM sales
""")) orders rated_orders cities revenue avg_rating first_day last_day
0 2000 1965 11 6879783.0 4.06 2024-01-01 2024-12-31
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT region,
COUNT(*) AS orders,
ROUND(SUM(quantity * unit_price * (1 - discount)) / 100000.0, 2) AS revenue_lakh,
ROUND(AVG(quantity * unit_price * (1 - discount))) AS aov
FROM sales
GROUP BY region
ORDER BY revenue_lakh DESC
""")) region orders revenue_lakh aov
0 South 654 20.82 3183.0
1 West 519 18.98 3656.0
2 North 508 16.01 3151.0
3 East 319 13.00 4075.0
🧠 The GROUP BY rule
Every column in SELECT must either be in GROUP BY or inside an aggregate (SUM, AVG, COUNT…). SELECT city, region, SUM(...) ... GROUP BY city is an error in most databases.
HAVING – filter groups
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT city,
ROUND(SUM(quantity * unit_price * (1 - discount)) / 100000.0, 2) AS revenue_lakh
FROM sales
WHERE payment_mode <> 'Cash on Delivery' -- row filter (before grouping)
GROUP BY city
HAVING SUM(quantity * unit_price * (1 - discount)) > 600000 -- group filter
ORDER BY revenue_lakh DESC
""")) city revenue_lakh
0 Delhi 7.98
1 Bengaluru 7.48
2 Kolkata 6.09
JOINs
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT e.name, e.salary, d.dept_name, d.location
FROM employees AS e
INNER JOIN departments AS d ON e.dept_id = d.dept_id
ORDER BY e.salary DESC
LIMIT 5
"""))
print(q("SELECT COUNT(*) AS inner_rows FROM employees e JOIN departments d ON e.dept_id = d.dept_id")) name salary dept_name location
0 Kunal 146000 Data Science Bengaluru
1 Tanvi 135000 Data Science Bengaluru
2 Neha 122000 Data Science Bengaluru
3 Kabir 118000 Data Science Bengaluru
4 Meera 115000 Data Science Bengaluru
inner_rows
0 28
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT e.emp_id, e.name, e.dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL -- employees with no matching department
""")) emp_id name dept_id dept_name
0 129 Nikhil 60 None
1 130 Priya 60 None
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT COALESCE(d.dept_name, 'Unassigned') AS department,
COALESCE(d.location, '-') AS location,
COUNT(e.emp_id) AS headcount,
ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
GROUP BY department, location
ORDER BY headcount DESC
""")) department location headcount avg_salary
0 Data Science Bengaluru 11 105818.0
1 Sales Mumbai 10 57500.0
2 HR Delhi 5 52800.0
3 Unassigned - 2 71500.0
4 Finance Mumbai 1 61000.0
5 Marketing Pune 1 79000.0
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
UNION ALL
SELECT 'No department', COUNT(*) FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments)
""")) dept_name headcount
0 Data Science 11
1 Finance 1
2 HR 5
3 Marketing 1
4 Sales 10
5 No department 2
💡 FULL OUTER JOIN
PostgreSQL and SQLite 3.39+ support FULL OUTER JOIN directly. MySQL does not – combine a LEFT JOIN and a RIGHT JOIN with UNION, as above.
Self-join and sub-queries
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT a.name AS employee, b.name AS colleague, a.city
FROM employees a
JOIN employees b ON a.city = b.city AND a.emp_id < b.emp_id AND a.dept_id = b.dept_id
WHERE a.city = 'Pune'
""")) employee colleague city
0 Kabir Aditya Pune
1 Ananya Isha Pune
2 Ananya Pooja Pune
3 Ananya Nisha Pune
4 Isha Pooja Pune
5 Isha Nisha Pune
6 Pooja Nisha Pune
7 Siddharth Harsh Pune
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT name, salary, dept_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id) -- correlated sub-query
ORDER BY dept_id, salary DESC
LIMIT 8
""")) name salary dept_id
0 Vihaan 78000 10
1 Yash 74000 10
2 Pooja 65000 10
3 Isha 61000 10
4 Kunal 146000 20
5 Tanvi 135000 20
6 Neha 122000 20
7 Kabir 118000 20
SQL ↔ pandas cheat sheet
| SQL | pandas |
|---|---|
| SELECT a, b FROM t | df[["a", "b"]] |
| WHERE city = 'Pune' | df[df.city == "Pune"] |
| GROUP BY city … SUM(x) | df.groupby("city")["x"].sum() |
| HAVING SUM(x) > 10 | .loc[lambda s: s > 10] after groupby |
| ORDER BY x DESC LIMIT 5 | df.nlargest(5, "x") |
| LEFT JOIN … ON k | df.merge(other, on="k", how="left") |
| UNION ALL | pd.concat([a, b]) |
| COALESCE(x, 0) | df.x.fillna(0) |
🏅 Job skills unlocked today
✔ Summarise with GROUP BY and aggregates✔ Filter groups with HAVING✔ Choose and write the right JOIN✔ Use sub-queries, self-joins and UNION
Practice tasks
🏋️ Practice – do it yourself
Task 1: Revenue and number of orders per category and payment mode, only combinations with at least 100 orders.
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT category, payment_mode, COUNT(*) AS orders, ROUND(SUM(quantity*unit_price*(1-discount))) AS revenue
FROM sales GROUP BY category, payment_mode HAVING COUNT(*) >= 100 ORDER BY revenue DESC""")) category payment_mode orders revenue
0 Electronics UPI 205 1516089.0
1 Electronics Card 115 1047969.0
2 Fashion UPI 235 703723.0
3 Fashion Card 128 406734.0
4 Grocery UPI 271 405319.0
5 Home & Kitchen UPI 146 192784.0
6 Grocery Card 113 180109.0
7 Grocery Cash on Delivery 105 140767.0
8 Books & Stationery UPI 104 137491.0
Task 2: Total salary cost per department location (use a JOIN).
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT d.location, SUM(e.salary) AS cost, COUNT(*) AS people FROM employees e
JOIN departments d ON e.dept_id = d.dept_id GROUP BY d.location ORDER BY cost DESC""")) location cost people
0 Bengaluru 1164000 11
1 Mumbai 636000 11
2 Delhi 264000 5
3 Pune 79000 1
Task 3: Find the highest-paid employee in each department using a sub-query.
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT d.dept_name, e.name, e.salary FROM employees e JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees x WHERE x.dept_id = e.dept_id) ORDER BY e.salary DESC""")) dept_name name salary
0 Data Science Kunal 146000
1 Marketing Rohan 79000
2 Sales Vihaan 78000
3 HR Zoya 62000
4 Finance Aarav 61000
Quick quiz
🧠 Quick Quiz – test yourself
1. HAVING filters…
Use WHERE for rows.
2. COUNT(rating) vs COUNT(*)?
COUNT(*) counts rows.
3. Employees with no department appear in…
With NULL department columns.
4. UNION vs UNION ALL?
UNION ALL is faster.
5. COALESCE(x, "Unknown") returns…
First non-NULL value.
Interview corner
💼 Interview Corner
Explain the types of SQL joins.
INNER returns matching rows from both tables; LEFT returns all rows of the left table plus matches (NULLs otherwise); RIGHT is the mirror; FULL OUTER returns all rows from both; CROSS returns every combination; a self-join joins a table to itself.
Find departments with more than 5 employees.
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5; – join departments for the name.
Summary
- GROUP BY + aggregates summarise; HAVING filters groups.
- INNER = matches, LEFT = keep all left rows, FULL = everything.
- COALESCE fills NULLs; UNION ALL stacks results.
- Sub-queries compare a row with its group.
➡️ Next
Day 34: window functions and CTEs – rankings, running totals and month-over-month growth in SQL.
Frequently Asked Questions
Is a sub-query or a JOIN faster?
Modern databases often optimise both to the same plan. Write the clearer one; check EXPLAIN when performance matters.
Why did my JOIN return more rows than expected?
The key is not unique on one side – each match multiplies rows. Check with SELECT key, COUNT(*) … GROUP BY key HAVING COUNT(*) > 1.
