SQL: Deduce Effective Pricing from overlapping Dates

Using the logic of new starting price window wins for overlaps.

Discreate Date version:

with data(article,price,startdate,enddate) as (
    select * FROM VALUES
        (123, 10, '2022-02-02'::date, '2049-12-31'::date),
        (123, 8,  '2022-02-14'::date, '2022-09-14'::date),
        (123, 5,  '2022-03-14'::date, '2022-04-06'::date),
        (123, 4,  '2022-04-11'::date, '2022-04-27'::date)
), dis_times as (
    select article,
        date as startdate,
        lead(date) over(partition by article order by date)-1 as enddate
    from (
        select distinct article, startdate as date from data
        union
        select distinct article, enddate+1 as date from data
    )
    qualify enddate is not null
)
select 
    d1.article, 
    d1.price, 
    d2.startdate,
    d2.enddate
from data as d1
join dis_times as d2
    on d1.article = d2.article 
        and d2.startdate between d1.startdate and d1.enddate qualify row_number() over (partition by d1.article, s_startdate order by d1.startdate desc) = 1
order by 1,3;

gives:

Continuous Timestamp version:

with data(article,price,startdate,enddate) as (
    select * FROM VALUES
        (123, 10, '2022-02-02'::date, '2049-12-31'::date),
        (123, 8,  '2022-02-14'::date, '2022-09-14'::date),
        (123, 5,  '2022-03-14'::date, '2022-04-06'::date),
        (123, 4,  '2022-04-11'::date, '2022-04-27'::date)
), dis_times as (
    select article,
        date as startdate,
        lead(date) over(partition by article order by date) as enddate
    from (
        select distinct article, startdate as date from data
        union
        select distinct article, enddate as date from data
    )
    qualify enddate is not null
)
select 
    d1.article, 
    d1.price, 
    d2.startdate,
    d2.enddate
from data as d1
join dis_times as d2
    on d1.article = d2.article 
        and d2.startdate >= d1.startdate and d2.startdate < d1.enddate
qualify row_number() over (partition by d1.article, s_startdate order by d1.startdate desc) = 1
order by 1,3;

which gives:

Thanks to MatBailie for the tighter join suggestion.

join dis_times as d2
    on d1.article = d2.article 
        and d2.startdate between d1.startdate and d1.enddate 

the continuous range I would normally do in this for

and d2.startdate between d1.startdate and d1.enddate and d2.startdate < d1.enddate

instead of this form

and d2.startdate >= d1.startdate and d2.startdate < d1.enddate

because I in experience it performed better. always test your complexities.

First thing I did was --I turned your price-per-date range data into a price-per-date lookup table.

create or replace temporary table price_date_lookup as
 
select distinct 
       article,
       dateadd('day',b.index-1,start_date) as dates,
       first_value(price) over (partition by article, dates order by end_date) as price
from my_table, 
     lateral split_to_table(repeat('.',datediff(day,start_date,end_date)), '.') b;

Notes:

• first_value handles overlaps by overriding prices based on their end dates.
• lateral... basically helps create a date column with all the days in the range

As soon as I created that table, I figured the rest could be approached like a gaps and island problem.

with cte1 as

(select *, case when lag(price) over (partition by article order by dates)=price then 0 else 1 end as price_start --flag start of a new price island
 from price_date_lookup),
 
cte2 as
 
(select *, sum(price_start) over (partition by article order by dates) as price_id --assign id to all the price islands
 from cte1)

 
select article, 
       price,
       min(dates) as start_date,
       max(dates) as end_date
from cte2
group by article,price,price_id;