Lesson 14 • Expert
Advanced Queries
Master window functions, CTEs, recursive queries, and CASE expressions — the tools that make SQL analysts indispensable.
🎯 What You'll Learn
- Window functions: SUM, AVG, COUNT OVER partitions
- Ranking: ROW_NUMBER, RANK, DENSE_RANK
- Row navigation: LAG, LEAD, FIRST_VALUE
- CTEs and recursive CTEs for hierarchies
- CASE expressions for conditional logic
Window Functions — The Game Changer
Window functions perform calculations across a set of related rows without collapsing them into one row (unlike GROUP BY). You get the detail and the summary.
🪟 Real-World Analogy
Imagine a class roster showing each student's grade AND the class average next to every student. GROUP BY would collapse everyone into one row. Window functions keep every student visible while adding the average as a new column.
💡 Pro Tip
The syntax is: function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY creates groups (like GROUP BY), ORDER BY sorts within each group.
Running Totals & Moving Averages
Calculate cumulative sums and rolling averages
-- Window functions: calculate across related rows
-- without collapsing them (unlike GROUP BY)
-- Running total of sales
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM sales;
-- Running average (last 7 days)
SELECT
order_date,
amount,
ROUND(AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_7d
FROM sales;Ranking Functions
Rank, row number, and top-N per group
-- Ranking functions
SELECT
name, department, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- Rank within each department (PARTITION BY)
SELECT
name, department, salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees;
-- Top 3 earners per department
SELECT * FROM (
SELECT n
...CTEs — Readable Complex Queries
CTEs (Common Table Expressions) let you name intermediate result sets, making complex queries readable. Recursive CTEs can traverse hierarchies like org charts and category trees.
⚠️ Common Mistake
Forgetting the base case in recursive CTEs leads to infinite loops. Always start with an anchor query (WHERE manager_id IS NULL), then add the recursive part with UNION ALL.
CTEs & Recursive Queries
Chain multiple CTEs and traverse hierarchical data
-- CTE: Common Table Expression (WITH clause)
WITH monthly_stats AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders,
SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
growth AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month) * 100, 1) AS pct
...CASE Expressions
Add conditional logic and create pivot-style reports
-- CASE: conditional logic in SQL
SELECT
product_name,
price,
CASE
WHEN price < 25 THEN 'Budget'
WHEN price < 100 THEN 'Mid-Range'
WHEN price < 500 THEN 'Premium'
ELSE 'Luxury'
END AS price_tier,
CASE
WHEN stock = 0 THEN 'Out of Stock'
WHEN stock < 10 THEN 'Low Stock'
ELSE 'In Stock'
END AS availability
FROM products;
-- CASE in aggregation: pivot-style report
SELECT
department,
COUNT(*) AS total,
SU
...📘 Quick Reference
| Function | Purpose |
|---|---|
| SUM() OVER (...) | Running total |
| ROW_NUMBER() OVER (...) | Unique sequential number |
| RANK() / DENSE_RANK() | Ranking with/without gaps |
| LAG(col) / LEAD(col) | Previous / next row value |
| WITH cte AS (...) | Named subquery (CTE) |
| CASE WHEN ... THEN ... END | Conditional logic |
🎉 Lesson Complete!
You've mastered advanced SQL analytics. Next, put everything together in the Final Project — building a complete production-ready database!
Sign up for free to track which lessons you've completed and get learning reminders.