Sam Guantai

Posted on Oct 1

SQL Window Functions Explained

#sql #database #tutorial #beginners

When I first learned SQL, GROUP BY felt like all I needed. Then I hit a question it couldn't answer: "Show me every trip, and next to it, that rider's total spend."

GROUP BY collapses rows. I wanted to keep every row and still see an aggregate beside it. That's what window functions are for.

In this post we'll use a small taxi company database (a safari schema with trips and drivers tables) to walk through the basics.

What is a window function?

A window function performs a calculation across a set of rows related to the current row without collapsing them into one.

The general shape is:

function_name(...) OVER (
    PARTITION BY column_a
    ORDER BY column_b
)

OVER() is what makes it a window function.
PARTITION BY splits rows into groups (windows). It's like GROUP BY, but rows aren't merged.
ORDER BY sets the order of rows inside each window, which matters for ranking and for "previous/next row" logic.

The simplest example is a bare counter:

SELECT trip_id, fare,
       ROW_NUMBER() OVER () AS fare_position
FROM safari.trips;

Every row gets 1, 2, 3, 4... and nothing is collapsed.

The three ranking functions

These three look similar but behave differently when there are ties.

SELECT
    trip_id,
    rider_rating,
    ROW_NUMBER() OVER (ORDER BY rider_rating DESC) AS row_num,
    RANK()       OVER (ORDER BY rider_rating DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY rider_rating DESC) AS dense_rank_num
FROM safari.trips
WHERE driver_id = 2
ORDER BY rider_rating DESC;

Function	Behavior on ties	Example output
`ROW_NUMBER()`	Never repeats; always counts up by 1	1, 2, 3, 4
`RANK()`	Tied rows share a rank, then it skips numbers	1, 2, 2, 4
`DENSE_RANK()`	Tied rows share a rank, no gaps	1, 2, 2, 3

When to use which:

ROW_NUMBER() when you need a unique position for every row (for example, picking exactly one row per group).
RANK() for leaderboards, where skipping a position after a tie feels natural (two silver medals, no bronze).
DENSE_RANK() when you want consecutive rank numbers regardless of ties.

Note that with ROW_NUMBER(), tied rows get an arbitrary order unless you add a tiebreaker column to ORDER BY.

Example 1: Rank all drivers by revenue

Rank all drivers by total revenue, highest first.

Window functions run after GROUP BY. So I aggregate first in a CTE, then rank the result.

WITH driver_revenue AS (
    SELECT driver_id, SUM(fare) AS total_revenue
    FROM safari.trips
    GROUP BY driver_id
)
SELECT
    dr.driver_id,
    d.driver_name,
    dr.total_revenue,
    RANK() OVER (ORDER BY dr.total_revenue DESC) AS revenue_rank
FROM driver_revenue dr
JOIN safari.drivers d ON d.driver_id = dr.driver_id;

No PARTITION BY here, because we want one ranking across everyone.

Example 2: Aggregates without losing rows

Show every trip with the rider's total spend alongside it.

A plain GROUP BY would give one row per rider. With a window, every trip stays:

SELECT
    rider_id,
    trip_id,
    fare,
    SUM(fare) OVER (PARTITION BY rider_id) AS rider_total_spend
FROM safari.trips;

Each row now shows its own fare and the rider's total. From here it's easy to calculate things like "what percentage of this rider's spend was this trip?" (fare / SUM(fare) OVER (...)).

Example 3: Comparing to the previous row with LAG

For driver 8, show each trip's fare, the previous trip's fare, and the change.

LAG() looks backward a row within the window. (LEAD() looks forward.)

SELECT
    t.driver_id,
    d.driver_name,
    t.trip_date,
    t.fare,
    LAG(t.fare) OVER (PARTITION BY t.driver_id ORDER BY t.trip_date) AS previous_fare,
    t.fare - LAG(t.fare) OVER (PARTITION BY t.driver_id ORDER BY t.trip_date) AS fare_change
FROM safari.trips t
JOIN safari.drivers d ON d.driver_id = t.driver_id
WHERE t.driver_id = 8
ORDER BY t.trip_date;

What happens on the first trip? There's no previous row, so LAG() returns NULL, and fare - NULL is also NULL as there's no change to report.

Example 4: Top row per group

For every driver, find their single highest-fare trip.

This is a very common problem, and probably the most useful pattern among these examples. Number the rows within each driver, then keep only number 1.

WITH ranked_trips AS (
    SELECT
        driver_id,
        trip_id,
        fare,
        trip_date,
        ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY fare DESC) AS fare_rank
    FROM safari.trips
)
SELECT
    d.driver_name,
    t.trip_id,
    t.fare,
    t.trip_date
FROM ranked_trips t
JOIN safari.drivers d ON d.driver_id = t.driver_id
WHERE t.fare_rank = 1
ORDER BY t.fare DESC;

Why the CTE? You can't filter on a window function in WHERE, because WHERE is evaluated before window functions run. Wrapping it in a CTE (or subquery) lets you filter on the computed column afterward.

Also note that we use ROW_NUMBER(). If a driver has two trips tied for the highest fare, you get exactly one. Swap in RANK() and you'd get both. Pick based on what the question means.

Summary

OVER() turns a function into a window function.
PARTITION BY defines the groups; ORDER BY defines the order within them.
Window functions keep your rows; GROUP BY collapses them.
ROW_NUMBER = unique, RANK = ties with gaps, DENSE_RANK = ties without gaps.
LAG/LEAD compare a row to its neighbors; the edge rows get NULL.
To filter on a window result, wrap the query in a CTE or subquery.

Once these click, a lot of problems that used to need self-joins or messy subqueries become a few readable lines.

DEV Community