Week 3 • Day 2Week 3
Day 2:Aggregations (`GROUP BY`, `HAVING`) & Multi-Table `JOIN`s
Battle-tested daily lesson note • 100% runnable code, interview Q&A, test suite & interactive quiz
Week 3 — Day 2: Aggregations (GROUP BY, HAVING) & Multi-Table JOINs
Mentor's Note (10+ YOE Staff AI Engineer POV): Real databases don't cram everything into one giant table (which causes massive data duplication!). Instead, we Normalize data into separate linked tables (
tenants,users,llm_requests) and connect them at query time usingJOINs andGROUP BYaggregations!
Part 1: SQL Execution Order & WHERE vs HAVING (Classic Interview Question!)
Did you know that SQL does NOT execute a query from top to bottom (SELECT is NOT run first!)?
Here is the true internal execution order of a SQL query:
text
1. FROM & JOIN ──> Assemble the base tables together
2. WHERE ──> Filter individual raw rows BEFORE grouping! (Cannot use SUM() or COUNT() here!)
3. GROUP BY ──> Bucket the surviving rows into groups
4. HAVING ──> Filter the aggregated groups AFTER grouping! (e.g., HAVING SUM(cost_usd) > 10.0)
5. SELECT ──> Compute the final output columns & aliases
6. ORDER BY ──> Sort the final result set
7. LIMIT / OFFSET ──> Return the top N rows
Part 2: Visualizing INNER JOIN vs LEFT JOIN
text
Table A (tenants) Table B (llm_requests)
+----+--------------+ +----+-----------+--------+
| id | name | | id | tenant_id | tokens |
+----+--------------+ +----+-----------+--------+
| 1 | Acme Corp | | 10 | 1 | 500 |
| 2 | Beta Startup | (0 calls) | 11 | 1 | 700 |
+----+--------------+ +----+-----------+--------+
INNER JOIN: Returns only rows that have a match in BOTH tables (Beta Startupdisappears because it has 0 requests!).LEFT JOIN+COALESCE(SUM(tokens), 0): Keeps ALL rows from the left table (tenants), fillingNULL(or0viaCOALESCE) when the right table has no matches (Beta Startupappears with0tokens!).
Part 3: Hands-On Lab — Multi-Tenant LLM Spend & Latency Analytics (sql_joins_analytics.py)
Check out src/sql_joins_analytics.py (in Tab 2: Codes — click Run in Browser to execute) and run the script and tests:
powershell
python week03_sql_databases_from_zero_to_pro/day02_aggregations_groupby_having_and_joins/src/sql_joins_analytics.py
python -m unittest discover -s week03_sql_databases_from_zero_to_pro/day02_aggregations_groupby_having_and_joins/tests -v