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.