11-window-functions.md

Chapter 11: Window Functions

Window functions are among the most powerful tools in a data analyst's arsenal. They allow you to perform calculations across a set of rows related to the current row, without the need for a GROUP BY clause.

11.1 What are Window Functions?

Unlike aggregate functions, window functions do not collapse the result set. They return a value for every row in the output.

Syntax:

function() OVER (
    PARTITION BY ...   -- Optional: groups rows
    ORDER BY ...       -- Defines the sort order within the window
    ROWS BETWEEN ...   -- Optional: further restricts the "frame"
)

11.2 ROW_NUMBER, RANK, and DENSE_RANK

These functions are used for numbering and ranking rows within a partition or the entire dataset.

• ROW_NUMBER(): Assigns a unique, sequential number to each row.
• RANK(): Assigns the same rank to ties, skipping subsequent ranks.
• DENSE_RANK(): Assigns the same rank to ties, without skipping ranks.

-- Top 3 sellers per state
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 1, 2
),
ranked AS (
    SELECT
        *,
        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;

11.3 NTILE for Bucketing

NTILE(n) divides the rows into n roughly equal groups or "tiles."

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

11.4 LAG and LEAD

These functions allow you to access data from previous (LAG) or following (LEAD) rows. This is essential for calculating month-over-month growth or time-series differences.

-- Month-over-month revenue comparison
WITH monthly AS (
    SELECT
        SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS ym,
        SUM(price) AS revenue
    FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY 1
)
SELECT
    ym,
    revenue,
    LAG(revenue) OVER (ORDER BY ym) AS prev_rev,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY ym)) / LAG(revenue) OVER (ORDER BY ym), 1) AS growth_pct
FROM monthly;

11.5 Cumulative Sums and Moving Averages

By defining a "window frame," you can calculate running totals and moving averages to smooth out trends.

-- 3-month moving average of revenue
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;

---

Exercises

1. Rank sellers by the number of distinct orders they've fulfilled. Show the top 10.
2. For each month, calculate the month-over-month change in the number of orders.
3. Create a cumulative sum of payment values by payment type over months.
4. Divide all sellers into 5 equal groups (NTILE) based on their total revenue. Show the average revenue per group.

Solutions

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

-- Exercise 2
WITH m_ord AS (SELECT SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS ym, COUNT(*) AS c FROM orders GROUP BY 1) SELECT ym, c, c - LAG(c) OVER (ORDER BY ym) FROM m_ord;

-- Exercise 3
WITH m_pay AS (SELECT SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS ym, p.payment_type, SUM(p.payment_value) AS v FROM order_payments p JOIN orders o ON p.order_id = o.order_id GROUP BY 1, 2) SELECT ym, payment_type, SUM(v) OVER (PARTITION BY payment_type ORDER BY ym ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM m_pay;

-- Exercise 4
WITH s_rev AS (SELECT seller_id, SUM(price) AS r FROM order_items GROUP BY 1), tiled AS (SELECT NTILE(5) OVER (ORDER BY r) AS q, r FROM s_rev) SELECT q, AVG(r) FROM tiled GROUP BY 1;