SQL Window Functions: The Tutorial Most Skip

TL;DR: Window functions don't collapse rows. They compute across them. That's the whole superpower.

The short version

Window functions don't collapse rows. They compute across them. That's the whole superpower.

This guide covers the mental model, the patterns that pay off, and the trade-offs that decide whether a technique fits your code.

Working example

Here's a minimal example you can run as-is. Drop it in a fresh file, run it, and trace through it once before reading the rest.

-- Top 5 customers by revenue, last 30 days
SELECT c.id, c.email, SUM(o.total_cents) / 100.0 AS revenue_usd
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
  AND o.status = 'paid'
GROUP BY c.id, c.email
ORDER BY revenue_usd DESC
LIMIT 5;

Why this pattern

The shape above shows up in real SQL codebases because it satisfies three constraints at once: it stays type-safe, it composes with the rest of the language's idioms, and it leaves a clear trail for the next developer (which, in six months, is you).

When you write the same pattern three times in a project, extract it. When you write it three times across projects, extract it into a shared library.

A common variant

The same idea adapted for a different shape. Notice how the structure stays the same — only the specifics change.

SELECT
  order_date,
  daily_total,
  SUM(daily_total) OVER (ORDER BY order_date) AS running_total
FROM (
  SELECT DATE(created_at) AS order_date, SUM(total_cents) AS daily_total
  FROM orders WHERE status = 'paid' GROUP BY 1
) d
ORDER BY order_date;

Trade-offs to watch

Every pattern has a failure mode. The most common one here is over-application: developers who learn a technique apply it everywhere, including places where simpler code would have been clearer.

Rule of thumb: if the abstraction takes more lines to describe than it saves, the abstraction is wrong.

A more involved example

Once the basic pattern is clear, here's how it composes with surrounding code. Read this one slowly.

WITH ranked AS (
  SELECT
    user_id,
    title,
    views,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY views DESC) AS rn
  FROM posts
)
SELECT user_id, title, views
FROM ranked
WHERE rn <= 3;

When to skip it

If the surrounding code is already simple, don't reach for SQL-specific cleverness. Boring code is a feature. Save the patterns for places where they actually pay off — usually at module boundaries, in shared libraries, or where the alternative would be 50 lines of repetition.

FAQ

Related reading

• SQL JOINs: Six Types, Five You Actually Use
• The N+1 Query Problem: Detection and Fixes
• CTEs vs Subqueries: When to Use Which
• Postgres JSONB vs JSON: When to Use Which
• settings.json: The Power-User Configuration