Skip to content

Instantly share code, notes, and snippets.

@jmelloy
Created February 7, 2013 23:12
Show Gist options
  • Save jmelloy/4735091 to your computer and use it in GitHub Desktop.
Save jmelloy/4735091 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment