-1

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 
;

SQL Fiddle Here

I have tried LAG() and some other examples found here but none of them seem to get the right last balance for the user.

sge
  • 170
  • 7

2 Answers2

1

We group every balance with the nulls that come after it by using count() over() and then we use max() over() to give the entire group the same value.

select name
      ,day
      ,max(balance) over(partition by name, grp order by day) as balance
from 
(
select      u.name
           ,s.day
           ,b.balance
           ,count(case when b.balance is not null then 1 end) over(partition by u.name order by s.day) as grp
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 
) t
name day balance
alice 2022-01-01 100
alice 2022-01-02 100
alice 2022-01-03 200
alice 2022-01-04 300
alice 2022-01-05 300
bob 2022-01-01 400
bob 2022-01-02 500
bob 2022-01-03 500
bob 2022-01-04 500
bob 2022-01-05 600

Fiddle

DannySlor
  • 4,574
  • 1
  • 3
  • 11
  • This solution adds the sum of all previous balances. I need it to add the last balance value. E.g. bob's 2022-01-05 balance should be 600 – sge Oct 12 '22 at 19:10
0

Based on How do I efficiently select the previous non-null value?, I ended up getting successful results with the following query:

select
  name, 
  day, 
  first_value(balance) over (partition by x.name, value_partition order by day) as balance
from (
  select 
    u.name as name, 
    s.day as day, 
    b.balance as balance,
    sum(case when b.balance is null then 0 else 1 end) over (partition by u.name order by s.day) as value_partition
  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
) x
order by x.name, x.day

DB Fiddle

sge
  • 170
  • 7