Window Functions

OVER (PARTITION BY ... ORDER BY ...)

Window functions compute values across a set of rows related to the current row, without collapsing them like GROUP BY: rankings (rank, row_number, dense_rank), running totals (sum(...) OVER (ORDER BY ...)), moving averages, and comparisons with neighbours (lag, lead). PARTITION BY restarts the calculation per group; ORDER BY inside OVER defines order and, for aggregates, a running frame.

Powerful PostgreSQL features

Window functions compute across rows, JSONB stores flexible data, and upserts make writes idempotent.

Ranking orders and per-customer running totals, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Each order keeps its row. rank() orders all orders by value (two orders tie at rank 2 and rank 3 is skipped); the running total restarts per customer: Asha 1,259 then 2,758, Ravi 1,200 then 2,699.

SELECT o.id, c.name, o.ordered_on,
       sum(oi.qty * p.price) AS total,
       rank() OVER (ORDER BY sum(oi.qty * p.price) DESC) AS rank_by_value,
       sum(sum(oi.qty * p.price)) OVER (PARTITION BY c.name ORDER BY o.ordered_on) AS customer_running_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id, c.name
ORDER BY o.id;

Output:

 id | name  | ordered_on |  total  | rank_by_value | customer_running_total 
----+-------+------------+---------+---------------+------------------------
  1 | Asha  | 2026-09-01 | 1259.00 |             4 |                1259.00
  2 | Asha  | 2026-09-15 | 1499.00 |             2 |                2758.00
  3 | Ravi  | 2026-09-10 | 1200.00 |             5 |                1200.00
  4 | Meera | 2026-09-20 | 1798.00 |             1 |                1798.00
  5 | Ravi  | 2026-09-21 | 1499.00 |             2 |                2699.00
(5 rows)

Pick the right ranking function

row_number never ties, rank skips after ties, dense_rank does not skip; choose by what "top N" means for you.

त्वरित जाँच: How do window functions differ from GROUP BY?

• They require temporary tables
• They delete rows
• They only work on JSON
• They compute across rows while keeping every row in the result