add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table table_1 as
select '2020-01-01'::date as date, 172 as revenue_North_AM, 38 as revenue_Asia, 10 as revenue_Africa, 20 as revenue_Oceania union all
select '2020-01-04'::date as date, 125 as revenue_North_AM, 100 as revenue_Asia, 30 as revenue_Africa, 55 as revenue_Oceania union all
select '2020-01-07'::date as date, 202 as revenue_North_AM, 312 as revenue_Asia, 20 as revenue_Africa, 85 as revenue_Oceania union all
select '2020-01-10'::date as date, 212 as revenue_North_AM, 35 as revenue_Asia, 0 as revenue_Africa, 35 as revenue_Oceania union all
select '2020-01-13'::date as date, 120 as revenue_North_AM, 50 as revenue_Asia, 30 as revenue_Africa, 20 as revenue_Oceania 
5 rows affected
select t1.date, t3.region, t3.revenue
from (select gs.date,
revenue_North_AM / num_days as revenue_North_AM,
revenue_Asia / num_days as revenue_Asia,
revenue_Africa / num_days as revenue_Africa,
revenue_Oceania / num_days as revenue_Oceania
from (select t1.*,
coalesce(lead(date) over (order by date) - interval '1 day', date) as last_date,
(lead(date) over (order by date) - date) as num_days
from table_1 t1
) t1 cross join
generate_series(date, last_date, interval '1 day') gs(date)
) t1 cross join lateral
(values (t1.revenue_North_AM, 'North_AM'),
(t1.revenue_Asia, 'Asia'),
(t1.revenue_Africa, 'Africa'),
(t1.revenue_Oceania, 'Oceania')
) t3(revenue, region)
date region revenue
2020-01-01 00:00:00 North_AM 57
2020-01-02 00:00:00 North_AM 57
2020-01-03 00:00:00 North_AM 57
2020-01-01 00:00:00 Asia 12
2020-01-02 00:00:00 Asia 12
2020-01-03 00:00:00 Asia 12
2020-01-01 00:00:00 Africa 3
2020-01-02 00:00:00 Africa 3
2020-01-03 00:00:00 Africa 3
2020-01-01 00:00:00 Oceania 6
2020-01-02 00:00:00 Oceania 6
2020-01-03 00:00:00 Oceania 6
2020-01-04 00:00:00 North_AM 41
2020-01-05 00:00:00 North_AM 41
2020-01-06 00:00:00 North_AM 41
2020-01-04 00:00:00 Asia 33
2020-01-05 00:00:00 Asia 33
2020-01-06 00:00:00 Asia 33
2020-01-04 00:00:00 Africa 10
2020-01-05 00:00:00 Africa 10
2020-01-06 00:00:00 Africa 10
2020-01-04 00:00:00 Oceania 18
2020-01-05 00:00:00 Oceania 18
2020-01-06 00:00:00 Oceania 18
2020-01-07 00:00:00 North_AM 67
2020-01-08 00:00:00 North_AM 67
2020-01-09 00:00:00 North_AM 67
2020-01-07 00:00:00 Asia 104
2020-01-08 00:00:00 Asia 104
2020-01-09 00:00:00 Asia 104
2020-01-07 00:00:00 Africa 6
2020-01-08 00:00:00 Africa 6
2020-01-09 00:00:00 Africa 6
2020-01-07 00:00:00 Oceania 28
2020-01-08 00:00:00 Oceania 28
2020-01-09 00:00:00 Oceania 28
2020-01-10 00:00:00 North_AM 70
2020-01-11 00:00:00 North_AM 70
2020-01-12 00:00:00 North_AM 70
2020-01-10 00:00:00 Asia 11
2020-01-11 00:00:00 Asia 11
2020-01-12 00:00:00 Asia 11
2020-01-10 00:00:00 Africa 0
2020-01-11 00:00:00 Africa 0
2020-01-12 00:00:00 Africa 0
2020-01-10 00:00:00 Oceania 11
2020-01-11 00:00:00 Oceania 11
2020-01-12 00:00:00 Oceania 11
2020-01-13 00:00:00 North_AM null
2020-01-13 00:00:00 Asia null
2020-01-13 00:00:00 Africa null
2020-01-13 00:00:00 Oceania null