Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
cte
yordan
Return IDs where there are exactly 3 occurrences
sample
Correlated subquery in SELECT clause, and rewritten for Netezza
Movie Rating SQL Analysis Conducted by Miranda Zhao
Query workaround in order to avoid IN Operator in WHERE Clause
postgresql select test
Current time/date, timezone
PostreSQL: Left Join