filling missing rows with currency

Question

I have the following table (with different currencies):

date	currency	ex_rate
30/11/2020 00.00	USD	0.8347245409015025
27/11/2020 00.00	USD	0.8387854386847845
26/11/2020 00.00	USD	0.84033613445378152

As you can see, there is some missing data for two dates. I would like to fill it with the previous available date, so it would be like this:

date	currency	ex_rate
30/11/2020 00.00	USD	0.8347245409015025
29/11/2020 00.00	USD	0.8387854386847845
28/11/2020 00.00	USD	0.8387854386847845
27/11/2020 00.00	USD	0.8387854386847845
26/11/2020 00.00	USD	0.84033613445378152

Or redirect me to a question of the same kind

Answer 1

You can use generate_series to build the series of dates from the earlierst and later values available in the table, then bring the corresponding rows with a lateral join:

select d.dt, 'USD', t.ex_rate
from (
    select generate_series(min(date), max(date), interval '1 day') as dt
    from mytable
    where currency = 'USD'
) d
cross join lateral (
    select t.*
    from mytable t 
    where currency = 'USD' and t.date <= d.dt
    order by t.date desc limit 1
) t

I wonder whether a left join on date equality and then some window function technique to build groups of records might be more efficient:

select dt, 'USD', max(ex_rate) over(partition by grp) as ex_rate
from (
    select d.*, t.*,
        count(t.date) over(order by d.dt) as grp
    from (
        select generate_series(min(date), max(date), interval '1 day') as dt
        from mytable
        where currency = 'USD'
    ) d
    left join mytable t on currency = 'USD' and t.date = d.dt
) t

Note that this can easily be generalized to handle all currencies at once:

select dt, currency, max(ex_rate) over(partition by currency, grp) as ex_rate
from (
    select d.dt, c.currency, t.ex_rate,
        count(t.date) over(partition by c.currency order by d.dt) as grp
    from (select distinct currency from mytable) c
    cross join (
        select generate_series(min(date), max(date), interval '1 day') as dt
        from mytable
    ) d
    left join mytable t on t.currency = c.currency and t.date = d.dt
) t