SQL window functions

RANK and ROW_NUMBER

ROW_NUMBER gives every row a unique place, which is what you want when you must keep exactly one current salary. RANK gives ties the same place and leaves a gap. Top customer per country uses RANK partitioned by country.

The frame of a running total

SUM(amount) OVER (ORDER BY paid_at) uses the default frame from the start of the partition through the current row. The order inside OVER is the accumulation order. Repeat that order in the query's ORDER BY if the reader should see the total grow downward.

Example

SELECT paid_at,
       amount_cents,
       SUM(amount_cents) OVER (ORDER BY paid_at, id) AS running_total_cents
FROM payments
WHERE status = 'succeeded';

The filter happens before the window, so refunds never enter the running total.

Practice

• HardHR
  Current salary for each employee

  Return one current salary row per employee.

• HardE-commerce
  Rank products by revenue

  Rank products by recognized revenue.

• MediumE-commerce
  Top customer in each country

  Find the customer with the highest recognized spend in each country.

• HardE-commerce
  Running total of cash collected

  Build a running total of succeeded payments in chronological order.

• HardHR
  Rank employees by current salary

  Rank every employee by their current annual salary.

Common mistakes

• Putting a window alias in WHERE of the same SELECT. Wrap it and filter outside.
• Ranking every historical salary instead of the current one.
• Using GROUP BY when you still need each payment in the output.
• Forgetting PARTITION BY and ranking the whole company as one group.

FAQ

Why can't I filter rn = 1 in the same SELECT?

WHERE is evaluated before window functions. The alias does not exist yet. Select the window in a subquery, then filter.

Do window functions work in MySQL and PostgreSQL?

Yes. RANK, ROW_NUMBER, and SUM() OVER are shared. Frame syntax has small differences, but the patterns in these exercises carry over.

Related practice

• SQL practice
• Beginner SQL practice
• SQL JOINs
• GROUP BY