Window Functions in SQL

• SQL window functions perform calculations across a set of table rows that are related to the current row - window functions allow you to perform advanced data analysis (like calculating running totals, rankings, or moving averages) on a specific group of related rows without losing the details of individual rows. Unlike regular aggregate functions (GROUP BY), window functions do not collapse your rows into a single output row; every individual row retains its separate identity while displaying the calculated value.

• A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

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

Window function syntax

The defining element of a window function is the OVER() clause.

PARTITION BY: Divides the rows into groups or "partitions" (similar to GROUP BY, but without collapsing rows). If omitted, the function treats the entire table as one single group.

ORDER BY: Dictates the sequence in which the rows are processed within each partition. Crucial for running totals, moving averages, and rankings.

ROWS/RANGE (Frame Clause): Further subsets the rows within the partition (e.g., "only the last 3 rows up to the current row").

Common types of Window Functions

1. Aggregate Window Functions
These perform standard math calculations but keep the row-level detail intact
SUM(), AVG(), COUNT(), MIN(), MAX()

• SUM()

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

• AVG()

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()

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;

2. Ranking Window Functions
Used to assign a sequential rank or number to rows based on a specific order.

• ROW_NUMBER(): Assigns a unique, sequential integer to every row starting at 1.

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 ranks, but skips numbers if there are duplicate values (e.g., 1, 2, 2, 4).

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

• DENSE_RANK(): Assigns ranks, but does not skip numbers for duplicates (e.g., 1, 2, 2, 3).

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

3. Value & Offset Functions
Used to grab values from other rows relative to the current row without doing math.

LAG(): Fetches data from a previous row (great for calculating month-over-month growth).

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(): Fetches data 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() / LAST_VALUE(): Grabs the very first or very last value in the partitioN