OVER and PARTITION BY: aggregates that keep the rows

Group totals without losing the rows

Each item in an order, with the order’s total and its share of it. With GROUP BY you would get the total but lose the items. A window function keeps both:

SELECT oi.order_id, p.name,
       oi.quantity * oi.unit_price AS line_total,
       sum(oi.quantity * oi.unit_price) OVER (PARTITION BY oi.order_id) AS order_total,
       round(100 * oi.quantity * oi.unit_price
             / sum(oi.quantity * oi.unit_price) OVER (PARTITION BY oi.order_id), 1) AS pct_of_order
FROM order_items oi JOIN products p ON p.id = oi.product_id
WHERE oi.order_id IN (101, 103)
ORDER BY oi.order_id, line_total DESC;

 order_id |        name         | line_total | order_total | pct_of_order
----------+---------------------+------------+-------------+--------------
      101 | Mechanical keyboard |    4500.00 |     5700.00 |         78.9
      101 | Wireless mouse      |    1200.00 |     5700.00 |         21.1
      103 | USB-C hub           |    2499.00 |     5498.00 |         45.5
      103 | Laptop stand        |    1899.00 |     5498.00 |         34.5
      103 | Wireless mouse      |    1100.00 |     5498.00 |         20.0

• OVER (...) turns sum from an aggregate into a window function: it is computed over a set of rows, the window, but each input row stays an output row.
• PARTITION BY oi.order_id makes each row’s window the rows of the same order.
• OVER () with nothing inside would make the window the whole result.

Running totals: add ORDER BY

ORDER BY inside OVER changes the window from “the whole partition” to “from the start up to this row”. A running total of revenue over time:

WITH totals AS (
  SELECT o.id, o.ordered_at, sum(oi.quantity * oi.unit_price) AS order_total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.ordered_at
)
SELECT id, ordered_at::date AS day, order_total,
       sum(order_total) OVER (ORDER BY ordered_at) AS running_total
FROM totals ORDER BY ordered_at;

 id  |    day     | order_total | running_total
-----+------------+-------------+---------------
 101 | 2025-03-02 |     5700.00 |       5700.00
 102 | 2025-03-05 |    37998.00 |      43698.00
 103 | 2025-04-11 |     5498.00 |      49196.00
 …
 112 | 2025-09-09 |    14500.00 |     143986.00

The last running total, 143,986, is the same total revenue the inflated-report investigation reconciled against.

Where window functions run

Window functions are computed after WHERE, GROUP BY and HAVING, just before the final ORDER BY. Two consequences:

• They see only the rows that survived filtering and grouping. The CTE above grouped items into orders first, then the window added up orders.
• You cannot filter on them in WHERE:

ERROR:  window functions are not allowed in WHERE

To keep, say, only rank 1, compute the window function in a subquery or CTE and filter outside it. The practice page does exactly that.

Check yourself

1. What is the difference between sum(x) with GROUP BY and sum(x) OVER (PARTITION BY ...)?

   Show answer

   GROUP BY returns one row per group. The window version keeps every input row and attaches its group’s sum to each.

   I could explain this without looking

2. What does adding ORDER BY inside OVER change?

   Show answer

   The window becomes cumulative: by default it covers rows from the start of the partition up to the current row (and rows tied with it), which gives running totals.

   I could explain this without looking

3. Why can’t you write WHERE rank() OVER (...) = 1?

   Show answer

   Window functions are computed after WHERE. Compute the rank in a subquery or CTE, then filter on it in the outer query.

   I could explain this without looking