Window basics

What you will be able to do

• Write a window function with OVER (PARTITION BY … ORDER BY …)
• Use ROW_NUMBER to pick top-N per group
• Contrast windows with GROUP BY

Why this matters for data work

GROUP BY collapses rows. Windows compute running metrics while keeping detail — top order per store, rank of customers, first event per id. Warehouse SQL lives here.

Concepts

• function() OVER (PARTITION BY … ORDER BY …)
• ROW_NUMBER() — unique rank per partition.
• RANK() / DENSE_RANK() — ties handled differently.
• PARTITION BY is the “group”; without it, the window is the whole result.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT
  store_id,
  order_id,
  line_total,
  row_number() OVER (PARTITION BY store_id ORDER BY line_total DESC) AS rn
FROM orders
WHERE status = 'completed'
ORDER BY store_id, rn
LIMIT 15;

WITH ranked AS (
  SELECT
    store_id,
    order_id,
    line_total,
    row_number() OVER (PARTITION BY store_id ORDER BY line_total DESC) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT * FROM ranked WHERE rn <= 2 ORDER BY store_id, rn;
SQL

What you should see

• Row numbers restarting per store.
• Top two completed lines per store from the CTE filter.

Common mistakes

• Filtering rn in WHERE on the same SELECT — need a subquery/CTE.
• ORDER BY inside OVER missing — ranks become arbitrary.
• Expecting GROUP BY collapse — windows keep grain.

Next

Window analytics