GuideIntermediate
Advanced Sql Querying
Learn to query a relational database with real SQL: JOINs in depth, aggregation, subqueries, CTEs (including recursive ones), window functions, and query optimization by reading the execution plan. You work on Reservo's database, a coworking room-booking system, and come out knowing how to answer complex business questions with a single query that's well written, readable, and efficient. It closes with a full analytical report: revenue by room and by month, top members by spend, occupancy, refund rate, and ranking.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
Outcomes
What you'll be able to do
- Go beyond the basic SELECT: expressions, aliases, and thinking of every query as a business question against Reservo's database
- Master JOINs in depth: INNER JOIN, LEFT JOIN (and rows with no match), self-join, joining three or more tables, ON vs WHERE, and the cartesian product trap
- Aggregate data with COUNT, SUM, AVG, MIN, and MAX, use GROUP BY and HAVING, and combine aggregates with JOINs
- Write scalar subqueries, subqueries in WHERE with IN/EXISTS, subqueries in FROM as a derived table, and correlated subqueries — and decide when each beats a JOIN
- Simplify complex queries with CTEs (WITH), chain them, and write recursive CTEs (WITH RECURSIVE) for hierarchies and series
- Use window functions: OVER (PARTITION BY... ORDER BY...), ROW_NUMBER, RANK, DENSE_RANK, running totals, LAG/LEAD, and top-N per group
- Read the execution plan with EXPLAIN QUERY PLAN, design indexes for queries, and avoid non-SARGable predicates and the N+1 pattern
- Deliver Reservo's full analytical report: revenue by room and month, top members by spend, occupancy by room, refund rate, and ranking
Before you start
What you need to bring
It's for you if...
- Devs and analysts who already know basic SELECT, WHERE, and ORDER BY and need to answer complex business questions with a single query
- Anyone preparing for technical interviews that ask you to write JOINs, subqueries, CTEs, or window functions live
- Backend devs who need to optimize slow queries by reading the execution plan
- Data analysts who want to stop exporting to spreadsheets for what SQL already solves
Requirements and materials
- Basic SQL: SELECT, WHERE, ORDER BY, LIMIT
- Python 3 installed (the `sqlite3` module ships with the standard library; nothing else to install)
- No prior experience with JOINs, subqueries, or window functions required
Content
The syllabus, module by module
Open any of them to see its lessons.
- 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
- 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
- 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
- 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
- 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
Where it fits
This guide is part of something bigger
It's studied inside these programs, with support and dates.
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
No. This guide is self-paced with no dates. The bootcamp is live, by cohort, with work someone reviews.
Start whenever you like
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!