-- Test: TEST045 (CompGeneral)
-- Functionality: WITH clause and common subexpressions
-- Tables created: schema T045_CSES
-- Expected files: EXPECTED045
--
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@

obey TEST045(clnup);
log LOG045 clear;
obey TEST045(setup);
obey TEST045(ddl);
obey TEST045(queries);
obey TEST045(clnup);
log;
exit;

?section clnup

drop schema T045_CSES cascade;
cqd cse_cleanup_hive_tables 'on';
cleanup obsolete volatile tables;

?section setup

-- general setup
cqd hist_missing_stats_warning_level '0';
cqd hive_max_string_length_in_bytes '32';
cqd mode_special_4 'on';

create schema T045_CSES;
set schema T045_CSES;

prepare show_cses from
select case when operator in ('BLOCKED_UNION', 'HIVE_INSERT')
            then operator
            else 'SCAN TEMP' end as operator,
       count(*) as how_many
from table(explain(null, 'S'))
where operator = 'BLOCKED_UNION'
   or tname like '%CSE_TEMP_%'
group by 1
order by 1;

?section enable_cses;
cqd cse_for_with 'on';
cqd cse_use_temp 'on';
cqd cse_hive_temp_table 'on';
cqd cse_debug_warnings 'on';

?section disable_cses;
cqd cse_for_with 'off';
cqd cse_use_temp 'off';
cqd cse_hive_temp_table reset;
cqd cse_debug_warnings 'off';

--------------------------------------------------------------------
?section ddl
--------------------------------------------------------------------

create table store_sales
(
    ss_sold_date_sk           int,
    ss_sold_time_sk           int,
    ss_item_sk                int not null,
    ss_customer_sk            int,
    ss_cdemo_sk               int,
    ss_hdemo_sk               int,
    ss_addr_sk                int,
    ss_store_sk               int,
    ss_promo_sk               int,
    ss_ticket_number          int not null,
    ss_quantity               int,
    ss_wholesale_cost         float,
    ss_list_price             float,
    ss_sales_price            float,
    ss_ext_discount_amt       float,
    ss_ext_sales_price        float,
    ss_ext_wholesale_cost     float,
    ss_ext_list_price         float,
    ss_ext_tax                float,
    ss_coupon_amt             float,
    ss_net_paid               float,
    ss_net_paid_inc_tax       float,
    ss_net_profit             float,
        primary key(ss_item_sk,ss_ticket_number)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table store_returns
(
        sr_returned_date_sk     int,
        sr_return_time_sk       int,
        sr_item_sk              int not null,
        sr_customer_sk          int,
        sr_cdemo_sk             int,
        sr_hdemo_sk             int,
        sr_addr_sk              int,
        sr_store_sk             int,
        sr_reason_sk            int,
        sr_ticket_number        int not null,
        sr_return_quantity      int,
        sr_return_amt           float,
        sr_return_tax           float,
        sr_return_amt_inc_tax   float,
        sr_fee                  float,
        sr_return_ship_cost     float,
        sr_refunded_cash        float,
        sr_reversed_charge      float,
        sr_store_credit         float,
        sr_net_loss             float,
        primary key(sr_item_sk,sr_ticket_number)
        
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table catalog_sales
(
        cs_sold_date_sk         int,
        cs_sold_time_sk         int,
        cs_ship_date_sk         int,
        cs_bill_customer_sk     int,
        cs_bill_cdemo_sk        int,
        cs_bill_hdemo_sk        int,
        cs_bill_addr_sk         int,
        cs_ship_customer_sk     int,
        cs_ship_cdemo_sk        int,
        cs_ship_hdemo_sk        int,
        cs_ship_addr_sk         int,
        cs_call_center_sk       int,
        cs_catalog_page_sk      int,
        cs_ship_mode_sk         int,
        cs_warehouse_sk         int,
        cs_item_sk              int not null,
        cs_promo_sk             int,
        cs_order_number         int not null,
        cs_quantity             int,
        cs_wholesale_cost       float,
        cs_list_price           float,
        cs_sales_price          float,
        cs_ext_discount_amt     float,
        cs_ext_sales_price      float,
        cs_ext_wholesale_cost   float,
        cs_ext_list_price       float,
        cs_ext_tax              float,
        cs_coupon_amt           float,
        cs_ext_ship_cost        float,
        cs_net_paid             float,
        cs_net_paid_inc_tax     float,
        cs_net_paid_inc_ship     float,
        cs_net_paid_inc_ship_tax float,
        cs_net_profit            float,
        primary key(cs_item_sk,cs_order_number)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table  catalog_returns
(
    cr_returned_date_sk               int,
        cr_returned_time_sk           int,
        cr_item_sk                    int not null,
        cr_refunded_customer_sk       int,
        cr_refunded_cdemo_sk          int,
        cr_refunded_hdemo_sk          int,
        cr_refunded_addr_sk           int,
        cr_returning_customer_sk      int,
        cr_returning_cdemo_sk         int,
        cr_returning_hdemo_sk         int,
        cr_returning_addr_sk          int,
        cr_call_center_sk             int,
        cr_catalog_page_sk            int,
        cr_ship_mode_sk               int,
        cr_warehouse_sk               int,
        cr_reason_sk                  int,
        cr_order_number               int not null,
        cr_return_quantity            int,
        cr_return_amount              float,
        cr_return_tax                 float,
        cr_return_amt_inc_tax         float,
        cr_fee                        float,
        cr_return_ship_cost           float,
        cr_refunded_cash              float,
        cr_reversed_charge            float,
        cr_store_credit               float,
        cr_net_loss                   float,
        
        primary key(cr_item_sk,cr_order_number)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table  web_sales
(
        ws_sold_date_sk               int,
        ws_sold_time_sk               int,
        ws_ship_date_sk               int,
        ws_item_sk                    int not null,
        ws_bill_customer_sk           int,
        ws_bill_cdemo_sk              int,
        ws_bill_hdemo_sk              int,
        ws_bill_addr_sk               int,
        ws_ship_customer_sk           int,
        ws_ship_cdemo_sk              int,
        ws_ship_hdemo_sk              int,
        ws_ship_addr_sk               int,
        ws_web_page_sk                int,
        ws_web_site_sk                int,
        ws_ship_mode_sk               int,
        ws_warehouse_sk               int,
        ws_promo_sk                   int,
        ws_order_number               int not null,
        ws_quantity                   int,
        ws_wholesale_cost             float,
        ws_list_price                 float,
        ws_sales_price                float,
        ws_ext_discount_amt           float,
        ws_ext_sales_price            float,
        ws_ext_wholesale_cost         float,
        ws_ext_list_price             float,
        ws_ext_tax                    float,
        ws_coupon_amt                 float,
        ws_ext_ship_cost              float,
        ws_net_paid                   float,
        ws_net_paid_inc_tax           float,
        ws_net_paid_inc_ship          float,
        ws_net_paid_inc_ship_tax      float,
        ws_net_profit                 float,
        
        primary key (ws_item_sk,ws_order_number)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table web_returns
(
        wr_returned_date_sk           int,
        wr_returned_time_sk           int,
        wr_item_sk                    int not null,
        wr_refunded_customer_sk       int,
        wr_refunded_cdemo_sk          int,
        wr_refunded_hdemo_sk          int,
        wr_refunded_addr_sk           int,
        wr_returning_customer_sk      int,
        wr_returning_cdemo_sk         int,
        wr_returning_hdemo_sk         int,
        wr_returning_addr_sk          int,
        wr_web_page_sk                int,
        wr_reason_sk                  int,
        wr_order_number               int not null,
        wr_return_quantity            int,
        wr_return_amt                 float,
        wr_return_tax                 float,
        wr_return_amt_inc_tax         float,
        wr_fee                        float,
        wr_return_ship_cost           float,
        wr_refunded_cash              float,
        wr_reversed_charge            float,
        wr_account_credit             float,
        wr_net_loss                   float,
        primary key ( wr_order_number, wr_item_sk )

)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table store 
(
        s_store_sk                    int not null,
        s_store_id                    char(16),
        s_rec_start_date              date,
        s_rec_end_date                date,
        s_closed_date_sk              int,
        s_store_name                  varchar(50),
        s_number_employees            int,
        s_floor_space                 int,
        s_hours                       char(20),
        S_manager                     varchar(40),
        S_market_id                   int,
        S_geography_class             varchar(100),
        S_market_desc                 varchar(100),
        s_market_manager              varchar(40),
        s_division_id                 int,
        s_division_name               varchar(50),
        s_company_id                  int,
        s_company_name                varchar(50),
        s_street_number               varchar(10),
        s_street_name                 varchar(60),
        s_street_type                 char(15),
        s_suite_number                char(10),  --fix bug
        s_city                        varchar(60),
        s_county                      varchar(30),
        s_state                       char(2),
        s_zip                         char(10),
        s_country                     varchar(20),
        s_gmt_offset                  float,
        s_tax_percentage              float,
        
        primary key(s_store_sk)
        
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table Customer
(
        c_customer_sk                 int not null,
        c_customer_id                 char(16) CHARACTER SET UTF8 not null,
        c_current_cdemo_sk            int,
        c_current_hdemo_sk            int,
        c_current_addr_sk             int,
        c_first_shipto_date_sk        int,
        c_first_sales_date_sk         int,
        c_salutation                  char(10) CHARACTER SET UTF8,
        c_first_name                  char(20) CHARACTER SET UTF8,
        c_last_name                   char(30) CHARACTER SET UTF8,
        c_preferred_cust_flag         char(1),
        c_birth_day                   integer,
        c_birth_month                 integer,
        c_birth_year                  integer,
        c_birth_country               varchar(20) CHARACTER SET UTF8,
        c_login                       char(13) CHARACTER SET UTF8,
        c_email_address               char(50) CHARACTER SET UTF8,
        c_last_review_date_sk         int,
        primary key (c_customer_sk)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table Customer_address
(
        ca_address_sk                 int not null,
        ca_address_id                 char(16) not null,
        ca_street_number              char(10),
        ca_street_name                varchar(60),
        ca_street_type                char(15),
        ca_suite_number               char(10),
        ca_city                       varchar(60),
        ca_county varchar(30),
        ca_state char(2),
        ca_zip char(10),
        ca_country varchar(20),
        ca_gmt_offset decimal(5,2),
        ca_location_type char(20),
        primary key (ca_address_sk)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table Date_dim
(
        d_date_sk                     int not null,
        d_date_id                     char(16) not null,
        d_date                        date,
        d_month_seq                   integer,
        d_week_seq                    integer,
        d_quarter_seq                 integer,
        d_year                        integer,
        d_dow                         integer,
        d_moy                         integer,
        d_dom                         integer,
        d_qoy                         integer,
        d_fy_year                     integer,
        d_fy_quarter_seq              integer,
        d_fy_week_seq                 integer,
        d_day_name                    char(9),
        d_quarter_name                char(6),
        d_holiday                     char(1),
        d_weekend                     char(1),
        d_following_holiday           char(1),
        d_first_dom                   integer,
        d_last_dom                    integer,
        d_same_day_ly                 integer,
        d_same_day_lq                 integer,
        d_current_day                 char(1),
        d_current_week                char(1),
        d_current_month               char(1),
        d_current_quarter             char(1),
        d_current_year                char(1),
        primary key (d_date_sk)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

create table item
(
        i_item_sk                      int not null,
        i_item_id                      char(16) not null,
        i_rec_start_date               date,
        i_rec_end_date                 date,
        i_item_desc                    varchar(200),
        i_current_price                decimal(7,2),
        i_wholesale_cost               decimal(7,2),
        i_brand_id                     integer,
        i_brand                        char(50),
        i_class_id                     integer,
        i_class                        char(50),
        i_category_id                  integer,
        i_category                     char(50),
        i_manufact_id                  integer,
        i_manufact                     char(50),
        i_size                         char(20),
        i_formulation                  char(20),
        i_color                        char(20),
        i_units                        char(10),
        i_container                    char(10),
        i_manager_id                   integer,
        i_product_name                 char(50),
        primary key(i_item_sk)
)
--SALT USING $PARTITION_NUM PARTITIONS
--  HBASE_OPTIONS
--  (
--    DATA_BLOCK_ENCODING = 'FAST_DIFF',
--    COMPRESSION = 'SNAPPY'
--  )
;

insert into date_dim values (
  1, '1', date '2000-01-01', 1, 1, 1, 2000, 1, 1, 1, 1, 2000, 1, 1, 'aday', 'aq', ' ', ' ', ' ', 1, 31, 0, 0, ' ', ' ', ' ', ' ', ' '
);

insert into store_sales values (
  1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1.0, 1.0, 1.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 1.0, 1.0, 0.0
);

update statistics for table date_dim on every column;
update statistics for table date_dim on (d_qoy, d_year);
update statistics for table store_sales on every column;

--------------------------------------------------------------------
?section queries
--------------------------------------------------------------------

obey TEST045(enable_cses);

--------------------------------------------------------------------
-- test some queries on populated Hive tables that we already have
--------------------------------------------------------------------

set schema hive.hive;

prepare s from
with cse1 as (select count(*) from date_dim where d_dow = ?)
select * from cse1
union all
select * from cse1;
execute show_cses;
execute s using 1;
-- execute a second time
execute s using 2;

-- prepare a second time, so far not using query cache
prepare s from
with cse1 as (select count(*) from date_dim where d_dow = ?)
select * from cse1
union all
select * from cse1;
execute show_cses;
execute s using 3;
-- execute a second time
execute s using 4;

-- test subquery unnesting
cqd subquery_unnesting 'debug';

prepare s from
with cse1 as (select * from date_dim)
select count(*)
from cse1 x
where d_date_sk > (select avg(d_date_sk) from cse1 y where x.d_moy > y.d_moy);
-- currently not unnested
execute show_cses;

--------------------------------------------------------------------
-- test some TPC-DS queries
--------------------------------------------------------------------

set schema trafodion.t045_cses;

--QID: 1

prepare s from
with customer_total_return as
  (select sr_customer_sk as ctr_customer_sk
         ,sr_store_sk as ctr_store_sk
         ,sum(SR_REVERSED_CHARGE) as ctr_total_return
   from store_returns
       ,date_dim
   where sr_returned_date_sk = d_date_sk
         and d_year =2000
   group by sr_customer_sk
        ,sr_store_sk
  )

select  c_customer_id
from customer_total_return ctr1
    ,store
    ,customer
where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
                               from customer_total_return ctr2
                               where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
  and s_store_sk = ctr1.ctr_store_sk
  and s_state = 'SD'
  and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id
limit 100;
execute show_cses;
execute s;


--QID: 2

prepare s from
 with wscs as
 (select sold_date_sk
        ,sales_price
  from  (select ws_sold_date_sk sold_date_sk
               ,ws_ext_sales_price sales_price
          from web_sales 
         union all
         select cs_sold_date_sk sold_date_sk
               ,cs_ext_sales_price sales_price
         from catalog_sales) t),
 wswscs as 
 (select d_week_seq,
        sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
        sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
        sum(case when (d_day_name='Tuesday') then sales_price else  null end) tue_sales,
        sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
        sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
        sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
        sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
 from wscs
     ,date_dim
 where d_date_sk = sold_date_sk
 group by d_week_seq)
 select d_week_seq1
       ,round(sun_sales1/sun_sales2,2)
       ,round(mon_sales1/mon_sales2,2)
       ,round(tue_sales1/tue_sales2,2)
       ,round(wed_sales1/wed_sales2,2)
       ,round(thu_sales1/thu_sales2,2)
       ,round(fri_sales1/fri_sales2,2)
       ,round(sat_sales1/sat_sales2,2)
 from
 (select wswscs.d_week_seq d_week_seq1
        ,sun_sales sun_sales1
        ,mon_sales mon_sales1
        ,tue_sales tue_sales1
        ,wed_sales wed_sales1
        ,thu_sales thu_sales1
        ,fri_sales fri_sales1
        ,sat_sales sat_sales1
  from wswscs,date_dim 
  where date_dim.d_week_seq = wswscs.d_week_seq and
        d_year = 1999) y,
 (select wswscs.d_week_seq d_week_seq2
        ,sun_sales sun_sales2
        ,mon_sales mon_sales2
        ,tue_sales tue_sales2
        ,wed_sales wed_sales2
        ,thu_sales thu_sales2
        ,fri_sales fri_sales2
        ,sat_sales sat_sales2
  from wswscs
      ,date_dim 
  where date_dim.d_week_seq = wswscs.d_week_seq and
        d_year = 1999+1) z
 where d_week_seq1=d_week_seq2-53
 order by d_week_seq1;
-- use temp for wscs only, not wswscs, due to MapValueIds
execute show_cses;
execute s;


--QID: 4

prepare s from
with year_total as (
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total
       ,'s' sale_type
 from customer
     ,store_sales
     ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
 union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total
       ,'c' sale_type
 from customer
     ,catalog_sales
     ,date_dim
 where c_customer_sk = cs_bill_customer_sk
   and cs_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total
       ,'w' sale_type
 from customer
     ,web_sales
     ,date_dim
 where c_customer_sk = ws_bill_customer_sk
   and ws_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
select
      t_s_secyear.customer_id
     ,t_s_secyear.customer_first_name
     ,t_s_secyear.customer_last_name
     ,t_s_secyear.customer_birth_country
 from year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
 where t_s_secyear.customer_id = t_s_firstyear.customer_id
   and t_s_firstyear.customer_id = t_c_secyear.customer_id
   and t_s_firstyear.customer_id = t_c_firstyear.customer_id
   and t_s_firstyear.customer_id = t_w_firstyear.customer_id
   and t_s_firstyear.customer_id = t_w_secyear.customer_id
   and t_s_firstyear.sale_type = 's'
   and t_c_firstyear.sale_type = 'c'
   and t_w_firstyear.sale_type = 'w'
   and t_s_secyear.sale_type = 's'
   and t_c_secyear.sale_type = 'c'
   and t_w_secyear.sale_type = 'w'
   and t_s_firstyear.dyear =  1998
   and t_s_secyear.dyear = 1998+1
   and t_c_firstyear.dyear =  1998
   and t_c_secyear.dyear =  1998+1
   and t_w_firstyear.dyear = 1998
   and t_w_secyear.dyear = 1998+1
   and t_s_firstyear.year_total > 0
   and t_c_firstyear.year_total > 0
   and t_w_firstyear.year_total > 0
   and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
           > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
   and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
           > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
 order by t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
limit 100;
execute show_cses;
execute s;


--QID: 11

prepare s from
with year_total as (
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
       ,'s' sale_type
 from customer
     ,store_sales
     ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag 
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year 
 union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total
       ,'w' sale_type
 from customer
     ,web_sales
     ,date_dim
 where c_customer_sk = ws_bill_customer_sk
   and ws_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag 
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
select  
     t_s_secyear.customer_id
    ,t_s_secyear.customer_first_name
    ,t_s_secyear.customer_last_name
    ,t_s_secyear.customer_preferred_cust_flag
 from year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
 where t_s_secyear.customer_id = t_s_firstyear.customer_id
         and t_s_firstyear.customer_id = t_w_secyear.customer_id
         and t_s_firstyear.customer_id = t_w_firstyear.customer_id
         and t_s_firstyear.sale_type = 's'
         and t_w_firstyear.sale_type = 'w'
         and t_s_secyear.sale_type = 's'
         and t_w_secyear.sale_type = 'w'
         and t_s_firstyear.dyear = 2001
         and t_s_secyear.dyear = 2001+1
         and t_w_firstyear.dyear = 2001
         and t_w_secyear.dyear = 2001+1
         and t_s_firstyear.year_total > 0
         and t_w_firstyear.year_total > 0
         and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end
             > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end
 order by t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_preferred_cust_flag
limit 100;
execute show_cses;
execute s;

--QID: 14a

prepare s from
-- **************
-- VARIANT/ALTERNATIVE Version from TPC-DS (Rollout) is used
-- **************

with  cross_items as
 (select i_item_sk ss_item_sk
 from item,
 (select iss.i_brand_id brand_id
     ,iss.i_class_id class_id
     ,iss.i_category_id category_id
 from store_sales
     ,item iss
     ,date_dim d1
 where ss_item_sk = iss.i_item_sk
   and ss_sold_date_sk = d1.d_date_sk
   and d1.d_year between 1999 AND 1999 + 2
 intersect 
 select ics.i_brand_id
     ,ics.i_class_id
     ,ics.i_category_id
 from catalog_sales
     ,item ics
     ,date_dim d2
 where cs_item_sk = ics.i_item_sk
   and cs_sold_date_sk = d2.d_date_sk
   and d2.d_year between 1999 AND 1999 + 2
 intersect
 select iws.i_brand_id
     ,iws.i_class_id
     ,iws.i_category_id
 from web_sales
     ,item iws
     ,date_dim d3
 where ws_item_sk = iws.i_item_sk
   and ws_sold_date_sk = d3.d_date_sk
   and d3.d_year between 1999 AND 1999 + 2) x
 where i_brand_id = brand_id
      and i_class_id = class_id
      and i_category_id = category_id
)
,
 avg_sales as
 (select avg(quantity*list_price) average_sales
  from (select ss_quantity quantity
             ,ss_list_price list_price
       from store_sales
           ,date_dim
       where ss_sold_date_sk = d_date_sk
         and d_year between 1999 and 2001 
       union all 
       select cs_quantity quantity 
             ,cs_list_price list_price
       from catalog_sales
           ,date_dim
       where cs_sold_date_sk = d_date_sk
         and d_year between 1999 AND 1999 + 2
       union all
       select ws_quantity quantity
             ,ws_list_price list_price
       from web_sales
           ,date_dim
       where ws_sold_date_sk = d_date_sk
         and d_year between 1999 AND 1999 + 2) x)
,
  results AS
(select channel, i_brand_id, i_class_id, i_category_id, sum(sales) sum_sales, sum(number_sales) number_sales
 from (
       select 'store' channel, i_brand_id,i_class_id
             ,i_category_id,sum(ss_quantity*ss_list_price) sales
             , count(*) number_sales
       from store_sales
           ,item
           ,date_dim
       where ss_item_sk in (select ss_item_sk from cross_items)
         and ss_item_sk = i_item_sk
         and ss_sold_date_sk = d_date_sk
         and d_year = 1999 + 2 
         and d_moy = 11
       group by i_brand_id,i_class_id,i_category_id
       having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)
       union all
       select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales
       from catalog_sales
           ,item
           ,date_dim
       where cs_item_sk in (select ss_item_sk from cross_items)
         and cs_item_sk = i_item_sk
         and cs_sold_date_sk = d_date_sk
         and d_year = 1999 + 2
         and d_moy = 11
       group by i_brand_id,i_class_id,i_category_id
       having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales)
       union all
       select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales
       from web_sales
           ,item
           ,date_dim
       where ws_item_sk in (select ss_item_sk from cross_items)
         and ws_item_sk = i_item_sk
         and ws_sold_date_sk = d_date_sk
         and d_year = 1999 + 2
         and d_moy = 11
       group by i_brand_id,i_class_id,i_category_id
       having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales)
 ) y
 group by channel, i_brand_id,i_class_id,i_category_id)

select channel, i_brand_id, i_class_id, i_category_id, sum_sales, number_sales
from (
      select channel, i_brand_id, i_class_id, i_category_id, sum_sales, number_sales from results
      union
      select channel, i_brand_id, i_class_id,  null as i_category_id, sum(sum_sales), sum(number_sales) from results
      group by channel, i_brand_id, i_class_id
      union
      select channel, i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results
      group by channel, i_brand_id
      union
      select channel, null as i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results
      group by channel
      union
      select null as channel, null as i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results) z
order by channel, i_brand_id, i_class_id, i_category_id
 limit 100 ;
execute show_cses;
execute s;
 
--QID: 23a

prepare s from
 with frequent_ss_items as 
 (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
  from store_sales
      ,date_dim 
      ,item
  where ss_sold_date_sk = d_date_sk
    and ss_item_sk = i_item_sk 
    and d_year in (2000,2000+1,2000+2,2000+3)
  group by substr(i_item_desc,1,30),i_item_sk,d_date
  having count(*) >4),
 max_store_sales as
 (select max(csales) tpcds_cmax 
  from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
        from store_sales
            ,customer
            ,date_dim 
        where ss_customer_sk = c_customer_sk
         and ss_sold_date_sk = d_date_sk
             and d_year in (2000,2000+1,2000+2,2000+3)
        group by c_customer_sk)),
 best_ss_customer as
 (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
  from store_sales
      ,customer
  where ss_customer_sk = c_customer_sk
  group by c_customer_sk
  having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select
  *
from
 max_store_sales))
 select sum(sales)
 from (select cs_quantity*cs_list_price sales
       from catalog_sales
           ,date_dim 
       where d_year = 2000 
         and d_moy = 6 
         and cs_sold_date_sk = d_date_sk 
         and cs_item_sk in (select item_sk from frequent_ss_items)
         and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
      union all
      select ws_quantity*ws_list_price sales
       from web_sales 
           ,date_dim 
       where d_year = 2000 
         and d_moy = 6 
         and ws_sold_date_sk = d_date_sk 
         and ws_item_sk in (select item_sk from frequent_ss_items)
         and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) 
limit 100;
execute show_cses;
execute s;
 
--QID: 23b

prepare s from
 with frequent_ss_items as
 (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
  from store_sales
      ,date_dim
      ,item
  where ss_sold_date_sk = d_date_sk
    and ss_item_sk = i_item_sk
    and d_year in (2000,2000+1,2000+2,2000+3)
  group by substr(i_item_desc,1,30),i_item_sk,d_date
  having count(*) >4),
 max_store_sales as
 (select max(csales) tpcds_cmax
  from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
        from store_sales
            ,customer
            ,date_dim 
        where ss_customer_sk = c_customer_sk
         and ss_sold_date_sk = d_date_sk
         and d_year in (2000,2000+1,2000+2,2000+3)
        group by c_customer_sk)),
 best_ss_customer as
 (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
  from store_sales
      ,customer
  where ss_customer_sk = c_customer_sk
  group by c_customer_sk
  having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select
  *
 from max_store_sales))
 select  c_last_name,c_first_name,sales
 from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales
        from catalog_sales
            ,customer
            ,date_dim 
        where d_year = 2000 
         and d_moy = 6 
         and cs_sold_date_sk = d_date_sk 
         and cs_item_sk in (select item_sk from frequent_ss_items)
         and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
         and cs_bill_customer_sk = c_customer_sk 
       group by c_last_name,c_first_name
      union all
      select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales
       from web_sales
           ,customer
           ,date_dim 
       where d_year = 2000
         and d_moy = 6 
         and ws_sold_date_sk = d_date_sk 
         and ws_item_sk in (select item_sk from frequent_ss_items)
         and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)
         and ws_bill_customer_sk = c_customer_sk
       group by c_last_name,c_first_name) 
     order by c_last_name,c_first_name,sales
limit 100;
execute show_cses;
execute s;

--QID: 24a

prepare s from
with ssales as
(select c_last_name
      ,c_first_name
      ,s_store_name
      ,ca_state
      ,s_state
      ,i_color
      ,i_current_price
      ,i_manager_id
      ,i_units
      ,i_size
      ,sum(ss_net_paid) netpaid
from store_sales
    ,store_returns
    ,store
    ,item
    ,customer
    ,customer_address
where ss_ticket_number = sr_ticket_number
  and ss_item_sk = sr_item_sk
  and ss_customer_sk = c_customer_sk
  and ss_item_sk = i_item_sk
  and ss_store_sk = s_store_sk
  and c_birth_country = upper(ca_country)
  and s_zip = ca_zip
  and s_market_id = 10
group by c_last_name
        ,c_first_name
        ,s_store_name
        ,ca_state
        ,s_state
        ,i_color
        ,i_current_price
        ,i_manager_id
        ,i_units
        ,i_size)
select c_last_name
      ,c_first_name
      ,s_store_name
      ,sum(netpaid) paid
from ssales
where i_color = 'smoke'
group by c_last_name
        ,c_first_name
        ,s_store_name
having sum(netpaid) > (select 0.05*avg(netpaid)
                                 from ssales)
;
execute show_cses;
execute s;

--QID: 24b

prepare s from
with ssales as
(select c_last_name
      ,c_first_name
      ,s_store_name
      ,ca_state
      ,s_state
      ,i_color
      ,i_current_price
      ,i_manager_id
      ,i_units
      ,i_size
      ,sum(ss_net_paid) netpaid
from store_sales
    ,store_returns
    ,store
    ,item
    ,customer
    ,customer_address
where ss_ticket_number = sr_ticket_number
  and ss_item_sk = sr_item_sk
  and ss_customer_sk = c_customer_sk
  and ss_item_sk = i_item_sk
  and ss_store_sk = s_store_sk
  and c_birth_country = upper(ca_country)
  and s_zip = ca_zip
  and s_market_id=10
group by c_last_name
        ,c_first_name
        ,s_store_name
        ,ca_state
        ,s_state
        ,i_color
        ,i_current_price
        ,i_manager_id
        ,i_units
        ,i_size)
select c_last_name
      ,c_first_name
      ,s_store_name
      ,sum(netpaid) paid
from ssales
where i_color = 'pink'
group by c_last_name
        ,c_first_name
        ,s_store_name
having sum(netpaid) > (select 0.05*avg(netpaid)
                           from ssales)
;
execute show_cses;
execute s;

--QID: 30

prepare s from
 with customer_total_return as
 (select wr_returning_customer_sk as ctr_customer_sk
        ,ca_state as ctr_state, 
         sum(wr_return_amt) as ctr_total_return
 from web_returns
     ,date_dim
     ,customer_address
 where wr_returned_date_sk = d_date_sk 
   and d_year =1999
   and wr_returning_addr_sk = ca_address_sk 
 group by wr_returning_customer_sk
         ,ca_state)
select  c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag
       ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
       ,c_last_review_date_sk,ctr_total_return
 from customer_total_return ctr1
     ,customer_address
     ,customer
 where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
                                from customer_total_return ctr2 
                                where ctr1.ctr_state = ctr2.ctr_state)
       and ca_address_sk = c_current_addr_sk
       and ca_state = 'OK'
       and ctr1.ctr_customer_sk = c_customer_sk
 order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag
                  ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
                  ,c_last_review_date_sk,ctr_total_return
limit 100;
execute show_cses;
execute s;

--QID: 31

prepare s from
 with ss as
 (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
 from store_sales,date_dim,customer_address
 where ss_sold_date_sk = d_date_sk
  and ss_addr_sk=ca_address_sk
 group by ca_county,d_qoy, d_year),
 ws as
 (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
 from web_sales,date_dim,customer_address
 where ws_sold_date_sk = d_date_sk
  and ws_bill_addr_sk=ca_address_sk
 group by ca_county,d_qoy, d_year)
 select 
        ss1.ca_county
       ,ss1.d_year
       ,ws2.web_sales/ws1.web_sales web_q1_q2_increase
       ,ss2.store_sales/ss1.store_sales store_q1_q2_increase
       ,ws3.web_sales/ws2.web_sales web_q2_q3_increase
       ,ss3.store_sales/ss2.store_sales store_q2_q3_increase
 from
        ss ss1
       ,ss ss2
       ,ss ss3
       ,ws ws1
       ,ws ws2
       ,ws ws3
 where
    ss1.d_qoy = 1
    and ss1.d_year = 2002
    and ss1.ca_county = ss2.ca_county
    and ss2.d_qoy = 2
    and ss2.d_year = 2002
    and ss2.ca_county = ss3.ca_county
    and ss3.d_qoy = 3
    and ss3.d_year = 2002
    and ss1.ca_county = ws1.ca_county
    and ws1.d_qoy = 1
    and ws1.d_year = 2002
    and ws1.ca_county = ws2.ca_county
    and ws2.d_qoy = 2
    and ws2.d_year = 2002
    and ws1.ca_county = ws3.ca_county
    and ws3.d_qoy = 3
    and ws3.d_year =2002
    and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end 
       > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end
    and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end
       > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end
 order by store_q1_q2_increase;
-- Different constants used in different references of WITH clause - not yet supported
execute show_cses;
execute s;
