Generate dates in-between date and date after

user_13391350 0
Viewed 48

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?

postgresql sql
2 Answers
 create table revenues(dates date, country varchar(20)   ,revenue int);
 insert into revenues values('2020-01-01',      'US',        120);
 insert into revenues values('2020-01-01',      'UK' ,        90);
 insert into revenues values('2020-01-01',      'IT'  ,       60);
 insert into revenues values('2020-01-05',      'US'   ,     180);
 insert into revenues values('2020-01-05',      'UK'    ,     45);
 insert into revenues values('2020-01-04',      'IT'     ,    27);

Query:

 WITH CTE AS 
 (select *,lead(dates)over(partition by country order by dates)-1 NEXTDATE
 
 from revenues r
 )
 SELECT (CASE WHEN GENERATED_DATE IS NULL THEN DATES ELSE GENERATED_DATE END)DATES,
 COUNTRY,MIN(REVENUE::FLOAT)OVER(PARTITION BY COUNTRY,NEXTDATE)/COUNT(REVENUE)OVER(PARTITION BY COUNTRY,NEXTDATE) REVENUE
 FROM CTE
 LEFT   JOIN LATERAL 
 generate_series(dates,NEXTDATE,interval '1 day' day)GENERATED_DATE
 ON TRUE
 ORDER BY COUNTRY DESC,DATES

Output:

dates country revenue
2020-01-01 00:00:00+00 US 30
2020-01-02 00:00:00+00 US 30
2020-01-03 00:00:00+00 US 30
2020-01-04 00:00:00+00 US 30
2020-01-05 00:00:00+00 US 180
2020-01-01 00:00:00+00 UK 22.5
2020-01-02 00:00:00+00 UK 22.5
2020-01-03 00:00:00+00 UK 22.5
2020-01-04 00:00:00+00 UK 22.5
2020-01-05 00:00:00+00 UK 45
2020-01-01 00:00:00+00 IT 20
2020-01-02 00:00:00+00 IT 20
2020-01-03 00:00:00+00 IT 20
2020-01-04 00:00:00+00 IT 27

db<fiddle here

user_86516010

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;
user_76057620
Related