Choosing values based on an order in a list using SQL

user_9393218 0
Viewed 31

I have data as follows:

USER_ID value  date         cost
001         A  01-01-2021     10
002         C  01-01-2021     11
001         D  01-02-2021     21
002         G  01-02-2021     23
001         H  01-03-2021     32

All I would like to do is determine which value happened 2nd and which value happened 3rd for each user. This can be determined by the date. After getting this, we also need the cost on those days.

Resulting table should be as follows:

USER_ID   second_val  third_val second_cost  third_cost
001                D          H          21          32
002                G         NA          23           0

Notice that USER_ID 002 does not have a third value, this will then be treated as NA. Consequently, the cost for an NA val is 0.

snowflake-cloud-data-platform sql
1 Answers

This should be enough:

CREATE OR REPLACE TABLE TEMP1 
AS 
SELECT USER_ID, value,  date, cost
  FROM (VALUES 
        ('001', 'A', '01-01-2021', 10),
        ('002', 'C', '01-01-2021', 11),
        ('001', 'D', '01-02-2021', 21),
        ('002', 'G', '01-02-2021', 23),
        ('001', 'H', '01-03-2021', 32)) t(USER_ID, value, date, cost);
               
SELECT DISTINCT USER_ID
     , IFNULL(NTH_VALUE(value, 2) OVER(PARTITION BY USER_ID ORDER BY date), 'NA') AS second_val
     , IFNULL(NTH_VALUE(value, 3) OVER(PARTITION BY USER_ID ORDER BY date), 'NA') AS third_val
     , IFNULL(NTH_VALUE(cost, 2) OVER(PARTITION BY USER_ID ORDER BY date), 0) AS second_cost
     , IFNULL(NTH_VALUE(cost, 3) OVER(PARTITION BY USER_ID ORDER BY date), 0) AS third_cost
  FROM TEMP1;
user_126071260
Related