Day 05 — Part 1: SQL for Data Science¶
SQL is the one tool every data role requires without exception. Analysts, data scientists, data engineers, and ML engineers all query databases daily. More importantly, SQL interviews are the most predictable part of a data hiring process — the question types repeat, and a student who has practised them thoroughly can pass a technical screen that trips up many candidates with stronger Python skills.
Session Overview
Difficulty: Beginner–Intermediate Reading time: ~3 hours | Exercises: ~2 hours Prerequisites: None — SQL is independent. Basic logic and set thinking helpful.
What You Will Learn¶
By the end of this session you will be able to:
- Write SELECT queries with filtering, sorting, and aggregation
- Understand the difference between WHERE and HAVING and when each applies
- Write INNER JOIN and LEFT JOIN and explain exactly what each returns
- Write a CTE for a multi-step analytical query
- Write a window function with RANK() and LAG()
Session Roadmap¶
| # | Topic | File | Time |
|---|---|---|---|
| 1 | SELECT & WHERE — filtering, comparison operators, LIKE, IN, BETWEEN | 01-select-and-where.md |
20 min |
| 2 | GROUP BY & HAVING — COUNT/SUM/AVG, filter after grouping | 02-group-by-and-having.md |
20 min |
| 3 | JOINs — INNER, LEFT, RIGHT: what each returns, common pitfalls | 03-joins.md |
25 min |
| 4 | Subqueries — correlated vs non-correlated, WHERE/FROM/SELECT | 04-subqueries.md |
25 min |
| 5 | CTEs — WITH clause, readable multi-step queries, recursive CTEs | 05-ctes.md |
20 min |
| 6 | Window Functions — ROW_NUMBER, RANK, LAG/LEAD, PARTITION BY | 06-window-functions.md |
25 min |
| 7 | SQL Case Studies — 5 real business scenarios end to end | 07-sql-case-studies.md |
30 min |
| 8 | Interview Questions — 20 Q&As with answers | 08-sql-interview-questions.md |
35 min |
Total active learning time: ~3 hours
How to Use This Session¶
Work through files 01–06 in order — each concept builds on the previous. For each file, type the SQL queries manually rather than copy-pasting. The muscle memory of writing SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... in the correct order is itself a skill that saves time under interview pressure.
After the case studies in file 07, attempt the interview questions in file 08 before reading the answers. Treat each question as a timed challenge: give yourself 3 minutes to write a query, then compare to the model answer.
Before You Start
You can run all the SQL in this session without installing a database. Use SQLiteOnline.com or the sqlite3 module in Python to execute queries immediately. Setting up a database server is not required for this session.