explain (costs off) with cs_ui as
 (select cs_item_sk
        ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
  from catalog_sales
      ,catalog_returns
  where cs_item_sk = cr_item_sk
    and cs_order_number = cr_order_number
  group by cs_item_sk
  having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
cross_sales as
 (select i_product_name product_name
     ,i_item_sk item_sk
     ,s_store_name store_name
     ,s_zip store_zip
     ,ad1.ca_street_number b_street_number
     ,ad1.ca_street_name b_streen_name
     ,ad1.ca_city b_city
     ,ad1.ca_zip b_zip
     ,ad2.ca_street_number c_street_number
     ,ad2.ca_street_name c_street_name
     ,ad2.ca_city c_city
     ,ad2.ca_zip c_zip
     ,d1.d_year as syear
     ,d2.d_year as fsyear
     ,d3.d_year s2year
     ,count(*) as cnt
     ,sum(ss_wholesale_cost) as s1
     ,sum(ss_list_price) as s2
     ,sum(ss_coupon_amt) as s3
  FROM   store_sales
        ,store_returns
        ,cs_ui
        ,date_dim d1
        ,date_dim d2
        ,date_dim d3
        ,store
        ,customer
        ,customer_demographics cd1
        ,customer_demographics cd2
        ,promotion
        ,household_demographics hd1
        ,household_demographics hd2
        ,customer_address ad1
        ,customer_address ad2
        ,income_band ib1
        ,income_band ib2
        ,item
  WHERE  ss_store_sk = s_store_sk AND
         ss_sold_date_sk = d1.d_date_sk AND
         ss_customer_sk = c_customer_sk AND
         ss_cdemo_sk= cd1.cd_demo_sk AND
         ss_hdemo_sk = hd1.hd_demo_sk AND
         ss_addr_sk = ad1.ca_address_sk and
         ss_item_sk = i_item_sk and
         ss_item_sk = sr_item_sk and
         ss_ticket_number = sr_ticket_number and
         ss_item_sk = cs_ui.cs_item_sk and
         c_current_cdemo_sk = cd2.cd_demo_sk AND
         c_current_hdemo_sk = hd2.hd_demo_sk AND
         c_current_addr_sk = ad2.ca_address_sk and
         c_first_sales_date_sk = d2.d_date_sk and
         c_first_shipto_date_sk = d3.d_date_sk and
         ss_promo_sk = p_promo_sk and
         hd1.hd_income_band_sk = ib1.ib_income_band_sk and
         hd2.hd_income_band_sk = ib2.ib_income_band_sk and
         cd1.cd_marital_status <> cd2.cd_marital_status and
         i_color in ('navajo','burlywood','cornflower','olive','turquoise','linen') and
         i_current_price between 79 and 79 + 10 and
         i_current_price between 79 + 1 and 79 + 15
group by i_product_name
       ,i_item_sk
       ,s_store_name
       ,s_zip
       ,ad1.ca_street_number
       ,ad1.ca_street_name
       ,ad1.ca_city
       ,ad1.ca_zip
       ,ad2.ca_street_number
       ,ad2.ca_street_name
       ,ad2.ca_city
       ,ad2.ca_zip
       ,d1.d_year
       ,d2.d_year
       ,d3.d_year
)
select  cs1.product_name
     ,cs1.store_name
     ,cs1.store_zip
     ,cs1.b_street_number
     ,cs1.b_streen_name
     ,cs1.b_city
     ,cs1.b_zip
     ,cs1.c_street_number
     ,cs1.c_street_name
     ,cs1.c_city
     ,cs1.c_zip
     ,cs1.syear
     ,cs1.cnt
     ,cs1.s1
     ,cs1.s2
     ,cs1.s3
     ,cs2.s1
     ,cs2.s2
     ,cs2.s3
     ,cs2.syear
     ,cs2.cnt
from cross_sales cs1,cross_sales cs2
where cs1.item_sk=cs2.item_sk and
     cs1.syear = 2001 and
     cs2.syear = 2001 + 1 and
     cs2.cnt <= cs1.cnt and
     cs1.store_name = cs2.store_name and
     cs1.store_zip = cs2.store_zip
order by cs1.product_name
       ,cs1.store_name
       ,cs2.cnt;

explain (costs off) select    
         w_warehouse_name
   ,w_warehouse_sq_ft
   ,w_city
   ,w_county
   ,w_state
   ,w_country
        ,ship_carriers
        ,year
   ,sum(jan_sales) as jan_sales
   ,sum(feb_sales) as feb_sales
   ,sum(mar_sales) as mar_sales
   ,sum(apr_sales) as apr_sales
   ,sum(may_sales) as may_sales
   ,sum(jun_sales) as jun_sales
   ,sum(jul_sales) as jul_sales
   ,sum(aug_sales) as aug_sales
   ,sum(sep_sales) as sep_sales
   ,sum(oct_sales) as oct_sales
   ,sum(nov_sales) as nov_sales
   ,sum(dec_sales) as dec_sales
   ,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot
   ,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot
   ,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot
   ,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot
   ,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot
   ,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot
   ,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot
   ,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot
   ,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot
   ,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot
   ,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot
   ,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot
   ,sum(jan_net) as jan_net
   ,sum(feb_net) as feb_net
   ,sum(mar_net) as mar_net
   ,sum(apr_net) as apr_net
   ,sum(may_net) as may_net
   ,sum(jun_net) as jun_net
   ,sum(jul_net) as jul_net
   ,sum(aug_net) as aug_net
   ,sum(sep_net) as sep_net
   ,sum(oct_net) as oct_net
   ,sum(nov_net) as nov_net
   ,sum(dec_net) as dec_net
 from (
    (select 
   w_warehouse_name
   ,w_warehouse_sq_ft
   ,w_city
   ,w_county
   ,w_state
   ,w_country
   ,'DHL' || ',' || 'ZOUROS' as ship_carriers
       ,d_year as year
   ,sum(case when d_moy = 1 
     then ws_ext_list_price* ws_quantity else 0 end) as jan_sales
   ,sum(case when d_moy = 2 
     then ws_ext_list_price* ws_quantity else 0 end) as feb_sales
   ,sum(case when d_moy = 3 
     then ws_ext_list_price* ws_quantity else 0 end) as mar_sales
   ,sum(case when d_moy = 4 
     then ws_ext_list_price* ws_quantity else 0 end) as apr_sales
   ,sum(case when d_moy = 5 
     then ws_ext_list_price* ws_quantity else 0 end) as may_sales
   ,sum(case when d_moy = 6 
     then ws_ext_list_price* ws_quantity else 0 end) as jun_sales
   ,sum(case when d_moy = 7 
     then ws_ext_list_price* ws_quantity else 0 end) as jul_sales
   ,sum(case when d_moy = 8 
     then ws_ext_list_price* ws_quantity else 0 end) as aug_sales
   ,sum(case when d_moy = 9 
     then ws_ext_list_price* ws_quantity else 0 end) as sep_sales
   ,sum(case when d_moy = 10 
     then ws_ext_list_price* ws_quantity else 0 end) as oct_sales
   ,sum(case when d_moy = 11
     then ws_ext_list_price* ws_quantity else 0 end) as nov_sales
   ,sum(case when d_moy = 12
     then ws_ext_list_price* ws_quantity else 0 end) as dec_sales
   ,sum(case when d_moy = 1 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as jan_net
   ,sum(case when d_moy = 2
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as feb_net
   ,sum(case when d_moy = 3 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as mar_net
   ,sum(case when d_moy = 4 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as apr_net
   ,sum(case when d_moy = 5 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as may_net
   ,sum(case when d_moy = 6 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as jun_net
   ,sum(case when d_moy = 7 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as jul_net
   ,sum(case when d_moy = 8 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as aug_net
   ,sum(case when d_moy = 9 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as sep_net
   ,sum(case when d_moy = 10 
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as oct_net
   ,sum(case when d_moy = 11
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as nov_net
   ,sum(case when d_moy = 12
     then ws_net_paid_inc_ship * ws_quantity else 0 end) as dec_net
     from
          web_sales
         ,warehouse
         ,date_dim
         ,time_dim
     ,ship_mode
     where
            ws_warehouse_sk =  w_warehouse_sk
        and ws_sold_date_sk = d_date_sk
        and ws_sold_time_sk = t_time_sk
   and ws_ship_mode_sk = sm_ship_mode_sk
        and d_year = 1999
   and t_time between 41070 and 41070+28800 
   and sm_carrier in ('DHL','ZOUROS')
     group by 
        w_warehouse_name
   ,w_warehouse_sq_ft
   ,w_city
   ,w_county
   ,w_state
   ,w_country
       ,d_year
   )
 union all
    (select 
   w_warehouse_name
   ,w_warehouse_sq_ft
   ,w_city
   ,w_county
   ,w_state
   ,w_country
   ,'DHL' || ',' || 'ZOUROS' as ship_carriers
       ,d_year as year
   ,sum(case when d_moy = 1 
     then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales
   ,sum(case when d_moy = 2 
     then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales
   ,sum(case when d_moy = 3 
     then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales
   ,sum(case when d_moy = 4 
     then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales
   ,sum(case when d_moy = 5 
     then cs_ext_sales_price* cs_quantity else 0 end) as may_sales
   ,sum(case when d_moy = 6 
     then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales
   ,sum(case when d_moy = 7 
     then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales
   ,sum(case when d_moy = 8 
     then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales
   ,sum(case when d_moy = 9 
     then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales
   ,sum(case when d_moy = 10 
     then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales
   ,sum(case when d_moy = 11
     then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales
   ,sum(case when d_moy = 12
     then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales
   ,sum(case when d_moy = 1 
     then cs_net_paid * cs_quantity else 0 end) as jan_net
   ,sum(case when d_moy = 2 
     then cs_net_paid * cs_quantity else 0 end) as feb_net
   ,sum(case when d_moy = 3 
     then cs_net_paid * cs_quantity else 0 end) as mar_net
   ,sum(case when d_moy = 4 
     then cs_net_paid * cs_quantity else 0 end) as apr_net
   ,sum(case when d_moy = 5 
     then cs_net_paid * cs_quantity else 0 end) as may_net
   ,sum(case when d_moy = 6 
     then cs_net_paid * cs_quantity else 0 end) as jun_net
   ,sum(case when d_moy = 7 
     then cs_net_paid * cs_quantity else 0 end) as jul_net
   ,sum(case when d_moy = 8 
     then cs_net_paid * cs_quantity else 0 end) as aug_net
   ,sum(case when d_moy = 9 
     then cs_net_paid * cs_quantity else 0 end) as sep_net
   ,sum(case when d_moy = 10 
     then cs_net_paid * cs_quantity else 0 end) as oct_net
   ,sum(case when d_moy = 11
     then cs_net_paid * cs_quantity else 0 end) as nov_net
   ,sum(case when d_moy = 12
     then cs_net_paid * cs_quantity else 0 end) as dec_net
     from
          catalog_sales
         ,warehouse
         ,date_dim
         ,time_dim
    ,ship_mode
     where
            cs_warehouse_sk =  w_warehouse_sk
        and cs_sold_date_sk = d_date_sk
        and cs_sold_time_sk = t_time_sk
   and cs_ship_mode_sk = sm_ship_mode_sk
        and d_year = 1999
   and t_time between 41070 AND 41070+28800 
   and sm_carrier in ('DHL','ZOUROS')
     group by 
        w_warehouse_name
   ,w_warehouse_sq_ft
   ,w_city
   ,w_county
   ,w_state
   ,w_country
       ,d_year
     ) 
 ) x
 group by 
        w_warehouse_name
   ,w_warehouse_sq_ft
   ,w_city
   ,w_county
   ,w_state
   ,w_country
   ,ship_carriers
       ,year
 order by w_warehouse_name
 limit 100;
