Window Functions & Analytics Patterns

In chapter 3, GROUP BY gave one row for each group. The other rows disappeared. A window function gives you the group answer and keeps every row.

First, the GROUP BY way. This query adds up the amounts for each customer:

SELECT customer_id,
       SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Now the same total as a window function. A window is a set of related rows. The function looks at the window and writes its answer on every row:

SELECT order_id,
       customer_id,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY order_id;

Orders 101 and 102 both show 65.00, the total for customer 1. Order 105 shows 15.50, because SUM skips its NULL amount.

The part after OVER says which rows belong together. PARTITION BY customer_id means one group for each customer.

What you will learn

In this chapter, you learn to:

1. Write the OVER clause, and number rows with ROW_NUMBER.
2. Find the share of the total, and keep the latest row for each key.
3. Rank rows when values tie, and take the top N for each group.
4. Find a median, compare a row with the row before it, and count days since a first order.
5. Build running totals, and avoid the default frame trap.
6. Filter a window result, and know why QUALIFY is not in PostgreSQL.

The examples use the shop from the earlier chapters and PostgreSQL. A CTE (WITH name AS (...)) is a named step from chapter 4. We use it often here.

Every window function has an OVER clause. It says which rows the function can see. It has up to three parts:

• PARTITION BY splits the rows into groups. Without it, all rows are one group.
• ORDER BY puts the rows of each group in order.
• A frame says which rows around the current row to use. Only some functions need it. The running totals section explains it.

ROW_NUMBER is the simplest window function. It numbers the rows 1, 2, 3 in each group.

ROW_NUMBER() has nothing inside the parentheses. It needs no column. Here it numbers each customer’s orders, oldest first:

SELECT order_id,
       customer_id,
       order_date,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY order_date
       ) AS n
FROM orders
ORDER BY customer_id, order_date;

The numbers start again at 1 for each customer. Customer 1 has orders 101 and 102, so they get 1 and 2. Customer 2 has one order, so it gets 1.

Chapter 3 found the share of orders that are paid. Now you find the share of revenue that each order gives.

OVER () with nothing inside means one group with all the rows. So SUM(amount) OVER () is the total of every amount. Divide each amount by it:

SELECT order_id,
       amount,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM orders
WHERE status = 'paid'
ORDER BY order_id;

WHERE runs first, so the total counts only paid orders. The four shares add up to 100.0.

Share for each customer

You can use a window function on a GROUP BY result.

Read SUM(SUM(amount)) OVER () from the inside out. GROUP BY runs first. Then the window adds up its rows:

SELECT customer_id,
       SUM(amount) AS revenue,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;

Customer 2 has no paid orders, so that customer is not in the result.

Some tables keep many rows for the same thing. A pipeline may save a new row each time an order changes. You often need only the latest row for each order.

This section uses one new table, order_status_history. It has one row each time an order changes status. First, number the rows of each order, newest first:

SELECT order_id,
       status,
       changed_at,
       ROW_NUMBER() OVER (
         PARTITION BY order_id
         ORDER BY changed_at DESC
       ) AS rn
FROM order_status_history
ORDER BY order_id, rn;

For order 101, the paid row is the newest, so it gets 1. Now keep only the rows with rn = 1.

You cannot filter on ROW_NUMBER() in WHERE, so put the numbering in a CTE first. Then filter in the outer query:

WITH numbered AS (
  SELECT order_id,
         status,
         changed_at,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY changed_at DESC
         ) AS rn
  FROM order_status_history
)
SELECT order_id, status, changed_at
FROM numbered
WHERE rn = 1
ORDER BY order_id;

The pattern has three steps:

1. Partition by the key.
2. Order so that the row you want comes first. Sort ascending to keep the oldest row.
3. Keep rn = 1 in the outer query. A later section explains why.

A tie is when two rows have the same value in the ORDER BY column. SQL has three ranking functions, and they treat ties in three ways.

To see it, count the units in each order. Orders 101 and 104 both have 3 units, so they tie. Rank the orders by units, most first:

WITH qty AS (
  SELECT order_id, SUM(quantity) AS units
  FROM order_items
  GROUP BY order_id
)
SELECT order_id,
       units,
       ROW_NUMBER() OVER (ORDER BY units DESC, order_id) AS row_num,
       RANK()       OVER (ORDER BY units DESC)           AS rnk,
       DENSE_RANK() OVER (ORDER BY units DESC)           AS dense_rnk
FROM qty
ORDER BY units DESC, order_id;

Look at the three columns:

• ROW_NUMBER gives 1, 2, 3, 4, 5. It never repeats a number. The extra order_id in its ORDER BY decides which tied row comes first.
• RANK gives 1, 1, 3, 4, 4. Tied rows share a number, and the next number skips ahead. Orders 101 and 104 share 1, so no row gets 2.
• DENSE_RANK gives 1, 1, 2, 3, 3. Tied rows share a number, and no number is skipped.

Use ROW_NUMBER when you need exactly one row for each number. Use RANK or DENSE_RANK when equal rows should share a place.

Sometimes you need the top N rows in each group. For example, the two biggest orders for each status. LIMIT cannot do this, because LIMIT cuts the whole result once.

Number the rows in each group, then keep the numbers up to N. This query keeps the top 2 orders for each status. It skips orders with no amount:

WITH ranked AS (
  SELECT status,
         order_id,
         amount,
         ROW_NUMBER() OVER (
           PARTITION BY status
           ORDER BY amount DESC, order_id
         ) AS rn
  FROM orders
  WHERE amount IS NOT NULL
)
SELECT status, order_id, amount, rn
FROM ranked
WHERE rn <= 2
ORDER BY status, rn;

The numbers start again for each status, so N applies inside each group. Use RANK instead of ROW_NUMBER if tied rows should all stay.

Chapter 3 found the median with PERCENTILE_CONT. MySQL and SQLite do not have it. There, you can use ROW_NUMBER.

The plan: sort the values, number them, and count them. The median is the middle row. First, number the four paid amounts and count them:

SELECT amount,
       ROW_NUMBER() OVER (ORDER BY amount) AS rn,
       COUNT(*)     OVER ()                AS n
FROM orders
WHERE status = 'paid'
ORDER BY amount;

With n = 4, the two middle rows are 2 and 3. The formulas (n + 1) / 2 and (n + 2) / 2 find them.

In PostgreSQL, 5 / 2 gives 2, because dividing two whole numbers drops the decimals. So the formulas give 2 and 3. With n = 5, both give 3, the one middle row.

Keep those rows and average them:

WITH numbered AS (
  SELECT amount,
         ROW_NUMBER() OVER (ORDER BY amount) AS rn,
         COUNT(*)     OVER ()                AS n
  FROM orders
  WHERE status = 'paid'
)
SELECT ROUND(AVG(amount), 2) AS median
FROM numbered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

Some questions compare a row with its neighbour. For example: how many days passed since the last order of this customer?

LAG gives the value from the row before. LEAD gives the value from the row after.

The rows must be in order, so LAG needs ORDER BY. Use PARTITION BY too, so each customer has their own rows. Subtract the dates to get the gap in days:

SELECT order_id,
       customer_id,
       order_date,
       LAG(order_date) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS previous_date,
       order_date - LAG(order_date) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS days_since
FROM orders
ORDER BY customer_id, order_date;

Order 102 is 7 days after order 101. Order 105 is 5 days after order 104. The first order of each customer has no row before it, so LAG gives NULL.

Both columns use the same OVER, so both read the same previous row.

LEAD looks forward

LEAD works the same way, but it reads the next row. This query shows the date of the next order:

SELECT order_id,
       customer_id,
       LEAD(order_date) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS next_date
FROM orders
ORDER BY order_id;

To use another value instead of NULL, write a third argument: LAG(order_date, 1, order_date). The 1 means one row back.

A running total adds each row to all the rows before it. Each row shows the total so far.

Add ORDER BY inside OVER, and SUM becomes a running total. We also write the frame out. The next part explains it:

SELECT order_id,
       order_date,
       amount,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status = 'paid'
ORDER BY order_date;

The frame

The last line of OVER is the frame. It says which rows around the current row to use.

UNBOUNDED PRECEDING means the first row of the group. CURRENT ROW means this row.

Change the start of the frame to 1 PRECEDING. Now each row adds only itself and the row before it:

SELECT order_id,
       amount,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS last_two
FROM orders
WHERE status = 'paid'
ORDER BY order_date;

Change SUM to AVG and you get a moving average. Use 6 PRECEDING for a 7-day average, if you have one row for each day.

A window with ORDER BY and no frame gets a default frame. It runs from the first row to the current row. It also includes rows that tie with the current row.

This causes two traps.

Trap 1: LAST_VALUE

FIRST_VALUE reads the first row of the frame. LAST_VALUE reads the last row. With the default frame, the last row is the current row.

Compare three columns on the paid orders:

SELECT order_id,
       customer_id,
       amount,
       FIRST_VALUE(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS first_amount,
       LAST_VALUE(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS last_wrong,
       LAST_VALUE(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_right
FROM orders
WHERE status = 'paid'
ORDER BY order_id;

On order 101, last_wrong shows 40.00. That is its own amount, not the last one. last_right shows 25.00, the amount of the last order of customer 1.

The fix is the frame UNBOUNDED FOLLOWING, which ends at the last row of the group.

Trap 2: ties in a running total

The default frame is called RANGE. RANGE counts every row that ties with the current row. ROWS counts one row at a time.

Orders 101, 102, 104 and 106 all have status paid, so they tie. Run a running total by status, with the default frame and with ROWS:

SELECT order_id,
       status,
       amount,
       SUM(amount) OVER (ORDER BY status) AS default_frame,
       SUM(amount) OVER (
         ORDER BY status, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS rows_frame
FROM orders
WHERE amount IS NOT NULL
ORDER BY status, order_id;

The four paid rows tie on status, so each of them sees all four paid rows. The total does not run.

ROWS counts one row at a time. Add order_id to the ORDER BY so the order is fixed.

A cohort is a group of customers who started in the same period. Retention asks how many of them came back. Here, the four customers who ordered in January are one cohort.

First, find the first order date of each customer. MIN as a window function puts it on every row. Subtract it to get the days since the first order:

SELECT customer_id,
       order_id,
       order_date,
       MIN(order_date) OVER (PARTITION BY customer_id)
         AS first_order_date,
       order_date - MIN(order_date) OVER (PARTITION BY customer_id)
         AS days_since_first
FROM orders
ORDER BY order_id;

Now count the customers who came back. A customer came back if they have an order 1 to 7 days after their first order. This query puts step 1 in a CTE, then counts:

WITH days AS (
  SELECT customer_id,
         order_date - MIN(order_date) OVER (PARTITION BY customer_id)
           AS days_since_first
  FROM orders
)
SELECT COUNT(DISTINCT customer_id) AS customers,
       COUNT(DISTINCT CASE WHEN days_since_first BETWEEN 1 AND 7
                           THEN customer_id END) AS came_back
FROM days;

You cannot use a window function in WHERE. Most people try it once. This query asks for the latest order of each customer:

SELECT customer_id, order_id
FROM orders
WHERE ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
      ) = 1;

SQL does not run the parts of a query in the order you write them. This table shows the order it uses:

WHERE is step 2. The window functions run in step 4. When WHERE runs, the window values do not exist yet.

A column name from SELECT, like rn, is also not ready in WHERE.

The fix is two steps. Compute the window in a CTE. Then filter in the outer query, where the column is ready:

WITH numbered AS (
  SELECT customer_id,
         order_id,
         order_date,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC
         ) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date
FROM numbered
WHERE rn = 1
ORDER BY customer_id;

Some databases add a clause named QUALIFY. It runs after the window functions, so it can filter on them in the same SELECT. You do not need a CTE.

Here is the latest status of each order:

-- Snowflake, BigQuery, Databricks. Not PostgreSQL.
SELECT order_id, status, changed_at
FROM order_status_history
QUALIFY ROW_NUMBER() OVER (
          PARTITION BY order_id
          ORDER BY changed_at DESC
        ) = 1
ORDER BY order_id;

QUALIFY is not part of the SQL standard. This table shows who has it:

The CTE version works in every database.

Each exercise opens in the SQL workspace with its own tables. The names differ a little from the shop: it uses buyers, orders and snapshots.

Before you write a query, say the window aloud. For example: “partition by buyer, order by time, newest first.”