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 using JOINs and GROUP BY aggregations!


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 | +----+--------------+ +----+-----------+--------+
  1. INNER JOIN: Returns only rows that have a match in BOTH tables (Beta Startup disappears because it has 0 requests!).
  2. LEFT JOIN + COALESCE(SUM(tokens), 0): Keeps ALL rows from the left table (tenants), filling NULL (or 0 via COALESCE) when the right table has no matches (Beta Startup appears with 0 tokens!).

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