SQL Window Functions Made Simple

When you need to calculate averages, ranks, or totals without collapsing rows, SQL’s window functions are your best friend. They let you perform calculations across related rows while keeping every original record visible - perfect for analytics, leaderboards, and comparisons.

Exploring window functions using a sample Safari Trips database.

Safari Trips Database Schema
Working with two tables: drivers and trips.

drivers

driver_id	driver_name	city
1	James Kariuki	Nairobi
2	Mary Achieng	Kisumu
3	Peter Otieno	Mombasa
4	Sarah Njeri	Eldoret

trips

trip_id	driver_id	rider_id	trip_date	fare	rider_rating
101	1	11	2026-08-01	2000	4.8
102	2	12	2026-08-02	1500	4.5
103	3	13	2026-08-03	2500	4.9
104	4	14	2026-08-04	1800	4.6

Definition:
A window function performs a calculation across a set of related rows - called a window - and adds the result as a new column.

Unlike GROUP BY, which collapses rows into groups, window functions preserve all rows and simply add computed values alongside them.

Example 1: Average Fare per Driver
GROUP BY way:

SELECT driver_id, AVG(fare) AS avg_fare
FROM safari.trips
GROUP BY driver_id;

Output:

driver_id	avg_fare
1	2000
2	1500
3	2500
4	1800

Window function way:

SELECT driver_id, fare, AVG(fare) OVER() AS avg_fare
FROM safari.trips;

Output:

driver_id	fare	avg_fare
1	2000	1950
2	1500	1950
3	2500	1950
4	1800	1950

Notice: every row survives, with the overall average added alongside.

Section A - Ranking Functions
A1. Driver Revenue Ranking

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 dr
JOIN safari.drivers d ON d.driver_id = dr.driver_id;

Output:

driver_name	total_revenue	revenue_rank
Peter Otieno	2500	1
James Kariuki	2000	2
Sarah Njeri	1800	3
Mary Achieng	1500	4

A2. Each Driver’s Best Trip

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, rt.trip_id, rt.fare, rt.trip_date
FROM ranked_trips rt
JOIN safari.drivers d ON d.driver_id = rt.driver_id
WHERE rt.fare_rank = 1
ORDER BY rt.fare DESC;

Output:

driver_name	trip_id	fare	trip_date
Peter Otieno	103	2500	2026-08-03
James Kariuki	101	2000	2026-08-01
Sarah Njeri	104	1800	2026-08-04
Mary Achieng	102	1500	2026-08-02

Section B - Totals and Aggregates
B1. Every Trip with Rider’s Total Spend

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

Output:

rider_id	trip_id	fare	rider_total_spend
11	101	2000	2000
12	102	1500	1500
13	103	2500	2500
14	104	1800	1800

Section C - Comparing Rows
C1. Fare Compared to Previous Trip

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

Output (Driver 1):

driver_name	trip_id	trip_date	fare	prev_fare	fare_change
James Kariuki	101	2026-08-01	2000	NULL	NULL
James Kariuki	105	2026-08-10	2200	2000	200

LAG() looks back at the previous row, making it easy to track changes over time.

Key Takeaways
Window functions let you calculate across rows while keeping all data visible.

Use them for ranking, running totals, comparisons, and analytics.

Common functions:

ROW_NUMBER() → Sequential numbering

RANK() / DENSE_RANK() → Leaderboards

SUM() / AVG() → Totals and averages

LAG() / LEAD() → Previous or next row comparisons

Conclusion
Window functions are the backbone of analytical SQL. They bridge the gap between raw data and insights - whether you’re ranking drivers, comparing fares, or tracking trends.