SQL Intermediate Course: CTEs, Window Functions and Real Reports

You can SELECT, filter, GROUP BY and JOIN. This course covers what separates a beginner from the person the team asks for reports: CTEs, window functions (rankings, running totals, month-on-month growth), anti-joins, recursive queries for org charts and calendars, pivot reports, indexes and query plans, and a final customer RFM and outstanding-payments project.

All lessons use one practice database, shop2.db (SQLite, opens in the free DB Browser for SQLite), with customers, orders, payments in instalments, an employee hierarchy and monthly targets. Every task has its real expected result in the exercise file. The SQL works almost unchanged in PostgreSQL, MySQL 8, SQL Server and Oracle; differences are noted.

Before you start: the SQL Beginner Course or equivalent.

1. 1SQL Intermediate Lesson 1: Common Table Expressions (WITH)Write readable SQL with CTEs: name a subquery once, chain several steps, compare each customer with the average and compute category shares.
2. 2SQL Intermediate Lesson 2: Window Functions – ROW_NUMBER, RANK, DENSE_RANKRank customers, find the top customer in each region and each customer's latest order with ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY.
3. 3SQL Intermediate Lesson 3: Running Totals and Moving AveragesRunning totals, year-to-date by region, 3-month moving averages and share of total with SUM() OVER and window frames (ROWS BETWEEN).
4. 4SQL Intermediate Lesson 4: LAG and LEAD – Month-on-Month GrowthCompare each row with the previous or next one: month-on-month growth %, days between a customer's orders and the next order date…
5. 5SQL Intermediate Lesson 5: Joins in Depth – Anti-Joins, Self-Joins, Cross JoinsFind unpaid orders with LEFT JOIN ... IS NULL and NOT EXISTS, list employees with their managers via a self-join, and fill…
6. 6SQL Intermediate Lesson 6: UNION, INTERSECT and EXCEPTCombine and compare result sets: one list from two tables, customers who bought both products, customers who never bought one, and activity…
7. 7SQL Intermediate Lesson 7: Recursive CTEs – Org Charts and Date SeriesWalk a manager hierarchy, build reporting paths, total a team's salary cost and generate a calendar to find days with no orders,…
8. 8SQL Intermediate Lesson 8: Pivot Reports With CASETurn rows into columns with conditional aggregation: sales by status per region, orders per quarter, payment-mode mix and actual vs target with…
9. 9SQL Intermediate Lesson 9: Indexes and Query PlansRead EXPLAIN QUERY PLAN, see SCAN turn into SEARCH with an index, build covering indexes, and learn why functions on columns stop…
10. 10SQL Intermediate Lesson 10: Project – Customer RFM and Outstanding PaymentsFinal project: recency, frequency and monetary scores with NTILE, customer segments, outstanding receivables per customer and average collection days by payment mode.

Next: query the same data from Python in the Python course, or model it in Power BI.