04. SQL Window Functions

Window functions calculate across related rows without collapsing the result set.

Latest record

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_at DESC
           ) AS rn
    FROM customer_changes
)
SELECT * FROM ranked WHERE rn = 1;

Running total

SUM(revenue) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Common functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM, and AVG.

Window operations can require sorting and repartitioning, so large windows are performance-sensitive.

Next

Continue through 05. SQL in Databricks in sequence.