complicated-conditional-sql-query-to-match-attributes-for-marketing-campaigns/43219741#43219741, PostgreSQL

complicated-conditional-sql-query-to-match-attributes-for-marketing-campaigns/43219741#43219741

create table products (
    id int,
    name text,
    popularity int,
    geo text
);
insert into products (id, name, popularity, geo) values
(1   ,'OATMEAL'      , 5293           , 'USA'),
(2   ,'FROZEN PIZZA' , 9050           , 'CA'),
(3   ,'SOUP'         , 0              , 'USA'),
(4   ,'COOKIES'      , 100            , 'USA');

create table marketing_campaigns (
    id int, name text
);
insert into marketing_campaigns (id, name) values
(1,'GEOGRAPHIC MARKETING CAMPAIGN'),
(2,'POPULARITY-BASED MARKETING CAMPAIGN'),
(3,'MULTI-ATTRIBUTE MARKETING CAMPAIGN'),
(4,'UNTARGETED MARKETING CAMPAIGN');

create table marketing_campaign_targets (
    id int,
    marketing_campaign_id int,
    target_type text,
    target_value text
);
insert into marketing_campaign_targets (id, marketing_campaign_id, target_type, target_value) values
(1   , 1                      , 'GEO'               , 'CA'),
(2   , 2                      , 'MIN_POPULARITY'    , '500'),   
(3   , 3                      , 'GEO'               , 'USA'),   
(4   , 3                      , 'MIN_POPULARITY'    , '100');

select distinct c.*
from
    products p
    inner join
    marketing_campaign_targets t on
        t.target_type = 'GEO' and p.geo = t.target_value or
        t.target_type = 'MIN_POPULARITY' and p.popularity >= t.target_value::int
    inner join
    marketing_campaigns c on c.id = t.marketing_campaign_id or c.id = 4
where p.id = 1
;


run \| edit \| history \| help	0