Advanced Sql Querying

• Module 1 Introduction: Beyond the Basic SELECT
• The query as a question
• `WHERE` in depth
• `ORDER BY`, `LIMIT`, and `DISTINCT`
• Expressions and aliases
• `CASE WHEN`
• `LIKE` and its traps
• Mini-project: answer 5 business questions about Reservo

• Module 2 Introduction: JOINs in Depth
• `INNER JOIN` and the `ON` clause
• `LEFT JOIN` and rows without a match
• The anti-join: finding what's missing
• The self-join
• Joining three or more tables
• `ON` vs `WHERE` and the Cartesian product
• Mini-project: Reservo questions across tables

• 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 4 — Subqueries: a question inside another
• The scalar subquery
• Subqueries in `WHERE` with `IN` and `NOT IN`
• `EXISTS` and `NOT EXISTS`
• The derived table in the `FROM`
• The correlated subquery
• Subquery vs JOIN and the `NOT IN` with `NULL` trap
• Mini-project: answering Reservo questions with subqueries

• 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

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.