Given the following tables:
users:
| name |
|---|
| alice |
| bob |
balances:
| id | user_name | date | balance |
|---|---|---|---|
| 1 | alice | 2022-01-01 | 100 |
| 2 | alice | 2022-01-03 | 200 |
| 3 | alice | 2022-01-04 | 300 |
| 4 | bob | 2022-01-01 | 400 |
| 5 | bob | 2022-01-02 | 500 |
| 6 | bob | 2022-01-05 | 600 |
I would like to get a full list of all days from the first available to the last for all users, replacing NULL balances with the last available balance for that user.
This is what I have so far:
select u.name, s.day, b.balance
from users u
cross join (select generate_series(min(day)::date, max(day)::date, interval '1 day')::date as day from balances) s
left join balances b on b.user_name = u.name and s.day = b.day
order by u.name, s.day
;
I have tried LAG() and some other examples found here but none of them seem to get the right last balance for the user.
CodePudding user response：
We want to use running_total: sum() over()
select u.name
,s.day
,sum(b.balance) over(partition by u.name order by s.day) as balance
from users u
cross join (select generate_series(min(day)::date, max(day)::date, interval '1 day')::date as day from balances) s
left join balances b on b.user_name = u.name and s.day = b.day
order by u.name, s.day
| name | day | balance |
|---|---|---|
| alice | 2022-01-01 | 100 |
| alice | 2022-01-02 | 100 |
| alice | 2022-01-03 | 300 |
| alice | 2022-01-04 | 600 |
| alice | 2022-01-05 | 600 |
| bob | 2022-01-01 | 400 |
| bob | 2022-01-02 | 900 |
| bob | 2022-01-03 | 900 |
| bob | 2022-01-04 | 900 |
| bob | 2022-01-05 | 1500 |