Advanced SQL

30 min Intermediate 4.2k learners

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:

1. Which SQL feature is best for ranking rows without grouping?
2. What keyword introduces a recursive query?

Great Work! 🚀

Continue your journey with ETL & Data Warehousing or revisit SQL Basics.