1. SQL Recipes
2. Calculations Per Group
3. Calculating Difference from Beginning Row

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;

Also, last_value

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.