CODEWITHSUNDEEP
sqlpostgresql
Omega
Omega

Reputation: 871

PostgreSQL year-over-year growth

How can I calculate the year-over-year growth by country in PostgreSQL? I have a query which works reasonably well, but it also takes values from one country and compares it with those from another country, when the value for the first year should be null or zero.

Expected result:

year | country | value | yoy
2019     A         10    -0.66
2018     A         20     0.05
2017     A         19    null
2019     B         8     -0.22
2018     B         10    -0.66
2017     B         20    null

Current result:

year | country | value | yoy
    2019     A         10    -0.66
    2018     A         20     0.05
    2017     A         19     0.81
    2019     B         8     -0.22
    2018     B         10    -0.66
    2017     B         20    null

Query:

SELECT *, 
       - 100.0 * (1 - LEAD(value) OVER (ORDER BY t.country) / value) AS Grown
FROM tbl AS t
ORDER BY t.country

Upvotes: 2

Views: 749

Answers (2)

Ben Wilson
Ben Wilson

Reputation: 2676

For monthly data:

SELECT current_table.item_id, current_table.date, (current_table.count - year_ago_table.count)/year_ago_table.count as count_year_over_year,

FROM 

(SELECT table.item_id, table - INTERVAL '1 year' as year_ago_date, table.count  FROM table) current_table

JOIN

(SELECT table.item_id, table.date, table.count  FROM table) year_ago_table

ON current_table.item_id = year_ago_table.item_id AND
current_table.year_ago_date = year_ago_table.date

ORDER BY date DESC

Upvotes: 0

eshirvana
eshirvana

Reputation: 24603

then get the lead() withing each country ordered by year:

SELECT *, 
       - 100.0 * (value - LEAD(value) OVER (Partition by Country ORDER BY t.year) / value) AS Growth
FROM tbl AS t
ORDER BY t.country

Upvotes: 3

Related Questions