Advanced SQL
Window Functions
Window functions let you perform calculations across sets of rows while keeping individual rows visible. They are used for ranking, moving averages, and running totals.
-- Rank customers by total spent
SELECT
customer_id,
SUM(total_amount) AS total_spent,
RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rank
FROM orders
GROUP BY customer_id;
Common Table Expressions (CTEs)
CTEs simplify complex queries by breaking them into smaller, reusable parts.
-- Top 5 products by sales
WITH product_sales AS (
SELECT
product_id,
SUM(quantity * price) AS total_sales
FROM order_items
GROUP BY product_id
)
SELECT *
FROM product_sales
ORDER BY total_sales DESC
LIMIT 5;
Recursive Queries
Recursive queries are used for hierarchical data, like employee-manager relationships or category trees.
-- Employee hierarchy
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id, name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
FROM employees e
INNER JOIN employee_hierarchy eh
ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;
Practice Quiz
Test your understanding of Advanced SQL:
Great Work! 🚀
Continue your journey with ETL & Data Warehousing or revisit SQL Basics.