Skip to content

Instantly share code, notes, and snippets.

@apple-corps
Created February 16, 2018 18:09
Show Gist options
  • Save apple-corps/79153b1ae228fa6a0f58b9958f552bbb to your computer and use it in GitHub Desktop.
Save apple-corps/79153b1ae228fa6a0f58b9958f552bbb to your computer and use it in GitHub Desktop.
create temp table event_shp
as (
select cust_key,
case when delivery_channel <> 'JOIN' and
product_type = 'General' and
sale_dt in
(select event_dt
from user_tbls.events
where anniversary_public_event=1 ) then 1
else 0
end as Anniversary_Public,
case when delivery_channel <> 'JOIN' and
product_type = 'General' and
sale_dt in
(select event_dt
from user_tbls.events
where anniversary_early_access=1) then 1
else 0
end as Anniversary_EA,
from user_tbls.transitory
);
@apple-corps
Copy link
Author

@apple-corps
Copy link
Author

apple-corps commented Feb 16, 2018

Given modified table without subquery above maybe something like :

select * from event_shp E left join select event_dt,anniversary_public_event,anniversary_early_access from user_tbls.events T
 CASE WHEN E.sale_dt=T.event_dt
   then E.AnniversaryPublic & T.anniversary_public_event as E.Anniversary_Public and E.Anniversary_EA & T.anniversary_early_access as E.Anniversary_EA
 else 0 as E.Anniversary_Public and 0 as E.Anniversary_EA;

@chrwei
Copy link

chrwei commented Feb 16, 2018

select cust_key,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
            anniversary_public_event=1 ) then 1
      else 0
      end as Anniversary_Public,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
            anniversary_early_access=1) then 1
      else 0
      end as Anniversary_EA,
 from user_tbls.transitory left join user_tbls.events on sale_dt = event_dt 

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment