Calculating Difference from Beginning/First Row in SQL
How to find the first or last value in your query result and use it in the current row?
This question commonly comes up with calculating growth rates, where you want to see the performance in different time frames, year-to-date and month-to-date. Other uses are:
- How much gap are we seeing from the current row to the last value in the query? Useful for sales projections and meeting quotas.
Consider a table of stock pices:
First value in the entire table
If you simple the first value in the entire table, the
first_value is what you're looking for. The window definition excludes any parameters (the
OVER clause) and so your window is over the entire table:
SELECT dt, price, first_value(price) over () FROM trades;
First value in the partition
You can start to fill in the window parameters to get just the first value in the current partition. In this case, we can find the first value for the month which can be used to compute a differnce or percentage growth to date.
SELECT dt, price, first_value(price) over (partition by date_trunc('month', dt)) FROM trades;
Just like the
first_value window function, you also have access to the
last_value function that picks the last value in the current partition.
You are reading a series of Advanced SQL tricks.
Read this next: Calculating Month-over-Month Growth Rate
Join 200+ data analysts who are using our weekly SQL tips to improve their skills. No spam, promise!