Mastering SQL Window Functions

Why window functions matter

Window functions let you compute across a set of rows related to the current row without collapsing them into a group. That means running totals, rankings, and period-over-period math in a single, readable query.

Callout: If you find yourself self-joining a table to itself to compare rows, a window function is almost always the better tool.

A running total

select
  order_date,
  revenue,
  sum(revenue) over (
    order by order_date
    rows between unbounded preceding and current row
  ) as running_revenue
from daily_sales
order by order_date;

Ranking within groups

select
  category,
  product,
  revenue,
  row_number() over (partition by category order by revenue desc) as rank_in_category
from product_sales;

The mental model

Master the frame clause and the rest follows.