Advanced SQL: CTEs, Recursive Queries and Window Functions
Advanced SQL (CTEs, window functions) is an advanced-level course of 5 lessons (about 2 h 47 min), with 17 exercises and 15 quiz questions. Prerequisites: Intermediate SQL (joins, subqueries). Exercises run in the browser, with nothing to install.
- SQL
- Advanced
- 5 lessons
- 2 h 47 min
- 17 exercises
- Exercises run in the browser
The course in brief
This advanced course gives you the analytical SQL tools that data analysts use every day. You will structure your queries with CTEs, walk hierarchies and generate calendars with WITH RECURSIVE, then master window functions: rankings, top N per group, running totals, moving averages and period-over-period comparisons.
What you will be able to do
- Break a complex analysis down into readable, testable CTEs
- Generate series and walk hierarchies with WITH RECURSIVE
- Compute aggregates per partition without losing row-level detail
- Rank rows and extract a top N per group with ROW_NUMBER, RANK and DENSE_RANK
- Compute running totals and moving averages while controlling window frames
- Measure changes and time gaps with LAG and LEAD, and segment with NTILE
Syllabus: 5 lessons
- Write a common table expression (CTE) with WITH
- Chain several CTEs to break a calculation into steps
- Choose between a CTE, a subquery and a view
- 02
Recursive CTEs: Series and Hierarchies
- Understand the structure of a recursive CTE (anchor, recursive part, stop condition)
- Generate a series of numbers or dates
- Walk a hierarchy (org chart, categories) while computing the level and the path
- 03
Window Functions: OVER, PARTITION BY and Rankings
- Understand the difference between a classic aggregate and a window function
- Compute an aggregate per partition without reducing the number of rows
- Rank rows with ROW_NUMBER, RANK and DENSE_RANK
- Extract the top N per group
OVER clausePARTITION BYROW_NUMBER, RANK, DENSE_RANKTop N per group
- 04
Running Totals and Moving Averages: ORDER BY and Window Frames
- Compute a running total with SUM ... OVER (ORDER BY ...)
- Reset a running total per group with PARTITION BY
- Define a window frame with ROWS BETWEEN for a moving average
- Understand the default frame and the difference between ROWS and RANGE
- 05
Comparing Over Time: LAG, LEAD and NTILE
- Access the previous or next row with LAG and LEAD
- Compute changes (difference and percentage) from one period to the next
- Measure gaps between events for the same customer or salesperson
- Split rows into equal-sized groups with NTILE
Final project
Monthly sales analytics report
Starting from an 18-month sales table (date, region, salesperson, customer, amount) and a sales hierarchy table, produce in SQL the data for a monthly report: complete monthly series, changes over time, year-to-date totals, rankings and customer segmentation.
Deliverables
- A .sql file structured into named, commented CTEs
- A complete monthly series generated by a recursive CTE (months with no sales at 0)
- Monthly revenue with a year-to-date running total, a 3-month moving average and the percentage change versus the previous month
- The top 3 salespeople per region with explicit tie handling (RANK or DENSE_RANK, with justification)
- Customer segmentation into spending quartiles (NTILE) and the average time between two purchases per segment
- Consolidated revenue per sales manager by walking the hierarchy
Assessment criteria
- Correct results, checked against a small sample computed by hand
- Explicit window frames and deterministic sorting
- Edge cases handled: first month, division by zero, ties, missing months
- Readability: well-named CTEs, one step per CTE, comments
- Portability: SQLite / PostgreSQL differences flagged in comments
Frequently asked questions
What is a window function in SQL?
It is a function that computes a value for each row from a set of related rows (the window), defined by the OVER clause, without collapsing the rows the way GROUP BY does. It is used for rankings, running totals and comparisons.
What is the difference between a CTE and a subquery?
The result is the same, but a CTE is named, written before the main query and reusable several times within it. It makes multi-step analyses far more readable.
Do window functions work in every database engine?
Yes for current engines: PostgreSQL, SQLite (since 3.25), MySQL 8, MariaDB, SQL Server, Oracle and cloud data warehouses. The course uses SQLite and points out the differences with PostgreSQL.
Is this course useful for Power BI?
Yes. Preparing clean data in SQL (calendars, rankings, running totals) simplifies the Power BI model, and window concepts help you understand the equivalent DAX functions.
This course, told for your job
Say what you want to be able to do: “Advanced SQL (CTEs, window functions)” takes its place in your path, with examples from your field. The concepts stay those of the course.