Window Functions

GROUP BY reduces many rows to one per group. A window function calculates across a set of related rows and keeps every row. It is what makes rankings, running totals and period-over-period comparisons possible in one query.

OVER

A window function is followed by OVER, which defines the rows it looks at. An empty OVER () means all rows.

SELECT name,
       category,
       price,
       ROUND(AVG(price) OVER (), 2) AS overall_average
FROM products;

Every product row is kept, and each shows the overall average beside it.

PARTITION BY

PARTITION BY splits the rows into groups, and the function is calculated separately in each.

SELECT name,
       category,
       price,
       ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_average,
       ROUND(price - AVG(price) OVER (PARTITION BY category), 2) AS difference
FROM products
ORDER BY category, price DESC;

Ranking

ORDER BY inside OVER sets the order used for ranking.

SELECT name,
       category,
       price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS row_number,
       RANK()       OVER (ORDER BY price DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

Function	With ties
ROW_NUMBER	Gives each row a different number
RANK	Gives ties the same number, then skips
DENSE_RANK	Gives ties the same number, with no gaps

Top N in each group

A window function cannot be used in WHERE, because it is calculated after filtering. Put it in a CTE and filter in the outer query. This returns the most expensive product in each category:

WITH ranked AS (
    SELECT name,
           category,
           price,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS position
    FROM products
)
SELECT category, name, price
FROM ranked
WHERE position = 1;

Running totals

An aggregate with ORDER BY in its window accumulates row by row.

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * p.price)      AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly;

Comparing with the previous row

LAG returns a value from an earlier row, and LEAD from a later one.

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * p.price)      AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

The first month has no previous row, so LAG returns NULL.

Moving averages

A frame limits the window to rows near the current one.

SELECT order_id,
       order_date,
       COUNT(*) OVER (
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rows_in_frame
FROM orders;

Replace COUNT(*) with AVG(some_value) to get a three-row moving average.

Share of total

SELECT category,
       SUM(price) AS total,
       ROUND(100.0 * SUM(price) / SUM(SUM(price)) OVER (), 1) AS percent_of_total
FROM products
GROUP BY category;

The inner SUM(price) is the group total. The outer SUM(...) OVER () adds those group totals together.

Practice

For each customer, number their orders by date so that their first order is 1, their second is 2, and so on.