SQL Advanced: CTEs, Subqueries & Window Functions

Picks up exactly where a SQL basics course ends and covers subqueries, common table expressions, and window functions in depth, including the specific bugs that silently produce wrong answers in production, like the NOT IN and NULL trap. Built on a real five-table relational database with genuine foreign key relationships, delivered as both SQLite and CSVs. Covers scalar and correlated subqueries, recursive CTEs, ranking and offset window functions, frame clauses, and a practical decision guide for choosing the right tool. Closes with two portfolio projects producing polished, presentation-ready analytical queries.

Rutvik Acharya

Principal Data Scientist

Atlassian

Loading modules...

What You'll Learn

Write scalar, correlated, and derived-table subqueries, and avoid the NOT IN / NULL trap that silently breaks queries

Refactor nested subqueries into readable CTEs, including recursive CTEs for date series and hierarchical data

Use ranking functions, LAG/LEAD, and frame clauses to solve "top N per group" and trend questions directly

Choose confidently between a subquery, a CTE, and a window function for a given problem

Who Should Attend

Analysts and engineers comfortable with basic SQL who want to write genuinely advanced queries

Anyone who has been burned by a query that silently returned the wrong answer

Candidates preparing for SQL-heavy technical interviews, where window functions are heavily tested

Analysts who want a portfolio-ready ranking report and trend analysis built entirely in SQL

CERTIFICATION

Certificate of Completion

6 Modules

6 Hour

17 Lessons

17 Challenges

Language: English

FAQ

FREQUENTLY ASKED QUESTIONS

`NOT IN` against a subquery that returns even one `NULL` silently makes the entire query return zero rows — no error, no warning. The course demonstrates this with a real query that returns 0 when the correct answer is 345, then shows why `NOT EXISTS` avoids the problem entirely.

By showing the same query written both ways with identical results, then pointing out where the two genuinely diverge: recursive CTEs can generate rows that don't exist in your data at all, which no ordinary subquery can do.

Generating a complete calendar of every day in a month and left-joining real orders against it — revealing that one store had zero orders on 17 of 31 days, completely invisible to a plain `GROUP BY`, which only shows days that had at least one order.

Using a genuine tie in the practice database — two products that sold the exact same quantity — and showing how each function handles that tie differently, rather than a hypothetical example with invented numbers.

Adding `ORDER BY` inside a window function silently changes its default frame from the whole partition to a running calculation — the same `SUM()` call becomes a flat total or a running total depending on that one addition, with no other change to the query.

It shows them actually disagreeing, using 52 real rows in the practice database that share the same value — `ROWS` produces a normal running total, `RANGE` gives every tied row the same combined total, and the course explains exactly why.

Through a direct comparison: both solve the top customer in each region identically, but the moment the question changes to top 3 instead of top 1, the window function version needs a one-character edit while the subquery version needs a structural rewrite.

A real, deliberately messy three-level nested subquery is rebuilt into three chained CTEs and one window function, then both versions are run and checked to confirm they return the exact same 12 rows — proving the refactor changed nothing but readability.

A single query finding the top 2 products in every category, in every region, using a two-partition `PARTITION BY` — and it surfaces a genuine tie in the real data along the way, which the project addresses directly.

It combines a running total and period-over-period growth with a customer cohort retention analysis and a recursive date-series check — including a real finding that a healthy network-wide trend was hiding a specific store with a serious reporting gap underneath it.

Scroll Up

GET FREE CAREER COUNSELING