gistfile1.sql

create table foresee_orders as  
(	
select distinct b.member_id, o.order_id, b.order_dwkey, b.receive_date_dwkey, d.the_date last_order_date, o.billing_address_id, 	
       case when o.shipping_sequence = 1 then 'new' else 'repeat' end cust_type, md.login_name email	
from   dsdw_prod.orders_dim o, dsdw_prod.members_dim md, dsdw_prod.date_dim d,	
( 	
  select distinct member_id, max(order_dwkey) order_dwkey, max(receive_date_dwkey) receive_date_dwkey	
  from   dsdw_prod.orders_dim o, (select distinct date_dwkey from dsdw_prod.date_dim where the_date >= to_date('123012','mmddyy') AND the_date <= to_date('010513','mmddyy')) d	
  where  ORDER_SHIPPED_DATE_DWKEY = D.DATE_DWKEY and 	
         o.cancelled_order_flag = 'N' and o.order_type != 2 and o.site_id in (0,1,30) and nvl(shipping_sequence, -1) != -1	
  group by member_id	
)b	
where  o.order_dwkey = b.order_dwkey and o.receive_date_dwkey = b.receive_date_dwkey and o.member_id = md.member_id and o.receive_date_dwkey = d.date_dwkey and	
       o.cancelled_order_flag = 'N' and o.order_type != 2 and o.site_id in (0,1,30) and md.current_flag = 'Y'	
)	
go --36 minutes	
--114823	
	
delete from foresee_orders where member_id in	
(	
select distinct member_id 	
	from foresee_orders m, dsdw_prod.email_subscriptions e
	where m.email = e.email_address and never_email = 1
)	
go --	
--11754
	
delete from foresee_orders where member_id in	
(	
select distinct member_id	
	from foresee_orders m, dsdw_prod.EMAIL_ADDRESSES_DIM e
	where m.email = e.email_address and EMAIL_DOMAIN IN ('drugstore.com','beauty.com','sephora.com','amazon.com') 
)	
go	
--25	
	
select count(distinct member_id), count(*) from foresee_orders	
go	
--103044	103044

/* DC id 17 dwkey 244 = nevada, dc_type 10 = dropship */
	
create table foresee_orders_2 as	
(	
select distinct member_id, billing_address_id, order_id, cust_type, email, last_order_date, sum_division_id, 	
       case when dc_type = 10 then 'DS' when dc_type != 10 and dc_id = 17 then 'NV'  when dc_type != 10 and dc_id = 30 then 'IL' else 'NJ' end as DC, 
       row_number() over (partition by case when sum_division_id in (2,3) then 5 else sum_division_id end order by dbms_random.value) random_rk	
from	
(	
select distinct o.member_id, o.billing_address_id, o.order_id, o.cust_type, email, o.last_order_date, sum(distinct i.division_id) sum_division_id,
       min(dc_type) dc_type, min(dc_id) dc_id	
from  foresee_orders o, dsdw_prod.suborder_lineitem_fact s, dsdw_prod.current_item_dim i, dsdw_prod.distribution_centers_dim dc	
where o.order_dwkey = s.order_dwkey and o.receive_date_dwkey = s.receive_date_dwkey and s.item_id = i.item_id and s.dc_dwkey = dc.dc_dwkey and	
      s.deleted_flag = 'N' and s.site_id in (0,1,30) and s.unit_price > 0 and i.division_id in (1,2,7)	
group by o.member_id, o.billing_address_id, o.order_id, o.cust_type, email, o.last_order_date	
))	
go	
--101839
	
select count(distinct member_id), count(*) from foresee_orders_2	
go	
--101839	101839
select sum_division_id, count(distinct member_id) from foresee_orders_2 group by sum_division_id	
go	
	
/*	
1	82674
2	4353
3	4882
7	9930
*/	
	
drop table foresee_orders purge	
go 	
	
select * from foresee_orders_2 where sum_division_id in (2,3) and random_rk <= 4800	
go	
	
select * from foresee_orders_2 where sum_division_id = 7 and random_rk <= 1200	
go	
	
select * from foresee_orders_2 where sum_division_id = 1 and random_rk <= 9600	
go	
	
drop table foresee_orders_2	purge
go	
Grant all on foresee_orders_2 to djarvi