CODEWITHSUNDEEP
sqlpostgresql
a.r.
a.r.

Reputation: 13

Calculating percentage between consecutive rows based on an ID in SQL?

I'm trying to calculate and find the largest percentage changes between dates based on an indicator_id

year                             indicator_id                 value
--------------------------      ---------------              -------
January 1, 1999, 12:00 AM             1                       1.99
January 1, 2000, 12:00 AM             1                       1.76
January 1, 2001, 12:00 AM             2                       3.37
January 1, 2006, 12:00 AM             2                       4.59

The output I'm trying to get is

year                             indicator_id                 value          % change
--------------------------      ---------------              -------         ---------
January 1, 1999, 12:00 AM             1                       1.99               0%
January 1, 2000, 12:00 AM             1                       1.76               ?
January 1, 2001, 12:00 AM             2                       3.37               0%
January 1, 2006, 12:00 AM             2                       4.59               ?

Please help

Upvotes: 0

Views: 374

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You want lag() and some arithmetic:

select t.*,
       (1 - value / nullif(lag(value) over (partition by indicator_id order by year), 0))  as ratio
from t;

Note: This returns a ratio between 0 and 1. You can multiple by 100, if you want a percentage.

Also, the first result is NULL, which makes more sense to me than 0. If you really want 0, you can use the 3 argument form of lag(): lag(value, 1, value).

Upvotes: 1

Related Questions