SQL Window Functions: Rank, Compare, and Analyze Without Losing Rows

A practical PostgreSQL tutorial using a safari trips dataset.

A GROUP BY query can tell us how much a rider spent. But the moment we group the data, the individual trips disappear. What if we need both on the same row?

This is the problem window functions solve. A window function performs a calculation across rows related to the current row, but unlike a regular aggregate, it does not group those rows into one output row. Each row keeps its identity while gaining information from the rows around it.

Before we start writing project queries, let us first build a clear picture of the window-function categories, the syntax they share, and the question each category answers. Once that foundation is in place, the safari trips examples will make much more sense.

The project dataset

The project uses PostgreSQL and a safari schema with three tables:

• drivers stores driver names, car models, join dates, and status.
• riders stores rider names, cities, and membership tiers.
• trips stores trip dates, routes, distances, fares, ratings, and payment methods.

The supplied dataset contains 10 drivers, 12 riders, and 40 trips. We will use these rows to rank drivers, place totals beside trips, compare fares, calculate a rolling average, and divide riders into spend groups.

First, what makes a function a window function?

Consider a normal aggregate:

SELECT
    rider_id,
    SUM(fare) AS total_spend
FROM safari.trips
GROUP BY rider_id;

This returns one row per rider. That is useful when we only need the summary, but the individual trips are no longer part of the result.

Now add an OVER clause:

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

The calculation still uses SUM(fare), but OVER turns it into a window calculation. Instead of collapsing the trips, SQL returns every trip and places the rider's total beside it.

That difference is the foundation for everything that follows.

The anatomy of a window expression

A window expression generally follows this shape:

window_function(expression) OVER (
    PARTITION BY grouping_column
    ORDER BY ordering_column
    ROWS BETWEEN frame_start AND frame_end
)

Not every function needs every clause. Each part answers a separate question:

Part	Question it answers
Window function	What calculation should SQL perform?
OVER	Should this calculation operate as a window function?
PARTITION BY	Which rows belong in the same group for this calculation?
Window ORDER BY	In what sequence should SQL evaluate the rows?
Window frame	Which rows around the current row should the calculation include?

OVER

OVER marks the calculation as a window function. Without it, SUM(), COUNT(), and AVG() are regular aggregate functions.

PARTITION BY

PARTITION BY divides the result into independent windows. A calculation partitioned by rider_id starts again for each rider. A ranking partitioned by city starts again for each city.

If PARTITION BY is omitted, the entire result is treated as one window.

Window ORDER BY

The ORDER BY inside OVER defines the sequence used by the calculation. Ranking functions need it to determine position. LAG() and LEAD() need it to know which row is previous or next. An aggregate can use it to build a running calculation.

This is different from the final ORDER BY, which only controls how the returned rows are displayed.

The window frame

A frame narrows an ordered window to rows around the current row. For example:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This frame includes the current row and the two rows before it. In our project, it creates a three-trip rolling average.

The main categories of window functions

1. Aggregate window functions

Regular aggregate functions such as SUM(), COUNT(), and AVG() can also be used as window functions.

Without a window ORDER BY, an aggregate returns the value for the full partition:

SUM(fare) OVER (PARTITION BY rider_id)

Every trip for the same rider receives that rider's total spend.

When an ORDER BY is added, the calculation follows a sequence. A frame can then control whether the function uses all earlier rows or only a selected number of nearby rows.

Aggregate window functions answer questions such as:

• What is the rider's total spend beside each trip?
• What is the average fare across a driver's trips?
• What is the rolling average of the current fare and the previous two fares?

2. Ranking window functions

Ranking functions assign positions to ordered rows. The project uses four of them:

Function	Purpose
ROW_NUMBER()	Gives every row a unique sequential number.
RANK()	Gives ties the same rank and leaves gaps after the tied rows.
DENSE_RANK()	Gives ties the same rank without leaving gaps.
NTILE(n)	Divides ordered rows into n approximately equal buckets.

For example, if three rows tie at position 3:

• ROW_NUMBER() still gives them different numbers.
• RANK() gives all three rank 3, then the next row jumps to rank 6.
• DENSE_RANK() gives all three rank 3, then the next row receives rank

The function you choose depends on the question. ROW_NUMBER() is useful when exactly one row must be selected from each group. RANK() works for leaderboards where gaps after ties are meaningful. DENSE_RANK() keeps tied positions fair without creating gaps. NTILE() creates ordered groups such as quartiles.

3. Offset or row-comparison functions

LAG() and LEAD() pull a value from another row in the ordered window:

• LAG() looks backward to a previous row.
• LEAD() looks forward to a following row.

These functions answer questions such as:

• What was the previous trip's fare?
• How much did the fare change from the previous trip?
• What value comes next in the ordered sequence?

The first row returned by LAG() has no previous row, so the result is NULL. In the same way, LEAD() returns NULL at the end when no following row exists.

4. Framed window calculations

A frame is not a separate function, but it creates an important branch of window analysis. It defines how much of an ordered partition the current calculation can see.

The project uses:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Combined with AVG(fare), this calculates the average of the current trip and the previous two trips. The frame grows naturally at the start because fewer than three rows are available, then moves forward one row at a time.

With the categories clear, we can now apply them to the safari dataset.

Query 1: Compare the ranking functions

We will begin with the trips for driver 2 because repeated rider ratings make the effect of ties visible:

SELECT
    trip_id,
    rider_rating,
    ROW_NUMBER() OVER (ORDER BY rider_rating DESC) AS row_number_value,
    RANK() OVER (ORDER BY rider_rating DESC) AS rank_value,
    DENSE_RANK() OVER (ORDER BY rider_rating DESC) AS dense_rank_value
FROM safari.trips
WHERE driver_id = 2
ORDER BY rider_rating DESC, trip_id;

All three functions use the same ordered rows. The only difference is how they assign positions when ratings tie. This makes the query a useful reference before choosing a ranking function for a real task.

Query 2: Rank drivers by total revenue

A window function works on the rows produced by its query. Because the ranking needs one revenue row per driver, we first calculate driver revenue in a CTE:

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

The query has two clear stages:

1. Group the trips to produce one total-revenue row per driver.
2. Rank those driver rows from highest to lowest revenue.

There is no PARTITION BY because we want one leaderboard across all drivers.

Query 3: Keep every trip and add the rider's total spend

Now we return to the problem from the introduction. We need each trip's fare and the rider's total spend on the same row:

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

PARTITION BY rider_id places trips from the same rider in the same calculation window. There is no window ORDER BY because we want the total for the full rider partition, not a running total.

The WHERE clause limits the displayed example to rider 2. The window result still demonstrates the key benefit: the trip rows remain separate.

Query 4: Find each driver's highest-fare trip

This task needs exactly one trip for every driver. ROW_NUMBER() is a good fit because it gives each trip a unique position inside its driver partition:

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

PARTITION BY driver_id restarts the numbering for each driver. Ordering by fare DESC places the highest fare first. The extra date and trip ID columns provide a consistent order if fares tie.

The window value is calculated in the CTE and filtered in the outer query. That separation is important because the result of a window function is not filtered in the same query level where it is calculated.

Query 5: Rank riders inside their city

So far, the revenue leaderboard used one window across all drivers. This query shows how PARTITION BY changes the comparison group:

WITH rider_total_spend AS (
    SELECT
        rider_id,
        SUM(fare) AS total_spend
    FROM safari.trips
    GROUP BY rider_id
)
SELECT
    r.rider_name,
    r.city,
    rts.total_spend,
    RANK() OVER (
        PARTITION BY r.city
        ORDER BY rts.total_spend DESC
    ) AS city_rank
FROM rider_total_spend AS rts
JOIN safari.riders AS r
    ON r.rider_id = rts.rider_id
ORDER BY r.city, city_rank, r.rider_name;

The CTE first calculates one total-spend row for every rider who has trips. The outer query then ranks those riders, but the ranking restarts whenever the city changes.

This is the same ranking pattern as the driver leaderboard. The difference is the partition.

Query 6: Compare each fare with the previous trip

Ranking functions care about position. Offset functions use that position to retrieve another row's value.

For driver 8, we order trips by date and use LAG() to retrieve the previous fare:

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

The first trip has no earlier row, so previous_fare and fare_change are NULL. Every later row can subtract the previous fare from the current fare.

The window ORDER BY defines what “previous” means. Without a meaningful sequence, a previous-row comparison would not answer a clear question.

Query 7: Calculate a three-trip rolling average

The next query combines an aggregate window function with an ordered frame:

SELECT
    d.driver_name,
    t.trip_id,
    t.trip_date,
    t.fare,
    ROUND(
        AVG(t.fare) OVER (
            PARTITION BY t.driver_id
            ORDER BY t.trip_date, t.trip_id
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        1
    ) AS rolling_average_3
FROM safari.trips AS t
JOIN safari.drivers AS d
    ON d.driver_id = t.driver_id
WHERE t.driver_id = 2
ORDER BY t.trip_date, t.trip_id;

The partition keeps the calculation inside the selected driver's trip history. The window order arranges the trips. The frame then tells AVG() to use only the current trip and the previous two trips.

This is different from:

AVG(fare) OVER (PARTITION BY driver_id)

That version calculates one average over the driver's full partition. The frame turns it into a moving calculation.

Query 8: Divide riders into spend quartiles

Finally, NTILE(4) assigns riders to four ordered spend buckets:

WITH rider_spend AS (
    SELECT
        rider_id,
        SUM(fare) AS total_spend
    FROM safari.trips
    GROUP BY rider_id
)
SELECT
    r.rider_name,
    rs.total_spend,
    NTILE(4) OVER (ORDER BY rs.total_spend DESC) AS spend_quartile
FROM rider_spend AS rs
JOIN safari.riders AS r
    ON r.rider_id = rs.rider_id
ORDER BY spend_quartile, rs.total_spend DESC, r.rider_name;

The CTE produces one total-spend row per rider who has taken a trip. NTILE(4) then divides those ordered rows into four approximately equal groups, with the highest spenders first.

Because this is a small dataset, the buckets should not be interpreted as exact statistical percentiles. They are ordered groups based on the available rows.

A practical way to choose a window function

When a question looks like a window-function problem, I work through it in this order:

1. Define the output row. Should one row represent a trip, a rider, or a driver?
2. Identify any grouped step. If the value being ranked is a total, calculate that total first in a CTE.
3. Choose the category. Do you need an aggregate, ranking, offset, or bucket calculation?
4. Choose the partition. Should the calculation restart for every rider, driver, or city?
5. Choose the order. What determines rank, previous row, or movement through the data?
6. Choose the frame if needed. Should the function see the full partition or only nearby rows?
7. Filter in an outer query. If you need the top row from each partition, calculate the window value first.
8. Sort the final output. Add a final ORDER BY so the result is easy to inspect.

This approach keeps the SQL tied to the question. Instead of starting with a function name, start with the output grain, comparison group, and sequence.

Final thoughts

Window functions are easier to understand when they are treated as a family of tools rather than one feature.

• Aggregate window functions add totals, counts, and averages without collapsing rows.
• Ranking functions assign positions and handle ties in different ways.
• Offset functions compare the current row with earlier or later rows.
• Frames create running and rolling calculations inside an ordered partition.

In the safari project, those branches work together to preserve trip details, rank drivers and riders, compare fares, calculate rolling averages, and build spend quartiles.

The next time GROUP BY gives you the right calculation but removes the detail you still need, look at the OVER clause. The calculation may belong beside the original rows rather than in a collapsed summary.