Find entity with the biggest difference in a given column

I hava table profiles and I want to get the account with the most gained followers. I am using Postgresql. Note current record and previous record are all on the same table but with different timestamps.

id                                  |username       |person    |tweets|followers|following|likes|created_at|
------------------------------------|---------------|----------|------|---------|---------|-----|----------|
3fa34100-d688-4051-a687-ec49d05e7212|renok          |NULL      |   110|        6|        0|    0|2020-10-10|
bab9ceb9-2770-49ea-8489-77e5d763a223|Lydia_C        |test user2|   515|     1301|     1852| 1677|2020-10-10|
4649077a-9188-4821-a1ec-3b38608ea44a|Kingston_Sav   |NULL      |  2730|     1087|     1082| 1339|2020-10-10|
eef80836-e140-4adc-9598-8b612ab1825b|TP_s           |NULL      |  1835|      998|      956| 1832|2020-10-10|
fd3ff8c7-0994-40b6-abe0-915368ab9ae5|DKSnr4         |NULL      |   580|      268|      705|  703|2020-10-10|
3fa34100-d688-4051-a687-ec49d05e7212|renok          |NULL      |   119|        6|        0|    0|2020-10-12|
bab9ceb9-2770-49ea-8489-77e5d763a223|Lydia_C        |test user2|   516|     1301|     1852| 1687|2020-10-12|
4649077a-9188-4821-a1ec-3b38608ea44a|Kingston_Sav   |NULL      |  2737|     1090|     1084| 1342|2020-10-12|
eef80836-e140-4adc-9598-8b612ab1825b|TP_s           |NULL      |  1833|      998|      957| 1837|2020-10-12|
fd3ff8c7-0994-40b6-abe0-915368ab9ae5|DKSnr4         |NULL      |   570|      268|      700|  703|2020-10-12|

I have the logic in my mind but honestly I don’t know how to express/think of it in terms of a sql query. This is my expected output.

id                                   | person | username      |gain      | 
-------------------------------------|--------|---------------|----------|
3fa34100-d688-4051-a687-ec49d05e7212 | NULL   | Kingston_Sav  | 3        |

The gain is will be the difference.

Any assistance is highly appreciated.Thank you.

Answer

The below answer worked for me.

 WITH cte AS (
       SELECT *, dense_rank() OVER (ORDER BY created_at DESC) AS rnk
       FROM   t
       )
    SELECT d1.*
         , d2.followers AS prev_followers
         , d1.followers - d2.followers AS gain
    FROM  (SELECT * FROM cte WHERE rnk = 1) d1
    JOIN  (SELECT * FROM cte WHERE rnk = 2) d2 USING (username)
    ORDER  BY gain DESC
    FETCH  FIRST 1 ROWS WITH TIES;

For more details and explanations check out this answer

Advertisement

Answer