INTERMEDIATE · 8 MODULES · 64 LESSONS
Advanced SQL Querying
Answer complex business questions with a single clear, fast SQL query, and stop exporting to spreadsheets what the database can already do for you.
- For you if
- You already write SELECT, WHERE, and ORDER BY, and anything beyond that feels hard.
- By the end
- You'll know how to use JOINs, CTEs, and window functions, and read the query plan to speed up a slow query.
- You need
- Basic SQL and Python 3. The database runs on SQLite, with nothing else to install.

Curriculum
02JOINs a fondo — INNER, LEFT, self y multi-tabla
03Agregación — GROUP BY, HAVING y funciones de agregado
- Module 3 — Aggregation: summary questions about Reservo
- Aggregate functions and what they do with NULL
- COUNT(*), COUNT(column), and COUNT(DISTINCT): three different counts
- GROUP BY: collapsing rows into groups (and the golden rule)
- Grouping by an expression: bookings per month with strftime
- HAVING vs WHERE: filtering rows before, filtering groups after
- Aggregating over JOINs: revenue per room (with its name) and the LEFT JOIN trap
- Mini-project: Reservo's revenue summary
04Subconsultas — escalares, IN, EXISTS y correlacionadas
05CTEs — WITH y consultas legibles (incluida la recursiva)
- Module 5 — CTEs: splitting a complex query into named steps
- What a CTE is, and why it's about readability
- The WITH clause: syntax, scope, and multiple CTEs
- Chained CTEs: building the result step by step
- CTE vs subquery vs view: when to use each
- Recursive CTEs: anchor case, recursive case, and stop condition
- The recursive date series: a monthly report with no gaps
- Mini-project: rewrite with CTEs and build the gap-free monthly report
06Window functions — `ROW_NUMBER`, `RANK`, running totals
- Module 6 — Window functions: calculating over a window of rows without collapsing them
- The window keeps the detail AND aggregates (unlike `GROUP BY`)
- The `OVER` clause: `PARTITION BY` defines the groups, `ORDER BY` the order
- `ROW_NUMBER`, `RANK`, and `DENSE_RANK`: three ways to number, and the tie that tells them apart
- The running total: `SUM(...) OVER (ORDER BY ...)` = accumulated revenue
- `LAG` and `LEAD`: looking at the previous or next row (the month-over-month delta)
- Top-N per group: `ROW_NUMBER` + a CTE (because the `WHERE` can't see the window)
- Mini-project: Reservo's report rankings and running totals
07Optimización de consultas — leer el plan y acelerar
- Module 7 — Query optimization: reading the plan and speeding things up
- Why a query is slow, and how to read `EXPLAIN QUERY PLAN`
- Reading `SCAN` vs `SEARCH`: the index changes one word (and 100× the time)
- Indexes for `WHERE`, `JOIN`, and `ORDER BY`
- SARGable predicates: when a function on the column kills the index
- The N+1 pattern: a hundred queries where one would do
- Measuring before and after: choosing with data, not intuition
- Mini-project: optimize Reservo's slow queries
08El capstone — el reporte analítico de Reservo
- Module 8 — Reservo's analytical report: the capstone that weaves the seven modules together
- Revenue by room and by month: the dashboard's first gauge
- Top members by spend: aggregation with ranking
- Accumulated revenue and month-over-month change: the dashboard's odometer
- Occupancy by room: the business's fuel level
- Refund rate and net revenue: the warning light
- The gap-free monthly report: composing with recursive CTEs and windows
- Delivering the report and closing out the guide
Reviews
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!