Getting percentage change between selected data within a column in PostgreSQL-CodePudding

I am using PostgreSQL and I am trying to calculate the percentage change for two values in the same column and group them by the name column and I am having trouble.

Suppose I have the following table:

name	day	score
Allen	1	87
Allen	2	89
Allen	3	95
Bob	1	64
Bob	2	68
Bob	3	75
Carl	1	71
Carl	2	77
Carl	3	80

I want the result to be the name and the percentage change for each person between day 3 and day 1. So Allen would be 9.2 because from 87 to 95 is a 9.2 percent increase.

I want the result to be:

name	percent_change
Allen	9.2
Bob	17.2
Carl	12.7

Thanks for your help.

CodePudding user response：

Try this...


    with dummy_table as (
        select
            name, 
            day, 
            score as first_day_score, 
            lag(score, 2) over (partition by name order by day desc) as last_day_score
        from YOUR_TABLE_NAME
    )
    select 
        name,  
        (last_day_score - first_day_score) / first_day_score::decimal as percentage_change 
    from dummy_table where last_day_score is not null

Just replace YOUR_TABLE_NAME. There are likely more performant and fancier solutions, but this works.

CodePudding user response：

You can try with lag function, something like this:

select name, day, score, 100*(score - lag(score, 1) over (partition by name order by day))/(lag(score, 1) over (partition by name order by day)) as growth_percentage