Part 2: Window Functions, CTEs & Aggregates

Window functions let you do calculations across a set of rows that are related to the current row — without collapsing rows like GROUP BY does. Think of it like looking through a sliding window at your data: you can see surrounding rows and compute things like rankings, running totals, or the previous row's value.

1. ROW_NUMBER() OVER (ORDER BY ...)

-- Number all products from cheapest to most expensive
SELECT
    ROW_NUMBER() OVER (ORDER BY price) AS row_num,
    product_name,
    price
FROM products;

2. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

-- Number each customer's orders, most recent first, within each customer
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY customer_name
        ORDER BY order_date DESC
    ) AS rn,
    customer_name,
    product_name,
    order_date
FROM orders;

3. RANK() OVER (ORDER BY ...)

-- Rank employees by number of sales closed (ties allowed, with gaps)
SELECT
    RANK() OVER (ORDER BY sales_closed DESC) AS rnk,
    employee_name,
    sales_closed
FROM employees;

4. DENSE_RANK() OVER (ORDER BY ...)

-- Dense-rank employees by sales closed (no gaps on ties)
SELECT
    DENSE_RANK() OVER (ORDER BY sales_closed DESC) AS d_rnk,
    employee_name,
    sales_closed
FROM employees;

5. Side-by-Side: ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    employee_name,
    sales_closed,
    ROW_NUMBER() OVER (ORDER BY sales_closed DESC) AS row_num,
    RANK()       OVER (ORDER BY sales_closed DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY sales_closed DESC) AS d_rnk
FROM employees;

6. NTILE(n) OVER (ORDER BY ...)

-- Split products into 3 price tiers
SELECT
    product_name,
    price,
    NTILE(3) OVER (ORDER BY price) AS price_tier
FROM products;

7. PERCENT_RANK() OVER (ORDER BY ...)

SELECT
    product_name,
    price,
    PERCENT_RANK() OVER (ORDER BY price) AS pct_rank
FROM products;

8. CUME_DIST() OVER (ORDER BY ...)

SELECT
    product_name,
    price,
    CUME_DIST() OVER (ORDER BY price) AS cume
FROM products;

9. LAG(col, n) OVER (ORDER BY ...)

-- Compare each month's sales total to the previous month
SELECT
    month_name,
    total_sales,
    LAG(total_sales, 1) OVER (ORDER BY month_num) AS prev_month,
    total_sales - LAG(total_sales, 1) OVER (ORDER BY month_num) AS change
FROM monthly_sales;

10. LAG() with PARTITION BY

-- Each employee's monthly sales compared to their previous month
SELECT
    employee_name,
    sale_date,
    amount,
    LAG(amount, 1) OVER (
        PARTITION BY employee_name
        ORDER BY sale_date
    ) AS prev_amount
FROM sales;

11. LEAD(col, n) OVER (ORDER BY ...)

-- Show the next scheduled delivery for each order
SELECT
    customer_name,
    order_date,
    LEAD(order_date, 1) OVER (
        ORDER BY order_date
    ) AS next_order
FROM orders;

12. FIRST_VALUE(col) OVER (...)

-- Show each product alongside the cheapest product's name
SELECT
    product_name,
    price,
    FIRST_VALUE(product_name) OVER (
        ORDER BY price
    ) AS cheapest_product
FROM products;

13. LAST_VALUE(col) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Show each product alongside the most expensive product's name
SELECT
    product_name,
    price,
    LAST_VALUE(product_name) OVER (
        ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS most_expensive
FROM products;

14. NTH_VALUE(col, n) OVER (...)

-- Show the 2nd cheapest product alongside each row
SELECT
    product_name,
    price,
    NTH_VALUE(product_name, 2) OVER (
        ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_cheapest
FROM products;

Visual: What Is a "Window"?

A CTE is like giving a name to a temporary result set. Instead of nesting subqueries deep inside each other (which gets hard to read), you write WITH name AS (query) at the top, and then use that name like a table. It exists only for the duration of that single SQL statement.

1. Basic CTE (WITH ... AS)

-- Find customers whose total spending exceeds 1000
WITH customer_totals AS (
    SELECT
        customer_name,
        SUM(total) AS total_spent,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_name
)
SELECT *
FROM customer_totals
WHERE total_spent > 1000;

2. Multiple CTEs Chained Together

-- CTE 1: total sales per department
-- CTE 2: average across departments
-- Final: departments above average
WITH dept_counts AS (
    SELECT
        department,
        COUNT(*) AS total_sales
    FROM sales
    GROUP BY department
),
avg_sales AS (
    SELECT AVG(total_sales) AS avg_count
    FROM dept_counts
)
SELECT
    d.department,
    d.total_sales,
    ROUND(a.avg_count, 1) AS overall_avg
FROM dept_counts d
CROSS JOIN avg_sales a
WHERE d.total_sales > a.avg_count;

3. Recursive CTE

  Diana Prince (CEO)
  ├── Bob Wilson (VP Engineering)
  │   ├── John Smith (Senior Developer)
  │   └── Jane Doe (Senior Developer)
  ├── Charlie Brown (VP Marketing)
  │   └── Alice Johnson (Marketing Manager)
  └── Sarah Lee (VP Sales)
      └── Mike Chen (Sales Manager)

-- Build the full hierarchy from a self-referencing table
WITH RECURSIVE org_chart AS (
    -- Anchor: start with the top-level (no manager)
    SELECT
        employee_id,
        employee_name,
        title,
        manager_id,
        0 AS level,
        CAST(employee_name AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find employees whose manager is in the previous level
    SELECT
        e.employee_id,
        e.employee_name,
        e.title,
        e.manager_id,
        o.level + 1,
        CONCAT(o.path, ' > ', e.employee_name)
    FROM employees e
    INNER JOIN org_chart o
        ON e.manager_id = o.employee_id
)
SELECT
    CONCAT(REPEAT('  ', level), employee_name) AS hierarchy,
    title,
    level,
    path
FROM org_chart
ORDER BY path;

Aggregate functions take many rows and crunch them down into a single value (or one value per group if you use GROUP BY). They are the bread and butter of reporting.

1. COUNT(*)

-- How many orders were placed in total?
SELECT
    COUNT(*) AS total_orders
FROM orders;

2. COUNT(column)

-- How many customers have a phone number on file?
SELECT
    COUNT(phone) AS with_phone,
    COUNT(*) AS total_customers
FROM customers;

3. COUNT(DISTINCT column)

-- How many different products have been ordered?
SELECT
    COUNT(DISTINCT product_name) AS unique_products,
    COUNT(product_name) AS total_order_lines
FROM orders;

4. SUM()

-- Total revenue per department
SELECT
    department,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY department;

5. AVG()

-- Average sale amount by department
SELECT
    department,
    ROUND(AVG(amount), 2) AS avg_amount
FROM sales
GROUP BY department;

6. MIN()

-- Earliest order date and smallest sale per department
SELECT
    department,
    MIN(amount) AS smallest_sale,
    MIN(sale_date) AS earliest_date
FROM sales
GROUP BY department;

7. MAX()

-- Largest sale and latest date per department
SELECT
    department,
    MAX(amount) AS largest_sale,
    MAX(sale_date) AS latest_date
FROM sales
GROUP BY department;

8. GROUP_CONCAT(col SEPARATOR ',')

-- List all products each customer has ordered, in one cell
SELECT
    customer_name,
    GROUP_CONCAT(product_name SEPARATOR ', ') AS products_ordered
FROM orders
GROUP BY customer_name;

9. GROUP_CONCAT with ORDER BY and DISTINCT

-- Unique products per category, sorted alphabetically
SELECT
    category,
    GROUP_CONCAT(
        DISTINCT product_name
        ORDER BY product_name ASC
        SEPARATOR ' | '
    ) AS available_products
FROM products
GROUP BY category;

10. JSON_ARRAYAGG(col)

-- List each customer's orders as a JSON array
SELECT
    customer_name,
    JSON_ARRAYAGG(product_name) AS products_json
FROM orders
GROUP BY customer_name;

11. JSON_OBJECTAGG(key, value)

-- Build a JSON price list for each category
SELECT
    category,
    JSON_OBJECTAGG(product_name, price) AS price_list
FROM products
GROUP BY category;

12. STD() / STDDEV()

-- How consistent are sale amounts within each department?
SELECT
    department,
    COUNT(*) AS num_sales,
    ROUND(AVG(amount), 2) AS avg_amount,
    ROUND(STD(amount), 2) AS std_dev
FROM sales
GROUP BY department;

13. VARIANCE()

-- Variance of sale amounts per department
SELECT
    department,
    ROUND(AVG(amount), 2) AS avg_amount,
    ROUND(VARIANCE(amount), 2) AS amount_variance,
    ROUND(STD(amount), 2) AS std_dev
FROM sales
GROUP BY department;