Difference of top two values while GROUP BY

Question

Suppose I have the following SQL Table:

id | score
------------
1  |  4433
1  |  678
1  |  1230
1  |  414
5  |  8899
5  |  123
6  |  2345
6  |  567
6  |  2323

Now I wanted to do a GROUP BY id operation wherein the score column would be modified as follows: take the absolute difference between the top two highest scores for each id.

For example, the response for the above query should be:

id  |  score
------------
1   |  3203
5   |  8776
6   |  22

How can I perform this query in PostgreSQL?

Answer 1

Using ROW_NUMBER along with pivoting logic we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY score DESC) rn
    FROM yourTable
)

SELECT id,
       ABS(MAX(score) FILTER (WHERE rn = 1) -
               MAX(score) FILTER (WHERE rn = 2)) AS score
FROM cte
GROUP BY id;

Demo