Split effective dates into new records if there is a black/white list in place

Question

I am looking for a solution to split date range if there is black/white list in place for a product. Currently my solution is using cross join to create sequence of dates between START_DATE and END_DATE with EXIST and NOT EXIST logic to deal with the black and white listing.

Data:

PRODUCT	RETAILER	START_DATE	END_DATE	LIST_START_DATE	LIST_END_DATE	LIST
1	A	2022-01-01	2022-01-30	2022-01-15	2022-01-28	black
2	A	2022-01-05	2022-01-30	null	null	null
3	B	2022-01-02	2022-01-30	null	null	white
4	B	2022-01-01	2022-01-29	null	null	null

Expected output:

PRODUCT	RETAILER	START_DATE	END_DATE
1	A	2022-01-01	2022-01-14
1	A	2022-01-29	2022-01-30
2	A	2022-01-05	2022-01-30
3	B	2022-01-02	2022-01-30

Rules:

there can be either white or black list in place but never both
if above happens then white list takes priority
all products where list is null (no list present) are passed to the output unless white list is in place then products without list are excluded
list_start_date and list_end_date can be null which means the product is excluded without time frame (forever)
if black or white list date range is between start_date and end_date then records must be split to exclude/include these dates

In expected output product 1/retailer A is split into two records excluding dates between list_start_date and list_end_date because this range is black listed. Product 2/retailer A is included because it has no list and retailer has no white list in place. Product 3/retailer B is included because it is white listed without dates that means start_date and end_date are the effective dates. Product 4/retailer B is excluded because it has no list and retailer has white list in place.

Sample data:

with product(retailer,product,start_date,end_date) as (
    select * from values 
    ('A',1,'2022-01-01','2022-01-30'),
    ('A',2,'2022-01-05','2022-01-30'), 
    ('B',3,'2022-01-02','2022-01-30'),
    ('B',4,'2022-01-01','2022-01-29')
), list(retailer, product, list, list_start_date, list_end_date) as (
    select * from values 
    ('A',1,'black','2022-01-15','2022-01-28'),
    ('B',3,'white',null, null)
), joined as (
    select
        product.product,
        product.retailer,
        product.start_date,
        product.end_date,
        list.list_start_date,
        list.list_end_date,
        list    
    from product
    left join list
        on list.retailer = product.retailer
            and list.product = product.product
)
select *
from joined;

Thanks !

user_43992 · Answer

Range - Range joins is Snowflake are poor, luckily the solution is super fast, you explode all date ranges to single date ranges, and then equi-join those, then you can apply you black/white/null rules, and then edge-detect those groups back into ranges.

this can be done with:

set begin_date = '2022-01-01'::date;
set end_date = '2023-01-01'::date;


with product(retailer,product,start_date,end_date) as (
    select * from values 
    ('A',1,'2022-01-01'::date,'2022-01-30'::date),
    ('A',2,'2022-01-05'::date,'2022-01-30'::date), 
    ('B',3,'2022-01-02'::date,'2022-01-30'::date),
    ('B',4,'2022-01-01'::date,'2022-01-29'::date)
), list(retailer, product, list, list_start_date, list_end_date) as (
    select * from values 
    ('A',1,'black'::string,'2022-01-15','2022-01-28'),
    ('B',3,'white'::string,null, null)
), huge_date_table as (
    select dateadd(day, row_number() over(order by null)-1, $begin_date) as day
    from table(generator(ROWCOUNT => 365))
), explode_p as (
    select p.*
        ,d.day
    from product as p
    join huge_date_table as d
        on d.day between p.start_date and p.end_date
), white_per_retailer as (
    select  
        retailer
        ,count_if(list ='white') > 0 as has_white_rules
    from list
    group by 1
), date_fix_lists as (
    select 
        l.retailer 
        ,l.product 
        ,l.list 
        ,iff(l.list='white',0,1) as rule_pref
        ,nvl(l.list_start_date, $begin_date) as list_start_date
        ,nvl(l.list_end_date, $end_date) as list_end_date
    from list as l
), explode_l as (
    select l.*
        ,d.day
    from date_fix_lists as l
    join huge_date_table as d
        on d.day between l.list_start_date and l.list_end_date
    // next is to remove the black and white rules the overlap on a per day bais, white white preferred
    qualify row_number() over(partition by l.retailer, l.product, d.day order by l.rule_pref) = 1
), days_data as (
    select
        p.product,
        p.retailer,
        wpr.has_white_rules,
        p.start_date,
        p.end_date,
        p.day,
        l.list_start_date,
        l.list_end_date,
        l.list    
    from explode_p as p
    left join explode_l as l
        on l.retailer = p.retailer
            and l.product = p.product
            and l.day = p.day
    left join white_per_retailer as wpr
        on wpr.retailer = p.retailer
    where
        (nvl(wpr.has_white_rules,false) = false and nvl(l.list,'') <> 'black') or
        (nvl(wpr.has_white_rules,false) = true and nvl(l.list,'') = 'white')
), group_data as (
    select product
        ,retailer
        ,day
        ,dateadd('day',-1,day) as dm1
        ,lag(day)over(partition by product, retailer order by day) as l_day
        ,CONDITIONAL_TRUE_EVENT(dm1<>l_day)over(partition by product, retailer order by day) as grp_key
    from days_data
)
select 
    product
    ,retailer
    ,min(day) as start_date
    ,max(day) as end_date
from group_data 
group by 1,2,grp_key
order by 1,2;

PRODUCT	RETAILER	START_DATE	END_DATE
1	A	2022-01-01	2022-01-14
1	A	2022-01-29	2022-01-30
2	A	2022-01-05	2022-01-30
3	B	2022-01-02	2022-01-30

this branch of the logic:

(nvl(wpr.has_white_rules,false) = false and nvl(l.list,'') <> 'black')

could be written as:

(nvl(wpr.has_white_rules,false) = false and l.list is null)

but the not black expression is a touch clearer, I think.

Split effective dates into new records if there is a black/white list in place

1 Answers