SQL Window Functions 101

Introduction.

Window functions in SQL are powerful tools used to perform calculations across a specific "window" of rows related to the current row. Unlike aggregate functions (like SUM(), AVG(), COUNT()), which collapse multiple rows into a single result, window functions retain individual rows while adding calculated values.

They are commonly used for tasks like aggregates, rankings and running totals. The OVER clause defines the “window” of rows for the calculation.

It can:

• PARTITION BY: It divides the data into groups using PARTITION BY.
• ORDER BY: It specifies the order of rows within each group using ORDER BY. With this, functions such as SUM(), AVG(), ROW_NUMBER(), RANK() and DENSE_RANK() can be applied in a controlled way.

Types of Window Functions

SQL window functions are mainly of two types:

• Aggregate window functions
• Ranking window functions.

1. Ranking window functions

These functions provide rankings of rows within a partition based on specific criteria.
Common ranking functions include:

• ROW_NUMBER() Assigns a unique sequential number to each row.

select 
    t.driver_id, 
    t.trip_id,
    t.trip_date, 
    row_number() over(partition by driver_id) as fare_rank
from safari.trips t;

• RANK()

Assigns a rank, with ties receiving the same rank and gaps appearing.

select
      d.driver_id,
      d.driver_name,
      rank() over(partition by driver__id) as driver_rank
from safari.drivers d;

• DENSE_RANK()

Similar to RANK(), but without gaps in ranking.

select
      name, 
      department, 
      salary,
      dense_rank() over (partition by department order by salary desc) as emp_dense_rank
from employee;

• NTILE(n)

Distributes rows into n buckets.

select
      t.rider_id,
      sum(t.fare) as total_spend
      ntile(4) over (order by total_spend desc) as quartile
from safari.trips t
group by
      t.rider_id;

2. Aggregate Window Functions

Aggregate window functions calculate aggregates over a window of rows while retaining individual rows.
Common aggregate functions include:

• SUM()

Running total within a partition.

select 
      t.rider_id,
      t.trip_id, 
      t.fare,
      sum(fare) over(partition by rider_id) as rider_spend
from safari.trips t;

• AVG()

Rolling average.

select
    t.rider_id,
    t.trip_id,
    t.fare,
    avg(t.fare) over(partition by t.rider_id) as rider_avg_fare
from safari.trips t ;

• COUNT()

Count of rows within a partition.

select 
    t.trip_id,
    t.fare,
    count(*) over() as driv_tt_trip_count
from safari.trips t 
where t.driver_id = 2
order by t.trip_id;

3. Value-Based Functions

These functions return values from other rows in the window.

• LAG()

Retrieves a value from a previous row.

select
    t.trip_date,
    t.fare,
    lag(t.fare) over(partition by t.driver_id order by t.trip_date ) as prev_fare
from safari.trips t
order by t.trip_date asc;

• LEAD()

Retrieves a value from a subsequent row.

select 
    t.trip_id,
    t.trip_date,
    t.rider_rating,
    lead(t.rider_rating) over(partition by t.rider_id order by t.trip_date) as next_trip
from safari.trips t 
where t.rider_id = 2;

• FIRST_VALUE()

Returns the first value in the window.

• LAST_VALUE()

Returns the last value in the window

select 
    t.driver_id,
    t.trip_id,
    t.trip_date,
    t.fare,
    first_value(t.fare) over(partition by t.driver_id order by t.trip_date) as first_fare,
    last_value(t.fare) over(partition by t.trip_id order by t.trip_date ) as last_fare
from safari.trips t
where t.driver_id in (2,8);

Conclusion

SQL window functions enhance analytical capabilities without reducing row count. They are commonly used in reporting, trend analysis, and ranking operations.