select count(c.id)
from customer_dim c
where c.id in
(select distinct f.customer_dim_id from event_fact f join product_dim p on f.product_dim_id = p.id
where event_type_dim_id=1 and created_date='19/03/2011' and f.skin_dim_id='1')
and not exists (select * from event_fact f join product_dim p on f.product_dim_id = p.id
where event_type_dim_id=1 and f.created_date>=to_date('19/03/2011','DD/MM/YYYY') - interval '30 days'
and f.created_date<'19/03/2011' and f.skin_dim_id='1' and f.customer_dim_id=c.id and f.skin_dim_id=c.skin_dim_id)
and exists (select * from agg_event_extract agg
where agg.event_type_dim_id=4 and agg.period='lifetime' and agg.skin_dim_id=1 and agg.customer_dim_id=c.id
and agg.skin_dim_id=c.skin_dim_id and agg.min_created_cet is not null)
What I have done -
A:
LOAD DISTINCT
customer_dim_id as cid_a,
(date#(created_date,'DD/MM/YYYY')) as cdate_a
Resident EventFacts WHERE event_type_dim_id= '1'
B:
LOAD DISTINCT
customer_dim_id as cid_b,
(date#(created_date,'DD/MM/YYYY')) as cdate_b
Resident EventFacts WHERE event_type_dim_id= '1' AND (created_date >= to_date('19/03/2011','DD/MM/YYYY') -30 AND created_date <= to_date('19/03/2011','DD/MM/YYYY'));
C:
LOAD
customer_dim_id as cid_c
RESIDENT CustomerLifeTimeValue WHERE deposit_lifetime_min_created_cet <> 'Null' ;
MainQuery:
LOAD Distinct
Count( customer_dim_id)
RESIDENT EventFacts WHERE event_type_dim_id and not exists(cid_b,customer_dim_id) and exists(cid_c,customer_dim_id);
How shall I go about doing the IN(Sub Query) bit and also the 19/03/2011 is a selection from the main dashboard, How can I make it into a parameter ?
Hopw I was clear enough
Steve
