Introduction
SQL window functions perform calculations across a set of rows related to the current row, without collapsing results into a single aggregated row. They are defined using the OVER() clause, which can include PARTITION BY (to group rows) and ORDER BY (to define row order within each group).
Basic Syntax:
SELECT column_name1,
window_function(column_name2)
OVER ([PARTITION BY column_name3] [ORDER BY column_name4])
AS new_column
FROM table_name;
Unlike standard aggregates, window functions retain individual rows while adding calculated values such as rankings, running totals, or moving averages.
select
driver_id,
sum(fare) as total_revenue,
rank() over(order by sum(fare) desc) as revenue_rank
from safari.trips
group by driver_id;
We have 3 categories of window functions in SQL.
Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
Rank Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(),NTILE()
Value Window Functions: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()
The main focus will be on the last category - Value Window Functions, and how we incorporate them to answer key business questions.
Main Purpose of Value Functions
They enable us access a value from another row in order to do a comparison, without having to join tables or do self joins.
i.e compare current row values with values of the previous, next, first and last rows.
1. LAG()
Used to access a value from the previous row within a window/ checks the previous value
lag(fare) over(partition by driver_id order by trip_date) as previous_fare
returns the fare from the previous trip the driver took.
2. LEAD()
Used to access a value from the next row within a window/ checks the next value
lead(sales, 2, 0) over(order by month)
returns the sales value 2 months from the current row value.
sales -> expression
2 -> offset; get the value of sales two months ahead
0 -> default; if there's no corresponding value, return 0 instead of null
If there is no previous/next value we get a NULL for that row.
3. FIRST_VALUE()
Returns a value from the first row within a window
first_value(fare) over(partition by driver_id order by trip_date) as first_fare
returns the first ever fare recorded by the driver
4. LAST_VALUE()
Returns a value from the last row within a window
last_value(fare) over(partition by driver_id order by trip_date
rows between current row and unbounded following) as last_fare
returns the last fare from the driver
Syntax
- All the 4 functions accept all data types i.e integer, text, float etc
- Partition clause is optional
Frame clause is not allowed for LAG() & LEAD(), optional for FIRST_VALUE() and required for LAST_VALUE().
Frame clause is especially important in LAST_VALUE() to get the correct results.Default frame in FIRST_VALUE & LAST_VALUE is "
range between unbounded preceding and current row" which works for FIRST_VALUE but not LAST_VALUE, hence the need to define its frame.
Here's how Value Functions help answer real business questions
Time Series Analysis
- Analyze data to understand patterns, trends and behavior over time. e.g YoY, MoM sales performance.
- The value functions will help analyze overall growth/decline of the business performance over time.
Sample business question:
How is revenue changing month by month? What are our best and worst months?
with monthly_revenue as (
select
travel_month,
sum(total_fare) as total_revenue
from safari_connect.v_clean_trips
group by 1)
select
travel_month,
total_revenue,
lag(total_revenue) over (order by travel_month) as prev_month_revenue,
total_revenue - lag(total_revenue) over (order by travel_month) as mom_rev_change,
round(100.0 * (total_revenue - lag(total_revenue) over (order by travel_month))
/ lag(total_revenue) over (order by travel_month), 2) as mom_pct_change
from monthly_revenue
order by travel_month;
The result >>>
The best month, using the month-on-month percentage change, is September 2024 and the worst month is January 2025.
Customer retention analysis
- Measure a customer's behaviour & loyalty to help businesses build strong relationships with customers
- Here we can understand customer's behaviour and loyalty by knowing how long it takes for each customer to buy/place their next order using lead(), and how it changes from one period to another.
Employee / Workforce Analysis
We could analyze metrics such as;
Employee salary changes over time, using
LAG()- compare an employee's current salary with previous salary.Salary at next review, using
LEAD()- compare current salary with employee's upcoming salary.
This would help with staff costs planning and budgeting.Performance changes, using
FIRST_VALUE()- compare an employee's current rating/KPI/sales with their starting value.
Conclusion
Value window functions are more than just SQL techniques for moving between rows, they provide a way to gain insights on change and performance within data.
Once you understand how to look back, look ahead and compare values within a window, you can use these functions to answer business questions that go beyond "What happened?" to "Where were we, where are we going, where did we start and where did we end?"
Top comments (1)
Well put.
Values shouldn't be arbitrary figures but tell a context based-story.