Window Functions

Introduction

Window functions compute a value across a set of rows related to the current row, without collapsing them. They power running totals, rankings, moving averages and 'top-N-per-group'.

Understanding the topic

Core concepts to understand:

• OVER (PARTITION BY ... ORDER BY ...) defines the window.
• Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG.
• Unlike GROUP BY, rows are kept.
• Massively simplifies analytics queries.
• All major DBs support them (Postgres, MySQL 8+, SQL Server, Oracle).

Syntax reference

Visual workflow / architecture:

SELECT user_id, created_at, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)
         AS running_total,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC)
         AS rank_in_user
FROM   orders;

Real-world use

Stripe's revenue charts, e-commerce 'top product per category', sports leaderboards and time-series moving averages all use window functions.

Best practices

• Use windows over self-joins for top-N-per-group.
• Match window ORDER BY to an index when possible.
• Keep PARTITION cardinality reasonable.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Difference between GROUP BY and OVER().
• Q2. RANK vs DENSE_RANK vs ROW_NUMBER.
• Q3. Example using LAG.
• Q4. Top-N-per-group with windows.
• Q5. Window frame clause — what is it?