CODEWITHSUNDEEP
sqlpostgresqlpostgresql-14
NightHawk
NightHawk

Reputation: 47

postgreSQL - fill in blank date rows per ID

I have a table which looks like this:

ID money_earned days_since_start
1 1000 1
1 2000 2
1 3000 4
1 2000 5
2 1000 1
2 100 3

I want that rows, without a days_since_start (which means that the money_earned column was empty that day) - will include all the days PER ID, and fill the money_earned with last known value, so it to look like this:

ID money_earned days_since_start
1 1000 1
1 2000 2
1 2000 3
1 3000 4
1 2000 5
2 1000 1
2 1000 2
2 100 3

I have tried to look up for something like that, but I don't even know what function does that...

thank you!

Upvotes: 1

Views: 197

Answers (1)

D-Shih
D-Shih

Reputation: 46239

You can try to use CTE RECURSIVE with OUTER JOIN and LAG window function to make it.

WITH RECURSIVE CTE
AS
(  
    SELECT ID,MIN(days_since_start) min_num,MAX(days_since_start) max_num
    FROM T 
    GROUP BY ID
    UNION ALL
    SELECT ID,min_num+1,max_num
    FROM CTE 
    WHERE min_num+1 <= max_num
)
SELECT c.ID,
       CASE WHEN t1.ID IS NULL THEN LAG(money_earned) OVER(PARTITION BY c.ID ORDER BY c.min_num) ELSE money_earned END,
       c.min_num days_since_start
FROM CTE c
LEFT JOIN T t1
ON c.min_num = t1.days_since_start 
AND c.ID = t1.ID
ORDER BY c.ID

sqlfiddle

Upvotes: 1

Related Questions