How to fill table with missed dates in PostgreSQL with previous data

Daniel G 0
Viewed 42

I have a table:

date user_id state
8/12/2021 1 visit
9/12/2021 1 registered
12/12/2021 1 order

In this table I only have updated of state of users, but I don't see the state by some particular date. How can I add rows with missing dates and fill them with previous value, so that the table will be:

date user_id state
8/12/2021 1 visit
9/12/2021 1 registered
10/12/2021 1 registered
11/12/2021 1 registered
12/12/2021 1 order
missing-data sql postgresql
1 Answers

Here's one attempt. The cte user_dates gets min and max dates for each user that is then fed to generate_series. I.e. each user is associated with all dates between there first and last date.

In the inner select we create a group for each first_value and consecutive null states.

In the outer select we pick the first_value for each such grp.

with user_dates(f, t, user_id) as (
  select min(T.dt), max(T.dt), user_id
  from T
  group by user_id
)
select user_id, dt, grp, first_value(state) over (partition by user_id, grp order by dt)
from (  
    select ud.user_id
         , cal.dt::date
         , state
         , count(T.state) over (partition by user_id
                                order by cal.dt) as grp
    from user_dates ud
    cross join generate_series(ud.f::timestamp, ud.t::timestamp , interval '1 day') cal (dt)
    left join T
        using (dt, user_id)
) as tmp 
order by user_id, dt
;
user_id dt  grp first_value
1   2021-12-08  1   visit
1   2021-12-09  2   registered
1   2021-12-10  2   registered
1   2021-12-11  2   registered
1   2021-12-12  3   order

You can remove grp from the select, it's merely there for informative purposes.

Fiddle

Lennart - Slava Ukraini0
Related