Generate dates in-between date and date after

Based on the table below

date          country   revenue
2020-01-01      US        120
2020-01-01      UK         90
2020-01-01      IT         60
2020-01-05      US        180
2020-01-05      UK         45
2020-01-05      IT         27
2020-01-09     ...        ... 
...
2020-01-13     ...        ...

I want to generate the dates in between, by dividing the revenue by the number of days from the date in the current row and the row below.

Example: Revenue in US on 1 January 2020 is 120. The next revenue date for the US is 5 January 2020. There are 4 days in betweeen. Thus, to make the revenue daily, I would like to divide 120 divided by 4 days = 30.

Output table

date          country   revenue
2020-01-01      US         30 (120 / 4)
2020-01-02      US         30
2020-01-03      US         30
2020-01-04      US         30
2020-01-01      UK        22.5 (90 / 4)
2020-01-02      UK        22.5
2020-01-03      UK        22.5
2020-01-04      UK        22.5
2020-01-01      IT        15.0 (60 / 4)
2020-01-02      IT        15.0
2020-01-03      IT        15.0
2020-01-04      IT        15.0
...
2020-01-05      US        45.0 (180 / 4)
2020-01-06      US        45.0
2020-01-07      US        45.0
2020-01-08      US        45.0
...

My idea is to use a generate_series, but generate_series requires two dates?

Answer

Similarly to the above reply, the lead function will give you the value of the next date. Also, in case you need to count up to today’s date in when you don’t have any further transaction, the coalesce can substitute the missing target date with CURRENT_DATE.

Data

create table rev (rev_date date, country varchar, revenue int);
insert into rev values('2020-01-01','US',120);
insert into rev values('2020-01-01','UK',90);
insert into rev values('2020-01-01','IT',60);
insert into rev values('2020-01-05','US',180);
insert into rev values('2020-01-05','UK',45);
insert into rev values('2020-01-05','IT',27);

Query

with current_and_prev as (
select country, 
    rev_date, 
    lead(rev_date) over (partition by country order by rev_date) next_date, 
    revenue
from rev
order by country, rev_date)

select country, 
    dd rev_date, 
    revenue::float / (coalesce(next_date,current_date) - rev_date) revenue_split
from current_and_prev, 
generate_series(rev_date, coalesce(next_date,current_date), '1 day'::interval) dd;