11-window-functions.sql

-- ============================================================
-- SQL Masterclass — Chapter 11: Window Functions
-- ============================================================
-- 🔴 ADVANCED
--
-- In this chapter you will learn:
--   • What window functions are and why they're powerful
--   • ROW_NUMBER, RANK, DENSE_RANK
--   • NTILE for bucketing
--   • LAG and LEAD for accessing adjacent rows
--   • Cumulative SUM, AVG with window frames
--   • PARTITION BY for grouped windows
--   • Moving averages and running totals
-- ============================================================


-- ============================================================
-- 11.1  WHAT ARE WINDOW FUNCTIONS?
-- ============================================================
-- Window functions perform calculations across rows related
-- to the current row WITHOUT collapsing them into groups.
--
-- Syntax: function() OVER (
--            PARTITION BY ...   -- optional: groups rows
--            ORDER BY ...       -- ordering within each partition
--            ROWS BETWEEN ...   -- optional: window frame
--         )


-- ============================================================
-- 11.2  ROW_NUMBER — Unique sequential numbering
-- ============================================================

-- Number all sellers by total revenue
WITH seller_revenue AS (
    SELECT
        seller_id,
        SUM(price) AS total_revenue
    FROM order_items
    GROUP BY seller_id
)
SELECT
    ROW_NUMBER() OVER (ORDER BY total_revenue DESC) AS rank_num,
    seller_id,
    total_revenue
FROM seller_revenue
LIMIT 15;


-- Number each item within an order
SELECT
    order_id,
    order_item_id,
    product_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY price DESC) AS item_rank
FROM order_items
LIMIT 20;


-- ============================================================
-- 11.3  RANK and DENSE_RANK
-- ============================================================
-- RANK: Ties get the same rank, next rank is skipped.
-- DENSE_RANK: Ties get the same rank, next rank is NOT skipped.

-- Rank product categories by total revenue
WITH category_revenue AS (
    SELECT
        p.product_category_name,
        SUM(oi.price) AS total_revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_category_name IS NOT NULL
    GROUP BY p.product_category_name
)
SELECT
    RANK()       OVER (ORDER BY total_revenue DESC) AS rank_pos,
    DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS dense_rank_pos,
    product_category_name,
    total_revenue
FROM category_revenue
LIMIT 15;


-- Top seller per state (using RANK + PARTITION BY)
WITH seller_state_revenue AS (
    SELECT
        s.seller_state,
        s.seller_id,
        SUM(oi.price) AS total_revenue
    FROM order_items oi
    JOIN sellers s ON oi.seller_id = s.seller_id
    GROUP BY s.seller_state, s.seller_id
),
ranked AS (
    SELECT
        seller_state,
        seller_id,
        total_revenue,
        RANK() OVER (PARTITION BY seller_state ORDER BY total_revenue DESC) AS state_rank
    FROM seller_state_revenue
)
SELECT *
FROM ranked
WHERE state_rank <= 3
ORDER BY seller_state, state_rank;


-- ============================================================
-- 11.4  NTILE — Divide into equal buckets
-- ============================================================

-- Divide customers into 4 spending quartiles
WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(p.payment_value) AS total_spent
    FROM orders o
    JOIN order_payments p ON o.order_id = p.order_id
    GROUP BY o.customer_id
)
SELECT
    NTILE(4) OVER (ORDER BY total_spent) AS spending_quartile,
    customer_id,
    total_spent
FROM customer_spending
LIMIT 20;

-- Summarize quartiles
WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(p.payment_value) AS total_spent
    FROM orders o
    JOIN order_payments p ON o.order_id = p.order_id
    GROUP BY o.customer_id
),
quartiles AS (
    SELECT
        NTILE(4) OVER (ORDER BY total_spent) AS quartile,
        total_spent
    FROM customer_spending
)
SELECT
    quartile,
    COUNT(*)          AS num_customers,
    MIN(total_spent)  AS min_spend,
    AVG(total_spent)  AS avg_spend,
    MAX(total_spent)  AS max_spend
FROM quartiles
GROUP BY quartile
ORDER BY quartile;


-- ============================================================
-- 11.5  LAG and LEAD — Access adjacent rows
-- ============================================================
-- LAG(col, n) → value from n rows BEFORE current row
-- LEAD(col, n) → value from n rows AFTER current row

-- Month-over-month revenue comparison
WITH monthly_revenue AS (
    SELECT
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        SUM(oi.price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)
)
SELECT
    year_month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY year_month)  AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY year_month) AS revenue_change,
    CASE
        WHEN LAG(revenue, 1) OVER (ORDER BY year_month) > 0
        THEN ROUND(
            100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY year_month))
            / LAG(revenue, 1) OVER (ORDER BY year_month), 1
        )
        ELSE NULL
    END AS growth_pct
FROM monthly_revenue
ORDER BY year_month;


-- ============================================================
-- 11.6  CUMULATIVE SUM — Running totals
-- ============================================================

-- Running total of monthly revenue
WITH monthly_revenue AS (
    SELECT
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        SUM(oi.price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)
)
SELECT
    year_month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY year_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM monthly_revenue
ORDER BY year_month;


-- Cumulative orders per state
WITH monthly_state_orders AS (
    SELECT
        c.customer_state,
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        COUNT(DISTINCT o.order_id) AS order_count
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_state, SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)
)
SELECT
    customer_state,
    year_month,
    order_count,
    SUM(order_count) OVER (
        PARTITION BY customer_state
        ORDER BY year_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_orders
FROM monthly_state_orders
WHERE customer_state IN ('SP', 'RJ', 'MG')
ORDER BY customer_state, year_month;


-- ============================================================
-- 11.7  MOVING AVERAGES — Smoothing trends
-- ============================================================

-- 3-month moving average of revenue
WITH monthly_revenue AS (
    SELECT
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        SUM(oi.price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)
)
SELECT
    year_month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY year_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_revenue
ORDER BY year_month;


-- ============================================================
-- 11.8  PERCENT OF TOTAL — Using SUM OVER ()
-- ============================================================

-- Each state's share of total revenue
WITH state_revenue AS (
    SELECT
        c.customer_state,
        SUM(oi.price) AS total_revenue
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.customer_state
)
SELECT
    customer_state,
    total_revenue,
    SUM(total_revenue) OVER () AS grand_total,
    ROUND(100.0 * total_revenue / SUM(total_revenue) OVER (), 2) AS pct_of_total
FROM state_revenue
ORDER BY total_revenue DESC;


-- Each seller's share within their state
WITH seller_rev AS (
    SELECT
        s.seller_state,
        s.seller_id,
        SUM(oi.price) AS revenue
    FROM order_items oi
    JOIN sellers s ON oi.seller_id = s.seller_id
    GROUP BY s.seller_state, s.seller_id
)
SELECT
    seller_state,
    seller_id,
    revenue,
    SUM(revenue) OVER (PARTITION BY seller_state) AS state_total,
    ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY seller_state), 2) AS pct_of_state
FROM seller_rev
WHERE seller_state IN ('SP', 'RJ')
ORDER BY seller_state, revenue DESC
LIMIT 20;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Rank sellers by the number of distinct orders
--             they've fulfilled. Show top 10.

-- Exercise 2: For each month, calculate the month-over-month
--             change in the number of orders (not revenue).

-- Exercise 3: Create a cumulative sum of payment values by
--             payment type over months.

-- Exercise 4: Divide all sellers into 5 equal groups (NTILE)
--             based on their total revenue. Show the average
--             revenue per group.


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
WITH seller_orders AS (
    SELECT
        seller_id,
        COUNT(DISTINCT order_id) AS num_orders
    FROM order_items
    GROUP BY seller_id
)
SELECT
    RANK() OVER (ORDER BY num_orders DESC) AS seller_rank,
    seller_id,
    num_orders
FROM seller_orders
LIMIT 10;

-- Exercise 2
WITH monthly_orders AS (
    SELECT
        SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)
)
SELECT
    year_month,
    order_count,
    LAG(order_count) OVER (ORDER BY year_month) AS prev_month,
    order_count - LAG(order_count) OVER (ORDER BY year_month) AS change
FROM monthly_orders
ORDER BY year_month;

-- Exercise 3
WITH monthly_payments AS (
    SELECT
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        p.payment_type,
        SUM(p.payment_value) AS monthly_value
    FROM order_payments p
    JOIN orders o ON p.order_id = o.order_id
    GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7), p.payment_type
)
SELECT
    year_month,
    payment_type,
    monthly_value,
    SUM(monthly_value) OVER (
        PARTITION BY payment_type
        ORDER BY year_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_value
FROM monthly_payments
WHERE payment_type IN ('credit_card', 'boleto')
ORDER BY payment_type, year_month;

-- Exercise 4
WITH seller_rev AS (
    SELECT
        seller_id,
        SUM(price) AS total_revenue
    FROM order_items
    GROUP BY seller_id
),
tiled AS (
    SELECT
        NTILE(5) OVER (ORDER BY total_revenue) AS quintile,
        total_revenue
    FROM seller_rev
)
SELECT
    quintile,
    COUNT(*)          AS num_sellers,
    MIN(total_revenue) AS min_rev,
    AVG(total_revenue) AS avg_rev,
    MAX(total_revenue) AS max_rev
FROM tiled
GROUP BY quintile
ORDER BY quintile;