文档章节

Tpc-ds测试以及结果

d
 donkeylala
发布于 2016/12/22 15:28
字数 17301
阅读 105
收藏 0

1.1 背景介绍

TPC-DS是衡量决策支持解决方案的性能,包括事实上的行业标准,但不限于,大数据系统。

目前版本为V2。它模型的几个一般适用的方面的决策支持系统,包括查询和数据维护。

虽然TPC-DS基本的商业模式是零售产品供应商、数据库架构、数据查询、数据维护人口模型和实施规则被设计成具有广泛代表性的现代决策支持系统。

1.2 测试目的及重点

本测试旨在测试spark2.0的sql兼容性及性能测试,主要对由工具生成的99个sql进行测试。

 

1.3 下载

从官网http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp

下载并解压至hadoop集群

Ps:因为无法从虚拟终端无法复制,多以截图为主!

1.4 编译

进入到tools目录

 

# make -f Makefile.suite

编译成功后会生成dsdgen和dsqgen两个文件

 

1.5 生成数据

Scale 以GB作为单位

 

 

1.6 生成sql

 

# vi hive.tpl

在最后一行加上define _END =””;

 

创建存储sql的文件夹

# cd ..

# mkdir sql

生成sql样例

# cd tools

# for i in {1..99}; do ./dsqgen -template ../query_templates/querry$i.tpl -directory ../query_templates/ -dialect hive -scale 1 -output ../sql; mv /home/hadoop/ly_tpc_ds/v2.3.0.pc_bak/sql/querrt_0.sql;done

 

 

1.1 建表及导入数据

create table store_sales

(

  ss_sold_date_sk           bigint,

  ss_sold_time_sk           bigint,

  ss_item_sk                bigint,

  ss_customer_sk            bigint,

  ss_cdemo_sk               bigint,

  ss_hdemo_sk               bigint,

  ss_addr_sk                bigint,

  ss_store_sk               bigint,

  ss_promo_sk               bigint,

  ss_ticket_number          bigint,

  ss_quantity               int,

  ss_wholesale_cost         decimal(7,2),

  ss_list_price             decimal(7,2),

  ss_sales_price            decimal(7,2),

  ss_ext_discount_amt       decimal(7,2),

  ss_ext_sales_price        decimal(7,2),

  ss_ext_wholesale_cost     decimal(7,2),

  ss_ext_list_price         decimal(7,2),

  ss_ext_tax                decimal(7,2),

  ss_coupon_amt             decimal(7,2),

  ss_net_paid               decimal(7,2),

  ss_net_paid_inc_tax       decimal(7,2),

  ss_net_profit             decimal(7,2)

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/store_sales.dat' overwrite into table store_sales;

create  table customer_demographics

(

  cd_demo_sk                bigint,

  cd_gender                 string,

  cd_marital_status         string,

  cd_education_status       string,

  cd_purchase_estimate      int,

  cd_credit_rating          string,

  cd_dep_count              int,

  cd_dep_employed_count     int,

  cd_dep_college_count      int

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/customer_demographics.dat' overwrite into table customer_demographics;

create  table date_dim

(

  d_date_sk                 bigint,

  d_date_id                 string,

  d_date                    string, -- YYYY-MM-DD format

  d_month_seq               int,

  d_week_seq                int,

  d_quarter_seq             int,

  d_year                    int,

  d_dow                     int,

  d_moy                     int,

  d_dom                     int,

  d_qoy                     int,

  d_fy_year                 int,

  d_fy_quarter_seq          int,

  d_fy_week_seq             int,

  d_day_name                string,

  d_quarter_name            string,

  d_holiday                 string,

  d_weekend                 string,

  d_following_holiday       string,

  d_first_dom               int,

  d_last_dom                int,

  d_same_day_ly             int,

  d_same_day_lq             int,

  d_current_day             string,

  d_current_week            string,

  d_current_month           string,

  d_current_quarter         string,

  d_current_year            string

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/date_dim.dat' overwrite into table date_dim;

create  table item

(

  i_item_sk                 bigint,

  i_item_id                 string,

  i_rec_start_date          string,

  i_rec_end_date            string,

  i_item_desc               string,

  i_current_price           decimal(7,2),

  i_wholesale_cost          decimal(7,2),

  i_brand_id                int,

  i_brand                   string,

  i_class_id                int,

  i_class                   string,

  i_category_id             int,

  i_category                string,

  i_manufact_id             int,

  i_manufact                string,

  i_size                    string,

  i_formulation             string,

  i_color                   string,

  i_units                   string,

  i_container               string,

  i_manager_id              int,

  i_product_name            string

)

row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE

;

load data inpath '/data_1tb/item.dat' overwrite into table item;

create table store

(

  s_store_sk                bigint,

  s_store_id                string,

  s_rec_start_date          string,

  s_rec_end_date            string,

  s_closed_date_sk          bigint,

  s_store_name              string,

  s_number_employees        int,

  s_floor_space             int,

  s_hours                   string,

  s_manager                 string,

  s_market_id               int,

  s_geography_class         string,

  s_market_desc             string,

  s_market_manager          string,

  s_division_id             int,

  s_division_name           string,

  s_company_id              int,

  s_company_name            string,

  s_street_number           string,

  s_street_name             string,

  s_street_type             string,

  s_suite_number            string,

  s_city                    string,

  s_county                  string,

  s_state                   string,

  s_zip                     string,

  s_country                 string,

  s_gmt_offset              decimal(5,2),

  s_tax_precentage          decimal(5,2)

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/store.dat' overwrite into table store;

create table customer

(

  c_customer_sk             bigint,

  c_customer_id             string,

  c_current_cdemo_sk        bigint,

  c_current_hdemo_sk        bigint,

  c_current_addr_sk         bigint,

  c_first_shipto_date_sk    bigint,

  c_first_sales_date_sk     bigint,

  c_salutation              string,

  c_first_name              string,

  c_last_name               string,

  c_preferred_cust_flag     string,

  c_birth_day               int,

  c_birth_month             int,

  c_birth_year              int,

  c_birth_country           string,

  c_login                   string,

  c_email_address           string,

  c_last_review_date        string

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE

;

load data inpath '/data_1tb/customer.dat' overwrite into table customer;

create  table promotion

(

  p_promo_sk                bigint,

  p_promo_id                string,

  p_start_date_sk           bigint,

  p_end_date_sk             bigint,

  p_item_sk                 bigint,

  p_cost                    decimal(15,2),

  p_response_target         int,

  p_promo_name              string,

  p_channel_dmail           string,

  p_channel_email           string,

  p_channel_catalog         string,

  p_channel_tv              string,

  p_channel_radio           string,

  p_channel_press           string,

  p_channel_event           string,

  p_channel_demo            string,

  p_channel_details         string,

  p_purpose                 string,

  p_discount_active         string

)

row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE

;

load data inpath '/data_1tb/promotion.dat' overwrite into table promotion;

 

create table household_demographics

(

  hd_demo_sk                bigint,

  hd_income_band_sk         bigint,

  hd_buy_potential          string,

  hd_dep_count              int,

  hd_vehicle_count          int

)

row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/household_demographics.dat' overwrite into table household_demographics;

create  table customer_address

(

  ca_address_sk             bigint,

  ca_address_id             string,

  ca_street_number          string,

  ca_street_name            string,

  ca_street_type            string,

  ca_suite_number           string,

  ca_city                   string,

  ca_county                 string,

  ca_state                  string,

  ca_zip                    string,

  ca_country                string,

  ca_gmt_offset             decimal(5,2),

  ca_location_type          string

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/customer_address.dat' overwrite into table customer_address;

create table time_dim

(

  t_time_sk                 bigint,

  t_time_id                 string,

  t_time                    int,

  t_hour                    int,

  t_minute                  int,

  t_second                  int,

  t_am_pm                   string,

  t_shift                   string,

  t_sub_shift               string,

  t_meal_time               string

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/time_dim.dat' overwrite into table time_dim;

create table dbgen_version

(

    dv_version                string                   ,

    dv_create_date            string                          ,

    dv_create_time            string                           ,

    dv_cmdline_args           string

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/dbgen_version.dat' overwrite into table dbgen_version;

create table warehouse

(

    w_warehouse_sk            int               ,

    w_warehouse_id            string              ,

    w_warehouse_name          string                   ,

    w_warehouse_sq_ft         int                       ,

    w_street_number           string                      ,

    w_street_name             string                   ,

    w_street_type           string                   ,

    w_suite_number           string                      ,

    w_city                  string                   ,

    w_county                 string                   ,

    w_state                  string                      ,

    w_zip                     string                      ,

    w_country                string                   ,

    w_gmt_offset              string                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/warehouse.dat' overwrite into table warehouse;

create table ship_mode

(

    sm_ship_mode_sk           int              ,

    sm_ship_mode_id            string               ,

    sm_type                    string                       ,

    sm_code                    string                       ,

    sm_carrier                string                       ,

    sm_contract                string                         

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/ship_mode.dat' overwrite into table ship_mode;

create table reason

(

    r_reason_sk               int              ,

    r_reason_id               string              ,

    r_reason_desc             string                     

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/reason.dat' overwrite into table reason;

create table income_band

(

    ib_income_band_sk         int              ,

    ib_lower_bound            int                       ,

    ib_upper_bound            int                       

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/income_band.dat' overwrite into table income_band;

create table call_center

(

    cc_call_center_sk         int             ,

    cc_call_center_id         string              ,

    cc_rec_start_date         string                            ,

    cc_rec_end_date           string                            ,

    cc_closed_date_sk         int                      ,

    cc_open_date_sk           int                       ,

    cc_name                   string                     ,

    cc_class                  string                     ,

    cc_employees              int                       ,

    cc_sq_ft                  int                      ,

    cc_hours                  string                        ,

    cc_manager                string                     ,

    cc_mkt_id                 int                       ,

    cc_mkt_class              string                       ,

    cc_mkt_desc               string                   ,

    cc_market_manager         string                     ,

    cc_division               int                       ,

    cc_division_name          string                     ,

    cc_company                int                       ,

    cc_company_name          string                        ,

    cc_street_number          string                        ,

    cc_street_name            string                    ,

    cc_street_type           string                        ,

    cc_suite_number           string                        ,

    cc_city                   string                     ,

    cc_county                 string                     ,

    cc_state                  string                         ,

    cc_zip                   string                        ,

    cc_country                string                     ,

    cc_gmt_offset             decimal(5,2)                  ,

    cc_tax_percentage         decimal(5,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/call_center.dat' overwrite into table call_center;

create table web_site

(

    web_site_sk               int              ,

    web_site_id               string              ,

    web_rec_start_date        string                           ,

    web_rec_end_date          string                        ,

    web_name                  string                    ,

    web_open_date_sk          int                       ,

    web_close_date_sk         int                       ,

    web_class                 string                    ,

    web_manager              string                ,

    web_mkt_id                int                       ,

    web_mkt_class             string                    ,

    web_mkt_desc              string                   ,

    web_market_manager        string                    ,

    web_company_id            int                       ,

    web_company_name          string                      ,

    web_street_number         string                       ,

    web_street_name           string                   ,

    web_street_type          string                     ,

    web_suite_number         string                      ,

    web_city                 string                    ,

    web_county               string                    ,

    web_state                 string                       ,

    web_zip                  string                       ,

    web_country               string                    ,

    web_gmt_offset            decimal(5,2)                  ,

    web_tax_percentage        decimal(5,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/web_site.dat' overwrite into table web_site;

create table store_returns

(

    sr_returned_date_sk       int                       ,

    sr_return_time_sk         int                       ,

    sr_item_sk                int               ,

    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               ,

    sr_return_quantity        int                       ,

    sr_return_amt             decimal(7,2)                  ,

    sr_return_tax             decimal(7,2)                  ,

    sr_return_amt_inc_tax     decimal(7,2)                  ,

    sr_fee                    decimal(7,2)                  ,

    sr_return_ship_cost       decimal(7,2)                  ,

    sr_refunded_cash          decimal(7,2)                  ,

    sr_reversed_charge        decimal(7,2)                  ,

    sr_store_credit           decimal(7,2)                  ,

    sr_net_loss               decimal(7,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/store_returns.dat' overwrite into table store_returns;

create table web_page

(

    wp_web_page_sk            int               ,

    wp_web_page_id           string               ,

    wp_rec_start_date         string                           ,

    wp_rec_end_date           string                           ,

    wp_creation_date_sk       int                       ,

    wp_access_date_sk         int                       ,

    wp_autogen_flag          string                        ,

    wp_customer_sk            int                       ,

    wp_url                    string                   ,

    wp_type                   string                     ,

    wp_char_count             int                       ,

    wp_link_count             int                       ,

    wp_image_count            int                       ,

    wp_max_ad_count           int                       

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/web_page.dat' overwrite into table web_page;

create table catalog_page

(

    cp_catalog_page_sk        int               ,

    cp_catalog_page_id        string             ,

    cp_start_date_sk          int                       ,

    cp_end_date_sk            int                       ,

    cp_department             string                    ,

    cp_catalog_number         int                       ,

    cp_catalog_page_number    int                       ,

    cp_description            string                   ,

    cp_type                   string                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/catalog_page.dat' overwrite into table catalog_page;

create table catalog_returns

(

    cr_returned_date_sk       int                       ,

    cr_returned_time_sk       int                       ,

    cr_item_sk                int               ,

    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               ,

    cr_return_quantity        int                       ,

    cr_return_amount          decimal(7,2)                  ,

    cr_return_tax             decimal(7,2)                  ,

    cr_return_amt_inc_tax     decimal(7,2)                  ,

    cr_fee                    decimal(7,2)                  ,

    cr_return_ship_cost       decimal(7,2)                  ,

    cr_refunded_cash          decimal(7,2)                  ,

    cr_reversed_charge        decimal(7,2)                  ,

    cr_store_credit           decimal(7,2)                  ,

    cr_net_loss               decimal(7,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/catalog_returns.dat' overwrite into table catalog_returns;

create table web_returns

(

    wr_returned_date_sk       int                       ,

    wr_returned_time_sk       int                       ,

    wr_item_sk                int               ,

    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               ,

    wr_return_quantity        int                       ,

    wr_return_amt             decimal(7,2)                  ,

    wr_return_tax             decimal(7,2)                  ,

    wr_return_amt_inc_tax     decimal(7,2)                  ,

    wr_fee                    decimal(7,2)                  ,

    wr_return_ship_cost       decimal(7,2)                  ,

    wr_refunded_cash          decimal(7,2)                  ,

    wr_reversed_charge        decimal(7,2)                  ,

    wr_account_credit         decimal(7,2)                  ,

    wr_net_loss               decimal(7,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/web_returns.dat' overwrite into table web_returns;

create table web_sales

(

    ws_sold_date_sk           int                       ,

    ws_sold_time_sk           int                       ,

    ws_ship_date_sk           int                       ,

    ws_item_sk                int               ,

    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               ,

    ws_quantity               int                       ,

    ws_wholesale_cost         decimal(7,2)                  ,

    ws_list_price             decimal(7,2)                  ,

    ws_sales_price            decimal(7,2)                  ,

    ws_ext_discount_amt       decimal(7,2)                  ,

    ws_ext_sales_price        decimal(7,2)                  ,

    ws_ext_wholesale_cost     decimal(7,2)                  ,

    ws_ext_list_price         decimal(7,2)                  ,

    ws_ext_tax                decimal(7,2)                  ,

    ws_coupon_amt             decimal(7,2)                  ,

    ws_ext_ship_cost          decimal(7,2)                  ,

    ws_net_paid               decimal(7,2)                  ,

    ws_net_paid_inc_tax       decimal(7,2)                  ,

    ws_net_paid_inc_ship      decimal(7,2)                  ,

    ws_net_paid_inc_ship_tax  decimal(7,2)                  ,

    ws_net_profit             decimal(7,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/web_sales.dat' overwrite into table web_sales;

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               ,

    cs_promo_sk               int                       ,

    cs_order_number           int               ,

    cs_quantity               int                       ,

    cs_wholesale_cost         decimal(7,2)                  ,

    cs_list_price             decimal(7,2)                  ,

    cs_sales_price            decimal(7,2)                  ,

    cs_ext_discount_amt       decimal(7,2)                  ,

    cs_ext_sales_price        decimal(7,2)                  ,

    cs_ext_wholesale_cost     decimal(7,2)                  ,

    cs_ext_list_price         decimal(7,2)                  ,

    cs_ext_tax                decimal(7,2)                  ,

    cs_coupon_amt             decimal(7,2)                  ,

    cs_ext_ship_cost          decimal(7,2)                  ,

    cs_net_paid               decimal(7,2)                  ,

    cs_net_paid_inc_tax       decimal(7,2)                  ,

    cs_net_paid_inc_ship      decimal(7,2)                  ,

    cs_net_paid_inc_ship_tax  decimal(7,2)                  ,

    cs_net_profit             decimal(7,2)                  

)row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/catalog_sales.dat' overwrite into table catalog_sales;

CREATE TABLE inventory(

  inv_date_sk bigint,

  inv_item_sk bigint,

  inv_warehouse_sk bigint,

  inv_quantity_on_hand int

  )row format delimited fields terminated by '|'

NULL DEFINED AS ''

STORED AS TEXTFILE;

load data inpath '/data_1tb/inventory.dat' overwrite into table inventory;

 

 

 

with customer_total_return as

(select sr_customer_sk as ctr_customer_sk

,sr_store_sk as ctr_store_sk

,sum( text({"SR_RETURN_AMT",1},{"SR_FEE",1},{"SR_REFUNDED_CASH",1},{"SR_RETURN_AMT_INC_TAX",1},{"SR_REVERSED_CHARGE",1},{"SR_STORE_CREDIT",1},{"SR_RETURN_TAX",1});) as ctr_total_return

from store_returns

,date_dim

where sr_returned_date_sk = d_date_sk

and d_year =random(1998, 2002, uniform);

group by sr_customer_sk

,sr_store_sk)

[100A] select [100B] 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 = 'distmember(fips_county, random(1, rowcount("active_counties", "store"), uniform);, 3);'

and ctr1.ctr_customer_sk = c_customer_sk

order by c_customer_id

[100C];

测试

1.1 原sql

Q1.sql

 

with customer_total_return as

(select sr_customer_sk as ctr_customer_sk

,sr_store_sk as ctr_store_sk

,sum(SR_FEE) 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 * from ( 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 = 'TN'

and ctr1.ctr_customer_sk = c_customer_sk

order by c_customer_id

 ) A limit  100;

 

Q2.sql

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) ) ,

 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 = 2001) 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 = 2001+1) z

 where d_week_seq1=d_week_seq2-53

 order by d_week_seq1;

 

Q3.sql

select * from (select  dt.d_year

       ,item.i_brand_id brand_id

       ,item.i_brand brand

       ,sum(ss_ext_sales_price) sum_agg

 from  date_dim dt

      ,store_sales

      ,item

 where dt.d_date_sk = store_sales.ss_sold_date_sk

   and store_sales.ss_item_sk = item.i_item_sk

   and item.i_manufact_id = 436

   and dt.d_moy=12

 group by dt.d_year

      ,item.i_brand

      ,item.i_brand_id

 order by dt.d_year

         ,sum_agg desc

         ,brand_id

  ) A limit  100;

 

 

Q4.sql

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 * from (  select  

                  t_s_secyear.customer_id

                 ,t_s_secyear.customer_first_name

                 ,t_s_secyear.customer_last_name

                 ,t_s_secyear.customer_email_address

 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 =  2001

   and t_s_secyear.dyear = 2001+1

   and t_c_firstyear.dyear =  2001

   and t_c_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_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_email_address

 ) A limit  100;

 

Q5.sql

with ssr as

 (select s_store_id,

        sum(sales_price) as sales,

        sum(profit) as profit,

        sum(return_amt) as returns,

        sum(net_loss) as profit_loss

 from

  ( select  ss_store_sk as store_sk,

            ss_sold_date_sk  as date_sk,

            ss_ext_sales_price as sales_price,

            ss_net_profit as profit,

            cast(0 as decimal(7,2)) as return_amt,

            cast(0 as decimal(7,2)) as net_loss

    from store_sales

    union all

    select sr_store_sk as store_sk,

           sr_returned_date_sk as date_sk,

           cast(0 as decimal(7,2)) as sales_price,

           cast(0 as decimal(7,2)) as profit,

           sr_return_amt as return_amt,

           sr_net_loss as net_loss

    from store_returns

   ) salesreturns,

     date_dim,

     store

 where date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  14 days)

       and store_sk = s_store_sk

 group by s_store_id)

 ,

 csr as

 (select cp_catalog_page_id,

        sum(sales_price) as sales,

        sum(profit) as profit,

        sum(return_amt) as returns,

        sum(net_loss) as profit_loss

 from

  ( select  cs_catalog_page_sk as page_sk,

            cs_sold_date_sk  as date_sk,

            cs_ext_sales_price as sales_price,

            cs_net_profit as profit,

            cast(0 as decimal(7,2)) as return_amt,

            cast(0 as decimal(7,2)) as net_loss

    from catalog_sales

    union all

    select cr_catalog_page_sk as page_sk,

           cr_returned_date_sk as date_sk,

           cast(0 as decimal(7,2)) as sales_price,

           cast(0 as decimal(7,2)) as profit,

           cr_return_amount as return_amt,

           cr_net_loss as net_loss

    from catalog_returns

   ) salesreturns,

     date_dim,

     catalog_page

 where date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  14 days)

       and page_sk = cp_catalog_page_sk

 group by cp_catalog_page_id)

 ,

 wsr as

 (select web_site_id,

        sum(sales_price) as sales,

        sum(profit) as profit,

        sum(return_amt) as returns,

        sum(net_loss) as profit_loss

 from

  ( select  ws_web_site_sk as wsr_web_site_sk,

            ws_sold_date_sk  as date_sk,

            ws_ext_sales_price as sales_price,

            ws_net_profit as profit,

            cast(0 as decimal(7,2)) as return_amt,

            cast(0 as decimal(7,2)) as net_loss

    from web_sales

    union all

    select ws_web_site_sk as wsr_web_site_sk,

           wr_returned_date_sk as date_sk,

           cast(0 as decimal(7,2)) as sales_price,

           cast(0 as decimal(7,2)) as profit,

           wr_return_amt as return_amt,

           wr_net_loss as net_loss

    from web_returns left outer join web_sales on

         ( wr_item_sk = ws_item_sk

           and wr_order_number = ws_order_number)

   ) salesreturns,

     date_dim,

     web_site

 where date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  14 days)

       and wsr_web_site_sk = web_site_sk

 group by web_site_id)

 select * from ( select  channel

        , id

        , sum(sales) as sales

        , sum(returns) as returns

        , sum(profit) as profit

 from

 (select 'store channel' as channel

        , 'store' || s_store_id as id

        , sales

        , returns

        , (profit - profit_loss) as profit

 from   ssr

 union all

 select 'catalog channel' as channel

        , 'catalog_page' || cp_catalog_page_id as id

        , sales

        , returns

        , (profit - profit_loss) as profit

 from  csr

 union all

 select 'web channel' as channel

        , 'web_site' || web_site_id as id

        , sales

        , returns

        , (profit - profit_loss) as profit

 from   wsr

 ) x

 group by rollup (channel, id)

 order by channel

         ,id

  ) A limit  100;

Q6.sql

select * from (select  a.ca_state state, count(*) cnt

 from customer_address a

     ,customer c

     ,store_sales s

     ,date_dim d

     ,item i

 where       a.ca_address_sk = c.c_current_addr_sk

  and c.c_customer_sk = s.ss_customer_sk

  and s.ss_sold_date_sk = d.d_date_sk

  and s.ss_item_sk = i.i_item_sk

  and d.d_month_seq =

       (select distinct (d_month_seq)

        from date_dim

               where d_year = 2000

          and d_moy = 2 )

  and i.i_current_price > 1.2 *

             (select avg(j.i_current_price)

       from item j

       where j.i_category = i.i_category)

 group by a.ca_state

 having count(*) >= 10

 order by cnt

  ) A limit  100;

Q7.sql

select * from (select  i_item_id,

        avg(ss_quantity) agg1,

        avg(ss_list_price) agg2,

        avg(ss_coupon_amt) agg3,

        avg(ss_sales_price) agg4

 from store_sales, customer_demographics, date_dim, item, promotion

 where ss_sold_date_sk = d_date_sk and

       ss_item_sk = i_item_sk and

       ss_cdemo_sk = cd_demo_sk and

       ss_promo_sk = p_promo_sk and

       cd_gender = 'F' and

       cd_marital_status = 'W' and

       cd_education_status = 'Primary' and

       (p_channel_email = 'N' or p_channel_event = 'N') and

       d_year = 1998

 group by i_item_id

 order by i_item_id

  ) A limit  100;

Q8.sql

select * from (select  s_store_name

      ,sum(ss_net_profit)

 from store_sales

     ,date_dim

     ,store,

     (select ca_zip

     from (

      SELECT substr(ca_zip,1,5) ca_zip

      FROM customer_address

      WHERE substr(ca_zip,1,5) IN (

                          '89436','30868','65085','22977','83927','77557',

                          '58429','40697','80614','10502','32779',

                          '91137','61265','98294','17921','18427',

                          '21203','59362','87291','84093','21505',

                          '17184','10866','67898','25797','28055',

                          '18377','80332','74535','21757','29742',

                          '90885','29898','17819','40811','25990',

                          '47513','89531','91068','10391','18846',

                          '99223','82637','41368','83658','86199',

                          '81625','26696','89338','88425','32200',

                          '81427','19053','77471','36610','99823',

                          '43276','41249','48584','83550','82276',

                          '18842','78890','14090','38123','40936',

                          '34425','19850','43286','80072','79188',

                          '54191','11395','50497','84861','90733',

                          '21068','57666','37119','25004','57835',

                          '70067','62878','95806','19303','18840',

                          '19124','29785','16737','16022','49613',

                          '89977','68310','60069','98360','48649',

                          '39050','41793','25002','27413','39736',

                          '47208','16515','94808','57648','15009',

                          '80015','42961','63982','21744','71853',

                          '81087','67468','34175','64008','20261',

                          '11201','51799','48043','45645','61163',

                          '48375','36447','57042','21218','41100',

                          '89951','22745','35851','83326','61125',

                          '78298','80752','49858','52940','96976',

                          '63792','11376','53582','18717','90226',

                          '50530','94203','99447','27670','96577',

                          '57856','56372','16165','23427','54561',

                          '28806','44439','22926','30123','61451',

                          '92397','56979','92309','70873','13355',

                          '21801','46346','37562','56458','28286',

                          '47306','99555','69399','26234','47546',

                          '49661','88601','35943','39936','25632',

                          '24611','44166','56648','30379','59785',

                          '11110','14329','93815','52226','71381',

                          '13842','25612','63294','14664','21077',

                          '82626','18799','60915','81020','56447',

                          '76619','11433','13414','42548','92713',

                          '70467','30884','47484','16072','38936',

                          '13036','88376','45539','35901','19506',

                          '65690','73957','71850','49231','14276',

                          '20005','18384','76615','11635','38177',

                          '55607','41369','95447','58581','58149',

                          '91946','33790','76232','75692','95464',

                          '22246','51061','56692','53121','77209',

                          '15482','10688','14868','45907','73520',

                          '72666','25734','17959','24677','66446',

                          '94627','53535','15560','41967','69297',

                          '11929','59403','33283','52232','57350',

                          '43933','40921','36635','10827','71286',

                          '19736','80619','25251','95042','15526',

                          '36496','55854','49124','81980','35375',

                          '49157','63512','28944','14946','36503',

                          '54010','18767','23969','43905','66979',

                          '33113','21286','58471','59080','13395',

                          '79144','70373','67031','38360','26705',

                          '50906','52406','26066','73146','15884',

                          '31897','30045','61068','45550','92454',

                          '13376','14354','19770','22928','97790',

                          '50723','46081','30202','14410','20223',

                          '88500','67298','13261','14172','81410',

                          '93578','83583','46047','94167','82564',

                          '21156','15799','86709','37931','74703',

                          '83103','23054','70470','72008','49247',

                          '91911','69998','20961','70070','63197',

                          '54853','88191','91830','49521','19454',

                          '81450','89091','62378','25683','61869',

                          '51744','36580','85778','36871','48121',

                          '28810','83712','45486','67393','26935',

                          '42393','20132','55349','86057','21309',

                          '80218','10094','11357','48819','39734',

                          '40758','30432','21204','29467','30214',

                          '61024','55307','74621','11622','68908',

                          '33032','52868','99194','99900','84936',

                          '69036','99149','45013','32895','59004',

                          '32322','14933','32936','33562','72550',

                          '27385','58049','58200','16808','21360',

                          '32961','18586','79307','15492')

     intersect

      select ca_zip

      from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt

            FROM customer_address, customer

            WHERE ca_address_sk = c_current_addr_sk and

                  c_preferred_cust_flag='Y'

            group by ca_zip

            having count(*) > 10)A1)A2) V1

 where ss_store_sk = s_store_sk

  and ss_sold_date_sk = d_date_sk

  and d_qoy = 1 and d_year = 2002

  and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))

 group by s_store_name

 order by s_store_name

  ) A limit  100;

Q9.sql

select case when (select count(*)

                  from store_sales

                  where ss_quantity between 1 and 20) > 25437

            then (select avg(ss_ext_discount_amt)

                  from store_sales

                  where ss_quantity between 1 and 20)

            else (select avg(ss_net_profit)

                  from store_sales

                  where ss_quantity between 1 and 20) end bucket1 ,

       case when (select count(*)

                  from store_sales

                  where ss_quantity between 21 and 40) > 22746

            then (select avg(ss_ext_discount_amt)

                  from store_sales

                  where ss_quantity between 21 and 40)

            else (select avg(ss_net_profit)

                  from store_sales

                  where ss_quantity between 21 and 40) end bucket2,

       case when (select count(*)

                  from store_sales

                  where ss_quantity between 41 and 60) > 9387

            then (select avg(ss_ext_discount_amt)

                  from store_sales

                  where ss_quantity between 41 and 60)

            else (select avg(ss_net_profit)

                  from store_sales

                  where ss_quantity between 41 and 60) end bucket3,

       case when (select count(*)

                  from store_sales

                  where ss_quantity between 61 and 80) > 10098

            then (select avg(ss_ext_discount_amt)

                  from store_sales

                  where ss_quantity between 61 and 80)

            else (select avg(ss_net_profit)

                  from store_sales

                  where ss_quantity between 61 and 80) end bucket4,

       case when (select count(*)

                  from store_sales

                  where ss_quantity between 81 and 100) > 18213

            then (select avg(ss_ext_discount_amt)

                  from store_sales

                  where ss_quantity between 81 and 100)

            else (select avg(ss_net_profit)

                  from store_sales

                  where ss_quantity between 81 and 100) end bucket5

from reason

where r_reason_sk = 1

;

Q10.sql

select * from (select  

  cd_gender,

  cd_marital_status,

  cd_education_status,

  count(*) cnt1,

  cd_purchase_estimate,

  count(*) cnt2,

  cd_credit_rating,

  count(*) cnt3,

  cd_dep_count,

  count(*) cnt4,

  cd_dep_employed_count,

  count(*) cnt5,

  cd_dep_college_count,

  count(*) cnt6

 from

  customer c,customer_address ca,customer_demographics

 where

  c.c_current_addr_sk = ca.ca_address_sk and

  ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County') and

  cd_demo_sk = c.c_current_cdemo_sk and

  exists (select *

          from store_sales,date_dim

          where c.c_customer_sk = ss_customer_sk and

                ss_sold_date_sk = d_date_sk and

                d_year = 2002 and

                d_moy between 4 and 4+3) and

   (exists (select *

            from web_sales,date_dim

            where c.c_customer_sk = ws_bill_customer_sk and

                  ws_sold_date_sk = d_date_sk and

                  d_year = 2002 and

                  d_moy between 4 ANd 4+3) or

    exists (select *

            from catalog_sales,date_dim

            where c.c_customer_sk = cs_ship_customer_sk and

                  cs_sold_date_sk = d_date_sk and

                  d_year = 2002 and

                  d_moy between 4 and 4+3))

 group by cd_gender,

          cd_marital_status,

          cd_education_status,

          cd_purchase_estimate,

          cd_credit_rating,

          cd_dep_count,

          cd_dep_employed_count,

          cd_dep_college_count

 order by cd_gender,

          cd_marital_status,

          cd_education_status,

          cd_purchase_estimate,

          cd_credit_rating,

          cd_dep_count,

          cd_dep_employed_count,

          cd_dep_college_count

 ) A limit  100;

Q11.sql

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 * from ( select  

                  t_s_secyear.customer_id

                 ,t_s_secyear.customer_first_name

                 ,t_s_secyear.customer_last_name

                 ,t_s_secyear.customer_email_address

 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_email_address

 ) A limit  100;

Q12.sql

select * from (select  i_item_id

      ,i_item_desc

      ,i_category

      ,i_class

      ,i_current_price

      ,sum(ws_ext_sales_price) as itemrevenue

      ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over

          (partition by i_class) as revenueratio

from

web_sales

     ,item

     ,date_dim

where

ws_item_sk = i_item_sk

   and i_category in ('Jewelry', 'Sports', 'Books')

   and ws_sold_date_sk = d_date_sk

and d_date between cast('2001-01-12' as date)

and (cast('2001-01-12' as date) + 30 days)

group by

i_item_id

        ,i_item_desc

        ,i_category

        ,i_class

        ,i_current_price

order by

i_category

        ,i_class

        ,i_item_id

        ,i_item_desc

        ,revenueratio

 ) A limit  100;

Q13.sql

select avg(ss_quantity)

       ,avg(ss_ext_sales_price)

       ,avg(ss_ext_wholesale_cost)

       ,sum(ss_ext_wholesale_cost)

 from store_sales

     ,store

     ,customer_demographics

     ,household_demographics

     ,customer_address

     ,date_dim

 where s_store_sk = ss_store_sk

 and  ss_sold_date_sk = d_date_sk and d_year = 2001

 and((ss_hdemo_sk=hd_demo_sk

  and cd_demo_sk = ss_cdemo_sk

  and cd_marital_status = 'D'

  and cd_education_status = '2 yr Degree'

  and ss_sales_price between 100.00 and 150.00

  and hd_dep_count = 3   

     )or

     (ss_hdemo_sk=hd_demo_sk

  and cd_demo_sk = ss_cdemo_sk

  and cd_marital_status = 'S'

  and cd_education_status = 'Secondary'

  and ss_sales_price between 50.00 and 100.00   

  and hd_dep_count = 1

     ) or

     (ss_hdemo_sk=hd_demo_sk

  and cd_demo_sk = ss_cdemo_sk

  and cd_marital_status = 'W'

  and cd_education_status = 'Advanced Degree'

  and ss_sales_price between 150.00 and 200.00

  and hd_dep_count = 1  

     ))

 and((ss_addr_sk = ca_address_sk

  and ca_country = 'United States'

  and ca_state in ('CO', 'IL', 'MN')

  and ss_net_profit between 100 and 200  

     ) or

     (ss_addr_sk = ca_address_sk

  and ca_country = 'United States'

  and ca_state in ('OH', 'MT', 'NM')

  and ss_net_profit between 150 and 300  

     ) or

     (ss_addr_sk = ca_address_sk

  and ca_country = 'United States'

  and ca_state in ('TX', 'MO', 'MI')

  and ss_net_profit between 50 and 250  

     ))

;

Q14.sql

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 1998 AND 1998 + 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 1998 AND 1998 + 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 1998 AND 1998 + 2)

 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 1998 and 1998 + 2

       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 1998 and 1998 + 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 1998 and 1998 + 2) x)

 select * from ( select  channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(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 = 1998+2

Q15.sql

select * from (select  ca_zip

       ,sum(cs_sales_price)

 from catalog_sales

     ,customer

     ,customer_address

     ,date_dim

 where cs_bill_customer_sk = c_customer_sk

  and c_current_addr_sk = ca_address_sk

  and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',

                                   '85392', '85460', '80348', '81792')

        or ca_state in ('CA','WA','GA')

        or cs_sales_price > 500)

  and cs_sold_date_sk = d_date_sk

  and d_qoy = 2 and d_year = 2000

 group by ca_zip

 order by ca_zip

  ) A limit  100;

Q16.sql

select * from (select  

   count(distinct cs_order_number) as "order count"

  ,sum(cs_ext_ship_cost) as "total shipping cost"

  ,sum(cs_net_profit) as "total net profit"

from

   catalog_sales cs1

  ,date_dim

  ,customer_address

  ,call_center

where

    d_date between '1999-2-01' and

           (cast('1999-2-01' as date) + 60 days)

and cs1.cs_ship_date_sk = d_date_sk

and cs1.cs_ship_addr_sk = ca_address_sk

and ca_state = 'IL'

and cs1.cs_call_center_sk = cc_call_center_sk

and cc_county in ('Williamson County','Williamson County','Williamson County','Williamson County',

                  'Williamson County'

)

and exists (select *

            from catalog_sales cs2

            where cs1.cs_order_number = cs2.cs_order_number

              and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)

and not exists(select *

               from catalog_returns cr1

               where cs1.cs_order_number = cr1.cr_order_number)

order by count(distinct cs_order_number)

 ) A limit  100;

 

Q17.sql

select * from (select  i_item_id

       ,i_item_desc

       ,s_state

       ,count(ss_quantity) as store_sales_quantitycount

       ,avg(ss_quantity) as store_sales_quantityave

       ,stddev_samp(ss_quantity) as store_sales_quantitystdev

       ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov

       ,count(sr_return_quantity) as store_returns_quantitycount

       ,avg(sr_return_quantity) as store_returns_quantityave

       ,stddev_samp(sr_return_quantity) as store_returns_quantitystdev

       ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov

       ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave

       ,stddev_samp(cs_quantity) as catalog_sales_quantitystdev

       ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov

 from store_sales

     ,store_returns

     ,catalog_sales

     ,date_dim d1

     ,date_dim d2

     ,date_dim d3

     ,store

     ,item

 where d1.d_quarter_name = '1998Q1'

   and d1.d_date_sk = ss_sold_date_sk

   and i_item_sk = ss_item_sk

   and s_store_sk = ss_store_sk

   and ss_customer_sk = sr_customer_sk

   and ss_item_sk = sr_item_sk

   and ss_ticket_number = sr_ticket_number

   and sr_returned_date_sk = d2.d_date_sk

   and d2.d_quarter_name in ('1998Q1','1998Q2','1998Q3')

   and sr_customer_sk = cs_bill_customer_sk

   and sr_item_sk = cs_item_sk

   and cs_sold_date_sk = d3.d_date_sk

   and d3.d_quarter_name in ('1998Q1','1998Q2','1998Q3')

 group by i_item_id

         ,i_item_desc

         ,s_state

 order by i_item_id

         ,i_item_desc

         ,s_state

 ) A limit  100;

Q18.sql

select * from (select  i_item_id,

        ca_country,

        ca_state,

        ca_county,

        avg( cast(cs_quantity as decimal(12,2))) agg1,

        avg( cast(cs_list_price as decimal(12,2))) agg2,

        avg( cast(cs_coupon_amt as decimal(12,2))) agg3,

        avg( cast(cs_sales_price as decimal(12,2))) agg4,

        avg( cast(cs_net_profit as decimal(12,2))) agg5,

        avg( cast(c_birth_year as decimal(12,2))) agg6,

        avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7

 from catalog_sales, customer_demographics cd1,

      customer_demographics cd2, customer, customer_address, date_dim, item

 where cs_sold_date_sk = d_date_sk and

       cs_item_sk = i_item_sk and

       cs_bill_cdemo_sk = cd1.cd_demo_sk and

       cs_bill_customer_sk = c_customer_sk and

       cd1.cd_gender = 'M' and

       cd1.cd_education_status = 'College' and

       c_current_cdemo_sk = cd2.cd_demo_sk and

       c_current_addr_sk = ca_address_sk and

       c_birth_month in (9,5,12,4,1,10) and

       d_year = 2001 and

       ca_state in ('ND','WI','AL'

                   ,'NC','OK','MS','TN')

 group by rollup (i_item_id, ca_country, ca_state, ca_county)

 order by ca_country,

        ca_state,

        ca_county,

i_item_id

  ) A limit  100;

Q19.sql

select * from (select  i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,

  sum(ss_ext_sales_price) ext_price

 from date_dim, store_sales, item,customer,customer_address,store

 where d_date_sk = ss_sold_date_sk

   and ss_item_sk = i_item_sk

   and i_manager_id=7

   and d_moy=11

   and d_year=1999

   and ss_customer_sk = c_customer_sk

   and c_current_addr_sk = ca_address_sk

   and substr(ca_zip,1,5) <> substr(s_zip,1,5)

   and ss_store_sk = s_store_sk

 group by i_brand

      ,i_brand_id

      ,i_manufact_id

      ,i_manufact

 order by ext_price desc

         ,i_brand

         ,i_brand_id

         ,i_manufact_id

         ,i_manufact

 ) A limit  100 ;

Q20.sql

select * from (select  i_item_id

       ,i_item_desc

       ,i_category

       ,i_class

       ,i_current_price

       ,sum(cs_ext_sales_price) as itemrevenue

       ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over

           (partition by i_class) as revenueratio

 from catalog_sales

     ,item

     ,date_dim

 where cs_item_sk = i_item_sk

   and i_category in ('Jewelry', 'Sports', 'Books')

   and cs_sold_date_sk = d_date_sk

 and d_date between cast('2001-01-12' as date)

  and (cast('2001-01-12' as date) + 30 days)

 group by i_item_id

         ,i_item_desc

         ,i_category

         ,i_class

         ,i_current_price

 order by i_category

         ,i_class

         ,i_item_id

         ,i_item_desc

         ,revenueratio

 ) A limit  100;

Q21.sql

select * from (select  *

 from(select w_warehouse_name

            ,i_item_id

            ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))

                then inv_quantity_on_hand

                      else 0 end) as inv_before

            ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))

                      then inv_quantity_on_hand

                      else 0 end) as inv_after

   from inventory

       ,warehouse

       ,item

       ,date_dim

   where i_current_price between 0.99 and 1.49

     and i_item_sk          = inv_item_sk

     and inv_warehouse_sk   = w_warehouse_sk

     and inv_date_sk    = d_date_sk

     and d_date between (cast ('1998-04-08' as date) - 30 days)

                    and (cast ('1998-04-08' as date) + 30 days)

   group by w_warehouse_name, i_item_id) x

 where (case when inv_before > 0

             then inv_after / inv_before

             else null

             end) between 2.0/3.0 and 3.0/2.0

 order by w_warehouse_name

         ,i_item_id

  ) A limit  100;

Q22.sql

select * from (select  i_product_name

             ,i_brand

             ,i_class

             ,i_category

             ,avg(inv_quantity_on_hand) qoh

       from inventory

           ,date_dim

           ,item

       where inv_date_sk=d_date_sk

              and inv_item_sk=i_item_sk

              and d_month_seq between 1212 and 1212 + 11

       group by rollup(i_product_name

                       ,i_brand

                       ,i_class

                       ,i_category)

order by qoh, i_product_name, i_brand, i_class, i_category

 ) A limit  100;

 

Q23.sql

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 (1999,1999+1,1999+2,1999+3)

  group by substr(i_item_desc,1,30),i_item_sk,d_date

  having count(*) >4),

 max_store_sales as

 (select max(csales) tpc_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 (1999,1999+1,1999+2,1999+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 * from ( select  sum(sales)

 from (select cs_quantity*cs_list_price sales

       from catalog_sales

           ,date_dim

       where d_year = 1999

         and d_moy = 1

         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 = 1999

         and d_moy = 1

         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))

  ) A limit  100;

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 (1999,1999 + 1,1999 + 2,1999 + 3)

  group by substr(i_item_desc,1,30),i_item_sk,d_date

  having count(*) >4),

 max_store_sales as

 (select max(csales) tpc_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 (1999,1999+1,1999+2,1999+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 * from ( 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 = 1999

         and d_moy = 1

         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 = 1999

         and d_moy = 1

         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

   ) A limit  100;

Q24.sql

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_sales_price) 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=7

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 = 'orchid'

group by c_last_name

        ,c_first_name

        ,s_store_name

having sum(netpaid) > (select 0.05*avg(netpaid)

                                 from ssales)

;

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_sales_price) 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 = 7

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 = 'chiffon'

group by c_last_name

        ,c_first_name

        ,s_store_name

having sum(netpaid) > (select 0.05*avg(netpaid)

                           from ssales)

;

Q25.sql

select * from (select  

 i_item_id

 ,i_item_desc

 ,s_store_id

 ,s_store_name

 ,sum(ss_net_profit) as store_sales_profit

 ,sum(sr_net_loss) as store_returns_loss

 ,sum(cs_net_profit) as catalog_sales_profit

 from

 store_sales

 ,store_returns

 ,catalog_sales

 ,date_dim d1

 ,date_dim d2

 ,date_dim d3

 ,store

 ,item

 where

 d1.d_moy = 4

 and d1.d_year = 2000

 and d1.d_date_sk = ss_sold_date_sk

 and i_item_sk = ss_item_sk

 and s_store_sk = ss_store_sk

 and ss_customer_sk = sr_customer_sk

 and ss_item_sk = sr_item_sk

 and ss_ticket_number = sr_ticket_number

 and sr_returned_date_sk = d2.d_date_sk

 and d2.d_moy               between 4 and  10

 and d2.d_year              = 2000

 and sr_customer_sk = cs_bill_customer_sk

 and sr_item_sk = cs_item_sk

 and cs_sold_date_sk = d3.d_date_sk

 and d3.d_moy               between 4 and  10

 and d3.d_year              = 2000

 group by

 i_item_id

 ,i_item_desc

 ,s_store_id

 ,s_store_name

 order by

 i_item_id

 ,i_item_desc

 ,s_store_id

 ,s_store_name

  ) A limit  100;

 

Q26.sql

select * from (select  i_item_id,

        avg(cs_quantity) agg1,

        avg(cs_list_price) agg2,

        avg(cs_coupon_amt) agg3,

        avg(cs_sales_price) agg4

 from catalog_sales, customer_demographics, date_dim, item, promotion

 where cs_sold_date_sk = d_date_sk and

       cs_item_sk = i_item_sk and

       cs_bill_cdemo_sk = cd_demo_sk and

       cs_promo_sk = p_promo_sk and

       cd_gender = 'F' and

       cd_marital_status = 'W' and

       cd_education_status = 'Primary' and

       (p_channel_email = 'N' or p_channel_event = 'N') and

       d_year = 1998

 group by i_item_id

 order by i_item_id

  ) A limit  100;

Q27.sql

select * from (select  i_item_id,

        s_state, grouping(s_state) g_state,

        avg(ss_quantity) agg1,

        avg(ss_list_price) agg2,

        avg(ss_coupon_amt) agg3,

        avg(ss_sales_price) agg4

 from store_sales, customer_demographics, date_dim, store, item

 where ss_sold_date_sk = d_date_sk and

       ss_item_sk = i_item_sk and

       ss_store_sk = s_store_sk and

       ss_cdemo_sk = cd_demo_sk and

       cd_gender = 'F' and

       cd_marital_status = 'W' and

       cd_education_status = 'Primary' and

       d_year = 1998 and

       s_state in ('TN','TN', 'TN', 'TN', 'TN', 'TN')

 group by rollup (i_item_id, s_state)

 order by i_item_id

         ,s_state

  ) A limit  100;

Q28.sql

select * from (select  *

from (select avg(ss_list_price) B1_LP

            ,count(ss_list_price) B1_CNT

            ,count(distinct ss_list_price) B1_CNTD

      from store_sales

      where ss_quantity between 0 and 5

        and (ss_list_price between 11 and 11+10

             or ss_coupon_amt between 460 and 460+1000

             or ss_wholesale_cost between 14 and 14+20)) B1,

     (select avg(ss_list_price) B2_LP

            ,count(ss_list_price) B2_CNT

            ,count(distinct ss_list_price) B2_CNTD

      from store_sales

      where ss_quantity between 6 and 10

        and (ss_list_price between 91 and 91+10

          or ss_coupon_amt between 1430 and 1430+1000

          or ss_wholesale_cost between 32 and 32+20)) B2,

     (select avg(ss_list_price) B3_LP

            ,count(ss_list_price) B3_CNT

            ,count(distinct ss_list_price) B3_CNTD

      from store_sales

      where ss_quantity between 11 and 15

        and (ss_list_price between 66 and 66+10

          or ss_coupon_amt between 920 and 920+1000

          or ss_wholesale_cost between 4 and 4+20)) B3,

     (select avg(ss_list_price) B4_LP

            ,count(ss_list_price) B4_CNT

            ,count(distinct ss_list_price) B4_CNTD

      from store_sales

      where ss_quantity between 16 and 20

        and (ss_list_price between 142 and 142+10

          or ss_coupon_amt between 3054 and 3054+1000

          or ss_wholesale_cost between 80 and 80+20)) B4,

     (select avg(ss_list_price) B5_LP

            ,count(ss_list_price) B5_CNT

            ,count(distinct ss_list_price) B5_CNTD

      from store_sales

      where ss_quantity between 21 and 25

        and (ss_list_price between 135 and 135+10

          or ss_coupon_amt between 14180 and 14180+1000

          or ss_wholesale_cost between 38 and 38+20)) B5,

     (select avg(ss_list_price) B6_LP

            ,count(ss_list_price) B6_CNT

            ,count(distinct ss_list_price) B6_CNTD

      from store_sales

      where ss_quantity between 26 and 30

        and (ss_list_price between 28 and 28+10

          or ss_coupon_amt between 2513 and 2513+1000

          or ss_wholesale_cost between 42 and 42+20)) B6

 ) A limit  100;

 

Q29.sql

select * from (select   

     i_item_id

    ,i_item_desc

    ,s_store_id

    ,s_store_name

    ,sum(ss_quantity)        as store_sales_quantity

    ,sum(sr_return_quantity) as store_returns_quantity

    ,sum(cs_quantity)        as catalog_sales_quantity

 from

    store_sales

   ,store_returns

   ,catalog_sales

   ,date_dim             d1

   ,date_dim             d2

   ,date_dim             d3

   ,store

   ,item

 where

     d1.d_moy               = 4

 and d1.d_year              = 1999

 and d1.d_date_sk           = ss_sold_date_sk

 and i_item_sk              = ss_item_sk

 and s_store_sk             = ss_store_sk

 and ss_customer_sk         = sr_customer_sk

 and ss_item_sk             = sr_item_sk

 and ss_ticket_number       = sr_ticket_number

 and sr_returned_date_sk    = d2.d_date_sk

 and d2.d_moy               between 4 and  4 + 3

 and d2.d_year              = 1999

 and sr_customer_sk         = cs_bill_customer_sk

 and sr_item_sk             = cs_item_sk

 and cs_sold_date_sk        = d3.d_date_sk     

 and d3.d_year              in (1999,1999+1,1999+2)

 group by

    i_item_id

   ,i_item_desc

   ,s_store_id

   ,s_store_name

 order by

    i_item_id

   ,i_item_desc

   ,s_store_id

   ,s_store_name

  ) A limit  100;

Q30.sql

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 =2002

   and wr_returning_addr_sk = ca_address_sk

 group by wr_returning_customer_sk

         ,ca_state)

 select * from ( 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,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 = 'IL'

       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,ctr_total_return

 ) A limit  100;

Q31.sql

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 = 2000

    and ss1.ca_county = ss2.ca_county

    and ss2.d_qoy = 2

    and ss2.d_year = 2000

 and ss2.ca_county = ss3.ca_county

    and ss3.d_qoy = 3

    and ss3.d_year = 2000

    and ss1.ca_county = ws1.ca_county

    and ws1.d_qoy = 1

    and ws1.d_year = 2000

    and ws1.ca_county = ws2.ca_county

    and ws2.d_qoy = 2

    and ws2.d_year = 2000

    and ws1.ca_county = ws3.ca_county

    and ws3.d_qoy = 3

    and ws3.d_year =2000

    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 ss1.d_year;

 

Q32.sql

select * from (select  sum(cs_ext_discount_amt)  as "excess discount amount"

from

   catalog_sales

   ,item

   ,date_dim

where

i_manufact_id = 269

and i_item_sk = cs_item_sk

and d_date between '1998-03-18' and

        (cast('1998-03-18' as date) + 90 days)

and d_date_sk = cs_sold_date_sk

and cs_ext_discount_amt  

     > (

         select

            1.3 * avg(cs_ext_discount_amt)

         from

            catalog_sales

           ,date_dim

         where

              cs_item_sk = i_item_sk

          and d_date between '1998-03-18' and

                             (cast('1998-03-18' as date) + 90 days)

          and d_date_sk = cs_sold_date_sk

      )

 ) A limit  100;

Q33.sql

with ss as (

 select

          i_manufact_id,sum(ss_ext_sales_price) total_sales

 from

  store_sales,

  date_dim,

         customer_address,

         item

 where

         i_manufact_id in (select

  i_manufact_id

from

 item

where i_category in ('Books'))

 and     ss_item_sk              = i_item_sk

 and     ss_sold_date_sk         = d_date_sk

 and     d_year                  = 1999

 and     d_moy                   = 3

 and     ss_addr_sk              = ca_address_sk

 and     ca_gmt_offset           = -5

 group by i_manufact_id),

 cs as (

 select

          i_manufact_id,sum(cs_ext_sales_price) total_sales

 from

  catalog_sales,

  date_dim,

         customer_address,

         item

 where

         i_manufact_id               in (select

  i_manufact_id

from

 item

where i_category in ('Books'))

 and     cs_item_sk              = i_item_sk

 and     cs_sold_date_sk         = d_date_sk

 and     d_year                  = 1999

 and     d_moy                   = 3

 and     cs_bill_addr_sk         = ca_address_sk

 and     ca_gmt_offset           = -5

 group by i_manufact_id),

 ws as (

 select

          i_manufact_id,sum(ws_ext_sales_price) total_sales

 from

  web_sales,

  date_dim,

         customer_address,

         item

 where

         i_manufact_id               in (select

  i_manufact_id

from

 item

where i_category in ('Books'))

 and     ws_item_sk              = i_item_sk

 and     ws_sold_date_sk         = d_date_sk

 and     d_year                  = 1999

 and     d_moy                   = 3

 and     ws_bill_addr_sk         = ca_address_sk

 and     ca_gmt_offset           = -5

 group by i_manufact_id)

 select * from ( select  i_manufact_id ,sum(total_sales) total_sales

 from  (select * from ss

        union all

        select * from cs

        union all

        select * from ws) tmp1

 group by i_manufact_id

 order by total_sales

 ) A limit  100;

Q34.sql

select c_last_name

       ,c_first_name

       ,c_salutation

       ,c_preferred_cust_flag

       ,ss_ticket_number

       ,cnt from

   (select ss_ticket_number

          ,ss_customer_sk

          ,count(*) cnt

    from store_sales,date_dim,store,household_demographics

    where store_sales.ss_sold_date_sk = date_dim.d_date_sk

    and store_sales.ss_store_sk = store.s_store_sk  

    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk

    and (date_dim.d_dom between 1 and 3 or date_dim.d_dom between 25 and 28)

    and (household_demographics.hd_buy_potential = '>10000' or

         household_demographics.hd_buy_potential = 'Unknown')

    and household_demographics.hd_vehicle_count > 0

    and (case when household_demographics.hd_vehicle_count > 0

then household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count

else null

end)  > 1.2

    and date_dim.d_year in (1998,1998+1,1998+2)

    and store.s_county in ('Williamson County','Williamson County','Williamson County','Williamson County',

                           'Williamson County','Williamson County','Williamson County','Williamson County')

    group by ss_ticket_number,ss_customer_sk) dn,customer

    where ss_customer_sk = c_customer_sk

      and cnt between 15 and 20

order by c_last_name,c_first_name,c_salutation,c_preferred_cust_flag desc;

Q35.sql

select * from (select   

  ca_state,

  cd_gender,

  cd_marital_status,

  cd_dep_count,

  count(*) cnt1,

  avg(cd_dep_count),

  max(cd_dep_count),

  sum(cd_dep_count),

  cd_dep_employed_count,

  count(*) cnt2,

  avg(cd_dep_employed_count),

  max(cd_dep_employed_count),

  sum(cd_dep_employed_count),

  cd_dep_college_count,

  count(*) cnt3,

  avg(cd_dep_college_count),

  max(cd_dep_college_count),

  sum(cd_dep_college_count)

 from

  customer c,customer_address ca,customer_demographics

 where

  c.c_current_addr_sk = ca.ca_address_sk and

  cd_demo_sk = c.c_current_cdemo_sk and

  exists (select *

          from store_sales,date_dim

          where c.c_customer_sk = ss_customer_sk and

                ss_sold_date_sk = d_date_sk and

                d_year = 1999 and

                d_qoy < 4) and

   (exists (select *

            from web_sales,date_dim

            where c.c_customer_sk = ws_bill_customer_sk and

                  ws_sold_date_sk = d_date_sk and

                  d_year = 1999 and

                  d_qoy < 4) or

    exists (select *

            from catalog_sales,date_dim

            where c.c_customer_sk = cs_ship_customer_sk and

                  cs_sold_date_sk = d_date_sk and

                  d_year = 1999 and

                  d_qoy < 4))

 group by ca_state,

          cd_gender,

          cd_marital_status,

          cd_dep_count,

          cd_dep_employed_count,

          cd_dep_college_count

 order by ca_state,

          cd_gender,

          cd_marital_status,

          cd_dep_count,

          cd_dep_employed_count,

          cd_dep_college_count

  ) A limit  100;

Q36.sql

select * from (select  

    sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin

   ,i_category

   ,i_class

   ,grouping(i_category)+grouping(i_class) as lochierarchy

   ,rank() over (

  partition by grouping(i_category)+grouping(i_class),

  case when grouping(i_class) = 0 then i_category end

  order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent

 from

    store_sales

   ,date_dim       d1

   ,item

   ,store

 where

    d1.d_year = 2000

 and d1.d_date_sk = ss_sold_date_sk

 and i_item_sk  = ss_item_sk

 and s_store_sk  = ss_store_sk

 and s_state in ('TN','TN','TN','TN',

                 'TN','TN','TN','TN')

 group by rollup(i_category,i_class)

 order by

   lochierarchy desc

  ,case when lochierarchy = 0 then i_category end

  ,rank_within_parent

   ) A limit  100;

Q37.sql

select * from (select  i_item_id

       ,i_item_desc

       ,i_current_price

 from item, inventory, date_dim, catalog_sales

 where i_current_price between 22 and 22 + 30

 and inv_item_sk = i_item_sk

 and d_date_sk=inv_date_sk

 and d_date between cast('2001-06-02' as date) and (cast('2001-06-02' as date) +  60 days)

 and i_manufact_id in (678,964,918,849)

 and inv_quantity_on_hand between 100 and 500

 and cs_item_sk = i_item_sk

 group by i_item_id,i_item_desc,i_current_price

 order by i_item_id

  ) A limit  100;

Q38.sql

select * from (select  count(*) from (

    select distinct c_last_name, c_first_name, d_date

    from store_sales, date_dim, customer

          where store_sales.ss_sold_date_sk = date_dim.d_date_sk

      and store_sales.ss_customer_sk = customer.c_customer_sk

      and d_month_seq between 1212 and 1212 + 11

  intersect

    select distinct c_last_name, c_first_name, d_date

    from catalog_sales, date_dim, customer

          where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk

      and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk

      and d_month_seq between 1212 and 1212 + 11

  intersect

    select distinct c_last_name, c_first_name, d_date

    from web_sales, date_dim, customer

          where web_sales.ws_sold_date_sk = date_dim.d_date_sk

      and web_sales.ws_bill_customer_sk = customer.c_customer_sk

      and d_month_seq between 1212 and 1212 + 11

) hot_cust

 ) A limit  100;

Q39.sql

with inv as

(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy

       ,stdev,mean, case mean when 0 then null else stdev/mean end cov

 from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy

            ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean

      from inventory

          ,item

          ,warehouse

          ,date_dim

      where inv_item_sk = i_item_sk

        and inv_warehouse_sk = w_warehouse_sk

        and inv_date_sk = d_date_sk

        and d_year =1998

      group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo

 where case mean when 0 then 0 else stdev/mean end > 1)

select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov

        ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov

from inv inv1,inv inv2

where inv1.i_item_sk = inv2.i_item_sk

  and inv1.w_warehouse_sk =  inv2.w_warehouse_sk

  and inv1.d_moy=4

  and inv2.d_moy=4+1

order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov

        ,inv2.d_moy,inv2.mean, inv2.cov

;

with inv as

(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy

       ,stdev,mean, case mean when 0 then null else stdev/mean end cov

 from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy

            ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean

      from inventory

          ,item

          ,warehouse

          ,date_dim

      where inv_item_sk = i_item_sk

        and inv_warehouse_sk = w_warehouse_sk

        and inv_date_sk = d_date_sk

        and d_year =1998

      group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo

 where case mean when 0 then 0 else stdev/mean end > 1)

select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov

        ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov

from inv inv1,inv inv2

where inv1.i_item_sk = inv2.i_item_sk

  and inv1.w_warehouse_sk =  inv2.w_warehouse_sk

  and inv1.d_moy=4

  and inv2.d_moy=4+1

  and inv1.cov > 1.5

order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov

        ,inv2.d_moy,inv2.mean, inv2.cov

;

Q40.sql

select * from (select  

   w_state

  ,i_item_id

  ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))

  then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before

  ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))

  then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after

 from

   catalog_sales left outer join catalog_returns on

       (cs_order_number = cr_order_number

        and cs_item_sk = cr_item_sk)

  ,warehouse

  ,item

  ,date_dim

 where

     i_current_price between 0.99 and 1.49

 and i_item_sk          = cs_item_sk

 and cs_warehouse_sk    = w_warehouse_sk

 and cs_sold_date_sk    = d_date_sk

 and d_date between (cast ('1998-04-08' as date) - 30 days)

                and (cast ('1998-04-08' as date) + 30 days)

 group by

    w_state,i_item_id

 order by w_state,i_item_id

 ) A limit  100;

 

Q41.sql

select * from (select  distinct(i_product_name)

 from item i1

 where i_manufact_id between 742 and 742+40

   and (select count(*) as item_cnt

        from item

        where (i_manufact = i1.i_manufact and

        ((i_category = 'Women' and

        (i_color = 'orchid' or i_color = 'papaya') and

        (i_units = 'Pound' or i_units = 'Lb') and

        (i_size = 'petite' or i_size = 'medium')

        ) or

        (i_category = 'Women' and

        (i_color = 'burlywood' or i_color = 'navy') and

        (i_units = 'Bundle' or i_units = 'Each') and

        (i_size = 'N/A' or i_size = 'extra large')

        ) or

        (i_category = 'Men' and

        (i_color = 'bisque' or i_color = 'azure') and

        (i_units = 'N/A' or i_units = 'Tsp') and

        (i_size = 'small' or i_size = 'large')

        ) or

        (i_category = 'Men' and

        (i_color = 'chocolate' or i_color = 'cornflower') and

        (i_units = 'Bunch' or i_units = 'Gross') and

        (i_size = 'petite' or i_size = 'medium')

        ))) or

       (i_manufact = i1.i_manufact and

        ((i_category = 'Women' and

        (i_color = 'salmon' or i_color = 'midnight') and

        (i_units = 'Oz' or i_units = 'Box') and

        (i_size = 'petite' or i_size = 'medium')

        ) or

        (i_category = 'Women' and

        (i_color = 'snow' or i_color = 'steel') and

        (i_units = 'Carton' or i_units = 'Tbl') and

        (i_size = 'N/A' or i_size = 'extra large')

        ) or

        (i_category = 'Men' and

        (i_color = 'purple' or i_color = 'gainsboro') and

        (i_units = 'Dram' or i_units = 'Unknown') and

        (i_size = 'small' or i_size = 'large')

        ) or

        (i_category = 'Men' and

        (i_color = 'metallic' or i_color = 'forest') and

        (i_units = 'Gram' or i_units = 'Ounce') and

        (i_size = 'petite' or i_size = 'medium')

        )))) > 0

 order by i_product_name

  ) A limit  100;

Q42.sql

select * from (select  dt.d_year

  ,item.i_category_id

  ,item.i_category

  ,sum(ss_ext_sales_price)

 from date_dim dt

  ,store_sales

  ,item

 where dt.d_date_sk = store_sales.ss_sold_date_sk

  and store_sales.ss_item_sk = item.i_item_sk

  and item.i_manager_id = 1  

  and dt.d_moy=12

  and dt.d_year=1998

 group by dt.d_year

  ,item.i_category_id

  ,item.i_category

 order by       sum(ss_ext_sales_price) desc,dt.d_year

  ,item.i_category_id

  ,item.i_category

 ) A limit  100 ;

Q43.sql

select * from (select  s_store_name, s_store_id,

        sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales,

        sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales,

        sum(case when (d_day_name='Tuesday') then ss_sales_price else  null end) tue_sales,

        sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales,

        sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales,

        sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales,

        sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales

 from date_dim, store_sales, store

 where d_date_sk = ss_sold_date_sk and

       s_store_sk = ss_store_sk and

       s_gmt_offset = -5 and

       d_year = 1998

 group by s_store_name, s_store_id

 order by s_store_name, s_store_id,sun_sales,mon_sales,tue_sales,wed_sales,thu_sales,fri_sales,sat_sales

  ) A limit  100;

Q44.sql

select * from (select  asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing

from(select *

     from (select item_sk,rank() over (order by rank_col asc) rnk

           from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col

                 from store_sales ss1

                 where ss_store_sk = 2

                 group by ss_item_sk

                 having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col

                                                  from store_sales

                                                  where ss_store_sk = 2

                                                    and ss_hdemo_sk is null

                                                  group by ss_store_sk))V1)V11

     where rnk  < 11) asceding,

    (select *

     from (select item_sk,rank() over (order by rank_col desc) rnk

           from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col

                 from store_sales ss1

                 where ss_store_sk = 2

                 group by ss_item_sk

                 having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col

                                                  from store_sales

                                                  where ss_store_sk = 2

                                                    and ss_hdemo_sk is null

                                                  group by ss_store_sk))V2)V21

     where rnk  < 11) descending,

item i1,

item i2

where asceding.rnk = descending.rnk

  and i1.i_item_sk=asceding.item_sk

  and i2.i_item_sk=descending.item_sk

order by asceding.rnk

 ) A limit  100;

Q45.sql

select * from (select  ca_zip, ca_county, sum(ws_sales_price)

 from web_sales, customer, customer_address, date_dim, item

 where ws_bill_customer_sk = c_customer_sk

  and c_current_addr_sk = ca_address_sk

  and ws_item_sk = i_item_sk

  and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792')

        or

        i_item_id in (select i_item_id

                             from item

                             where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19, 23, 29)

                             )

      )

  and ws_sold_date_sk = d_date_sk

  and d_qoy = 2 and d_year = 2000

 group by ca_zip, ca_county

 order by ca_zip, ca_county

  ) A limit  100;

Q46.sql

select * from (select  c_last_name

       ,c_first_name

       ,ca_city

       ,bought_city

       ,ss_ticket_number

       ,amt,profit

 from

   (select ss_ticket_number

          ,ss_customer_sk

          ,ca_city bought_city

          ,sum(ss_coupon_amt) amt

          ,sum(ss_net_profit) profit

    from store_sales,date_dim,store,household_demographics,customer_address

    where store_sales.ss_sold_date_sk = date_dim.d_date_sk

    and store_sales.ss_store_sk = store.s_store_sk  

    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk

    and store_sales.ss_addr_sk = customer_address.ca_address_sk

    and (household_demographics.hd_dep_count = 5 or

         household_demographics.hd_vehicle_count= 3)

    and date_dim.d_dow in (6,0)

    and date_dim.d_year in (1999,1999+1,1999+2)

    and store.s_city in ('Midway','Fairview','Fairview','Midway','Fairview')

    group by ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city) dn,customer,customer_address current_addr

    where ss_customer_sk = c_customer_sk

      and customer.c_current_addr_sk = current_addr.ca_address_sk

      and current_addr.ca_city <> bought_city

  order by c_last_name

          ,c_first_name

          ,ca_city

          ,bought_city

          ,ss_ticket_number

   ) A limit  100;

Q47.sql

with v1 as(

 select i_category, i_brand,

        s_store_name, s_company_name,

        d_year, d_moy,

        sum(ss_sales_price) sum_sales,

        avg(sum(ss_sales_price)) over

          (partition by i_category, i_brand,

                     s_store_name, s_company_name, d_year)

          avg_monthly_sales,

        rank() over

          (partition by i_category, i_brand,

                     s_store_name, s_company_name

           order by d_year, d_moy) rn

 from item, store_sales, date_dim, store

 where ss_item_sk = i_item_sk and

       ss_sold_date_sk = d_date_sk and

       ss_store_sk = s_store_sk and

       (

         d_year = 2000 or

         ( d_year = 2000-1 and d_moy =12) or

         ( d_year = 2000+1 and d_moy =1)

       )

 group by i_category, i_brand,

          s_store_name, s_company_name,

          d_year, d_moy),

 v2 as(

 select v1.i_category

        ,v1.d_year, v1.d_moy

        ,v1.avg_monthly_sales

        ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum

 from v1, v1 v1_lag, v1 v1_lead

 where v1.i_category = v1_lag.i_category and

       v1.i_category = v1_lead.i_category and

       v1.i_brand = v1_lag.i_brand and

       v1.i_brand = v1_lead.i_brand and

       v1.s_store_name = v1_lag.s_store_name and

       v1.s_store_name = v1_lead.s_store_name and

       v1.s_company_name = v1_lag.s_company_name and

       v1.s_company_name = v1_lead.s_company_name and

       v1.rn = v1_lag.rn + 1 and

       v1.rn = v1_lead.rn - 1)

 select * from ( select  *

 from v2

 where  d_year = 2000 and    

        avg_monthly_sales > 0 and

        case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1

 order by sum_sales - avg_monthly_sales, 3

  ) A limit  100;

Q48.sql

select sum (ss_quantity)

 from store_sales, store, customer_demographics, customer_address, date_dim

 where s_store_sk = ss_store_sk

 and  ss_sold_date_sk = d_date_sk and d_year = 1998

 and  

 (

  (

   cd_demo_sk = ss_cdemo_sk

   and

   cd_marital_status = 'M'

   and

   cd_education_status = '4 yr Degree'

   and

   ss_sales_price between 100.00 and 150.00  

   )

 or

  (

  cd_demo_sk = ss_cdemo_sk

   and

   cd_marital_status = 'D'

   and

   cd_education_status = 'Primary'

   and

   ss_sales_price between 50.00 and 100.00   

  )

 or

 (

  cd_demo_sk = ss_cdemo_sk

  and

   cd_marital_status = 'U'

   and

   cd_education_status = 'Advanced Degree'

   and

   ss_sales_price between 150.00 and 200.00  

 )

 )

 and

 (

  (

  ss_addr_sk = ca_address_sk

  and

  ca_country = 'United States'

  and

  ca_state in ('KY', 'GA', 'NM')

  and ss_net_profit between 0 and 2000  

  )

 or

  (ss_addr_sk = ca_address_sk

  and

  ca_country = 'United States'

  and

  ca_state in ('MT', 'OR', 'IN')

  and ss_net_profit between 150 and 3000

  )

 or

  (ss_addr_sk = ca_address_sk

  and

  ca_country = 'United States'

  and

  ca_state in ('WI', 'MO', 'WV')

  and ss_net_profit between 50 and 25000

  )

 )

;

Q49.sql

select * from (select  

 'web' as channel

 ,web.item

 ,web.return_ratio

 ,web.return_rank

 ,web.currency_rank

 from (

  select

   item

  ,return_ratio

  ,currency_ratio

  ,rank() over (order by return_ratio) as return_rank

  ,rank() over (order by currency_ratio) as currency_rank

  from

  ( select ws.ws_item_sk as item

  ,(cast(sum(coalesce(wr.wr_return_quantity,0)) as dec(15,4))/

  cast(sum(coalesce(ws.ws_quantity,0)) as dec(15,4) )) as return_ratio

  ,(cast(sum(coalesce(wr.wr_return_amt,0)) as dec(15,4))/

  cast(sum(coalesce(ws.ws_net_paid,0)) as dec(15,4) )) as currency_ratio

  from

   web_sales ws left outer join web_returns wr

  on (ws.ws_order_number = wr.wr_order_number and

  ws.ws_item_sk = wr.wr_item_sk)

                 ,date_dim

  where

  wr.wr_return_amt > 10000

  and ws.ws_net_profit > 1

                         and ws.ws_net_paid > 0

                         and ws.ws_quantity > 0

                         and ws_sold_date_sk = d_date_sk

                         and d_year = 2000

                         and d_moy = 12

  group by ws.ws_item_sk

  ) in_web

 ) web

 where

 (

 web.return_rank <= 10

 or

 web.currency_rank <= 10

 )

 union

 select

 'catalog' as channel

 ,catalog.item

 ,catalog.return_ratio

 ,catalog.return_rank

 ,catalog.currency_rank

 from (

  select

   item

  ,return_ratio

  ,currency_ratio

  ,rank() over (order by return_ratio) as return_rank

  ,rank() over (order by currency_ratio) as currency_rank

  from

  ( select

  cs.cs_item_sk as item

  ,(cast(sum(coalesce(cr.cr_return_quantity,0)) as dec(15,4))/

  cast(sum(coalesce(cs.cs_quantity,0)) as dec(15,4) )) as return_ratio

  ,(cast(sum(coalesce(cr.cr_return_amount,0)) as dec(15,4))/

  cast(sum(coalesce(cs.cs_net_paid,0)) as dec(15,4) )) as currency_ratio

  from

  catalog_sales cs left outer join catalog_returns cr

  on (cs.cs_order_number = cr.cr_order_number and

  cs.cs_item_sk = cr.cr_item_sk)

                ,date_dim

  where

  cr.cr_return_amount > 10000

  and cs.cs_net_profit > 1

                         and cs.cs_net_paid > 0

                         and cs.cs_quantity > 0

                         and cs_sold_date_sk = d_date_sk

                         and d_year = 2000

                         and d_moy = 12

                 group by cs.cs_item_sk

  ) in_cat

 ) catalog

 where

 (

 catalog.return_rank <= 10

 or

 catalog.currency_rank <=10

 )

 union

 select

 'store' as channel

 ,store.item

 ,store.return_ratio

 ,store.return_rank

 ,store.currency_rank

 from (

  select

   item

  ,return_ratio

  ,currency_ratio

  ,rank() over (order by return_ratio) as return_rank

  ,rank() over (order by currency_ratio) as currency_rank

  from

  ( select sts.ss_item_sk as item

  ,(cast(sum(coalesce(sr.sr_return_quantity,0)) as dec(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) as dec(15,4) )) as return_ratio

  ,(cast(sum(coalesce(sr.sr_return_amt,0)) as dec(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) as dec(15,4) )) as currency_ratio

  from

  store_sales sts left outer join store_returns sr

  on (sts.ss_ticket_number = sr.sr_ticket_number and sts.ss_item_sk = sr.sr_item_sk)

                ,date_dim

  where

  sr.sr_return_amt > 10000

  and sts.ss_net_profit > 1

                         and sts.ss_net_paid > 0

                         and sts.ss_quantity > 0

                         and ss_sold_date_sk = d_date_sk

                         and d_year = 2000

                         and d_moy = 12

  group by sts.ss_item_sk

  ) in_store

 ) store

 where  (

 store.return_rank <= 10

 or

 store.currency_rank <= 10

 )

 order by 1,4,5

  ) A limit  100;

Q50.sql

select * from (select  

   s_store_name

  ,s_company_id

  ,s_street_number

  ,s_street_name

  ,s_street_type

  ,s_suite_number

  ,s_city

  ,s_county

  ,s_state

  ,s_zip

  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end)  as "30 days"

  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and

                 (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end )  as "31-60 days"

  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and

                 (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end)  as "61-90 days"

  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and

                 (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end)  as "91-120 days"

  ,sum(case when (sr_returned_date_sk - ss_sold_date_sk  > 120) then 1 else 0 end)  as ">120 days"

from

   store_sales

  ,store_returns

  ,store

  ,date_dim d1

  ,date_dim d2

where

    d2.d_year = 2000

and d2.d_moy  = 9

and ss_ticket_number = sr_ticket_number

and ss_item_sk = sr_item_sk

and ss_sold_date_sk   = d1.d_date_sk

and sr_returned_date_sk   = d2.d_date_sk

and ss_customer_sk = sr_customer_sk

and ss_store_sk = s_store_sk

group by

   s_store_name

  ,s_company_id

  ,s_street_number

  ,s_street_name

  ,s_street_type

  ,s_suite_number

  ,s_city

  ,s_county

  ,s_state

  ,s_zip

order by s_store_name

        ,s_company_id

        ,s_street_number

        ,s_street_name

        ,s_street_type

        ,s_suite_number

        ,s_city

        ,s_county

        ,s_state

        ,s_zip

 ) A limit  100;

Q51.sql

WITH web_v1 as (

select

  ws_item_sk item_sk, d_date,

  sum(sum(ws_sales_price))

      over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales

from web_sales

    ,date_dim

where ws_sold_date_sk=d_date_sk

  and d_month_seq between 1212 and 1212+11

  and ws_item_sk is not NULL

group by ws_item_sk, d_date),

store_v1 as (

select

  ss_item_sk item_sk, d_date,

  sum(sum(ss_sales_price))

      over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales

from store_sales

    ,date_dim

where ss_sold_date_sk=d_date_sk

  and d_month_seq between 1212 and 1212+11

  and ss_item_sk is not NULL

group by ss_item_sk, d_date)

select * from ( select  *

from (select item_sk

     ,d_date

     ,web_sales

     ,store_sales

     ,max(web_sales)

         over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative

     ,max(store_sales)

         over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative

     from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk

                 ,case when web.d_date is not null then web.d_date else store.d_date end d_date

                 ,web.cume_sales web_sales

                 ,store.cume_sales store_sales

           from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk

                                                          and web.d_date = store.d_date)

          )x )y

where web_cumulative > store_cumulative

order by item_sk

        ,d_date

 ) A limit  100;

Q52.sql

select * from (select  dt.d_year

  ,item.i_brand_id brand_id

  ,item.i_brand brand

  ,sum(ss_ext_sales_price) ext_price

 from date_dim dt

     ,store_sales

     ,item

 where dt.d_date_sk = store_sales.ss_sold_date_sk

    and store_sales.ss_item_sk = item.i_item_sk

    and item.i_manager_id = 1

    and dt.d_moy=12

    and dt.d_year=1998

 group by dt.d_year

  ,item.i_brand

  ,item.i_brand_id

 order by dt.d_year

  ,ext_price desc

  ,brand_id

 ) A limit  100 ;

Q53.sql

select * from (select  * from

(select i_manufact_id,

sum(ss_sales_price) sum_sales,

avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales

from item, store_sales, date_dim, store

where ss_item_sk = i_item_sk and

ss_sold_date_sk = d_date_sk and

ss_store_sk = s_store_sk and

d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and

((i_category in ('Books','Children','Electronics') and

i_class in ('personal','portable','reference','self-help') and

i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',

'exportiunivamalg #9','scholaramalgamalg #9'))

or(i_category in ('Women','Music','Men') and

i_class in ('accessories','classical','fragrances','pants') and

i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',

'importoamalg #1')))

group by i_manufact_id, d_qoy ) tmp1

where case when avg_quarterly_sales > 0

then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales

else null end > 0.1

order by avg_quarterly_sales,

 sum_sales,

 i_manufact_id

 ) A limit  100;

Q54.sql

with my_customers as (

 select distinct c_customer_sk

        , c_current_addr_sk

 from   

        ( select cs_sold_date_sk sold_date_sk,

                 cs_bill_customer_sk customer_sk,

                 cs_item_sk item_sk

          from   catalog_sales

          union all

          select ws_sold_date_sk sold_date_sk,

                 ws_bill_customer_sk customer_sk,

                 ws_item_sk item_sk

          from   web_sales

         ) cs_or_ws_sales,

         item,

         date_dim,

         customer

 where   sold_date_sk = d_date_sk

         and item_sk = i_item_sk

         and i_category = 'Jewelry'

         and i_class = 'consignment'

         and c_customer_sk = cs_or_ws_sales.customer_sk

         and d_moy = 3

         and d_year = 1999

 )

 , my_revenue as (

 select c_customer_sk,

        sum(ss_ext_sales_price) as revenue

 from   my_customers,

        store_sales,

        customer_address,

        store,

        date_dim

 where  c_current_addr_sk = ca_address_sk

        and ca_county = s_county

        and ca_state = s_state

        and ss_sold_date_sk = d_date_sk

        and c_customer_sk = ss_customer_sk

        and d_month_seq between (select distinct d_month_seq+1

                                 from   date_dim where d_year = 1999 and d_moy = 3)

                           and  (select distinct d_month_seq+3

                                 from   date_dim where d_year = 1999 and d_moy = 3)

 group by c_customer_sk

 )

 , segments as

 (select cast((revenue/50) as int) as segment

  from   my_revenue

 )

 select * from ( select  segment, count(*) as num_customers, segment*50 as segment_base

 from segments

 group by segment

 order by segment, num_customers

  ) A limit  100;

Q55.sql

select * from (select  i_brand_id brand_id, i_brand brand,

  sum(ss_ext_sales_price) ext_price

 from date_dim, store_sales, item

 where d_date_sk = ss_sold_date_sk

  and ss_item_sk = i_item_sk

  and i_manager_id=36

  and d_moy=12

  and d_year=2001

 group by i_brand, i_brand_id

 order by ext_price desc, i_brand_id

 ) A limit  100 ;

Q56.sql

with ss as (

 select i_item_id,sum(ss_ext_sales_price) total_sales

 from

  store_sales,

  date_dim,

         customer_address,

         item

 where i_item_id in (select

     i_item_id

from item

where i_color in ('orchid','chiffon','lace'))

 and     ss_item_sk              = i_item_sk

 and     ss_sold_date_sk         = d_date_sk

 and     d_year                  = 2000

 and     d_moy                   = 1

 and     ss_addr_sk              = ca_address_sk

 and     ca_gmt_offset           = -8

 group by i_item_id),

 cs as (

 select i_item_id,sum(cs_ext_sales_price) total_sales

 from

  catalog_sales,

  date_dim,

         customer_address,

         item

 where

         i_item_id               in (select

  i_item_id

from item

where i_color in ('orchid','chiffon','lace'))

 and     cs_item_sk              = i_item_sk

 and     cs_sold_date_sk         = d_date_sk

 and     d_year                  = 2000

 and     d_moy                   = 1

 and     cs_bill_addr_sk         = ca_address_sk

 and     ca_gmt_offset           = -8

 group by i_item_id),

 ws as (

 select i_item_id,sum(ws_ext_sales_price) total_sales

 from

  web_sales,

  date_dim,

         customer_address,

         item

 where

         i_item_id               in (select

  i_item_id

from item

where i_color in ('orchid','chiffon','lace'))

 and     ws_item_sk              = i_item_sk

 and     ws_sold_date_sk         = d_date_sk

 and     d_year                  = 2000

 and     d_moy                   = 1

 and     ws_bill_addr_sk         = ca_address_sk

 and     ca_gmt_offset           = -8

 group by i_item_id)

 select * from ( select  i_item_id ,sum(total_sales) total_sales

 from  (select * from ss

        union all

        select * from cs

        union all

        select * from ws) tmp1

 group by i_item_id

 order by total_sales

  ) A limit  100;

Q57.sql

with v1 as(

 select i_category, i_brand,

        cc_name,

        d_year, d_moy,

        sum(cs_sales_price) sum_sales,

        avg(sum(cs_sales_price)) over

          (partition by i_category, i_brand,

                     cc_name, d_year)

          avg_monthly_sales,

        rank() over

          (partition by i_category, i_brand,

                     cc_name

           order by d_year, d_moy) rn

 from item, catalog_sales, date_dim, call_center

 where cs_item_sk = i_item_sk and

       cs_sold_date_sk = d_date_sk and

       cc_call_center_sk= cs_call_center_sk and

       (

         d_year = 2000 or

         ( d_year = 2000-1 and d_moy =12) or

         ( d_year = 2000+1 and d_moy =1)

       )

 group by i_category, i_brand,

          cc_name , d_year, d_moy),

 v2 as(

 select v1.i_category, v1.i_brand

        ,v1.d_year, v1.d_moy

        ,v1.avg_monthly_sales

        ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum

 from v1, v1 v1_lag, v1 v1_lead

 where v1.i_category = v1_lag.i_category and

       v1.i_category = v1_lead.i_category and

       v1.i_brand = v1_lag.i_brand and

       v1.i_brand = v1_lead.i_brand and

       v1. cc_name = v1_lag. cc_name and

       v1. cc_name = v1_lead. cc_name and

       v1.rn = v1_lag.rn + 1 and

       v1.rn = v1_lead.rn - 1)

 select * from ( select  *

 from v2

 where  d_year = 2000 and

        avg_monthly_sales > 0 and

        case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1

 order by sum_sales - avg_monthly_sales, 3

  ) A limit  100;

Q58.sql

with ss_items as

 (select i_item_id item_id

        ,sum(ss_ext_sales_price) ss_item_rev

 from store_sales

     ,item

     ,date_dim

 where ss_item_sk = i_item_sk

   and d_date in (select d_date

                  from date_dim

                  where d_week_seq = (select d_week_seq

                                      from date_dim

                                      where d_date = '1998-02-19'))

   and ss_sold_date_sk   = d_date_sk

 group by i_item_id),

 cs_items as

 (select i_item_id item_id

        ,sum(cs_ext_sales_price) cs_item_rev

  from catalog_sales

      ,item

      ,date_dim

 where cs_item_sk = i_item_sk

  and  d_date in (select d_date

                  from date_dim

                  where d_week_seq = (select d_week_seq

                                      from date_dim

                                      where d_date = '1998-02-19'))

  and  cs_sold_date_sk = d_date_sk

 group by i_item_id),

 ws_items as

 (select i_item_id item_id

        ,sum(ws_ext_sales_price) ws_item_rev

  from web_sales

      ,item

      ,date_dim

 where ws_item_sk = i_item_sk

  and  d_date in (select d_date

                  from date_dim

                  where d_week_seq =(select d_week_seq

                                     from date_dim

                                     where d_date = '1998-02-19'))

  and ws_sold_date_sk   = d_date_sk

 group by i_item_id)

 select * from ( select  ss_items.item_id

       ,ss_item_rev

       ,ss_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ss_dev

       ,cs_item_rev

       ,cs_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 cs_dev

       ,ws_item_rev

       ,ws_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ws_dev

       ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average

 from ss_items,cs_items,ws_items

 where ss_items.item_id=cs_items.item_id

   and ss_items.item_id=ws_items.item_id

   and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev

   and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev

   and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev

   and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev

   and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev

   and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev

 order by item_id

         ,ss_item_rev

  ) A limit  100;

Q59.sql

with wss as

 (select d_week_seq,

        ss_store_sk,

        sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales,

        sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales,

        sum(case when (d_day_name='Tuesday') then ss_sales_price else  null end) tue_sales,

        sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales,

        sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales,

        sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales,

        sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales

 from store_sales,date_dim

 where d_date_sk = ss_sold_date_sk

 group by d_week_seq,ss_store_sk

 )

 select * from ( select  s_store_name1,s_store_id1,d_week_seq1

       ,sun_sales1/sun_sales2,mon_sales1/mon_sales2

       ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2

       ,fri_sales1/fri_sales2,sat_sales1/sat_sales2

 from

 (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1

        ,s_store_id s_store_id1,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 wss,store,date_dim d

  where d.d_week_seq = wss.d_week_seq and

        ss_store_sk = s_store_sk and

        d_month_seq between 1185 and 1185 + 11) y,

 (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2

        ,s_store_id s_store_id2,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 wss,store,date_dim d

  where d.d_week_seq = wss.d_week_seq and

        ss_store_sk = s_store_sk and

        d_month_seq between 1185+ 12 and 1185 + 23) x

 where s_store_id1=s_store_id2

   and d_week_seq1=d_week_seq2-52

 order by s_store_name1,s_store_id1,d_week_seq1

 ) A limit  100;

Q60.sql

with ss as (

 select

          i_item_id,sum(ss_ext_sales_price) total_sales

 from

  store_sales,

  date_dim,

         customer_address,

         item

 where

         i_item_id in (select

  i_item_id

from

 item

where i_category in ('Children'))

 and     ss_item_sk              = i_item_sk

 and     ss_sold_date_sk         = d_date_sk

 and     d_year                  = 1999

 and     d_moy                   = 9

 and     ss_addr_sk              = ca_address_sk

 and     ca_gmt_offset           = -6

 group by i_item_id),

 cs as (

 select

          i_item_id,sum(cs_ext_sales_price) total_sales

 from

  catalog_sales,

  date_dim,

         customer_address,

         item

 where

         i_item_id               in (select

  i_item_id

from

 item

where i_category in ('Children'))

 and     cs_item_sk              = i_item_sk

 and     cs_sold_date_sk         = d_date_sk

 and     d_year                  = 1999

 and     d_moy                   = 9

 and     cs_bill_addr_sk         = ca_address_sk

 and     ca_gmt_offset           = -6

 group by i_item_id),

 ws as (

 select

          i_item_id,sum(ws_ext_sales_price) total_sales

 from

  web_sales,

  date_dim,

         customer_address,

         item

 where

         i_item_id               in (select

  i_item_id

from

 item

where i_category in ('Children'))

 and     ws_item_sk              = i_item_sk

 and     ws_sold_date_sk         = d_date_sk

 and     d_year                  = 1999

 and     d_moy                   = 9

 and     ws_bill_addr_sk         = ca_address_sk

 and     ca_gmt_offset           = -6

 group by i_item_id)

 select * from ( select   

  i_item_id

,sum(total_sales) total_sales

 from  (select * from ss

        union all

        select * from cs

        union all

        select * from ws) tmp1

 group by i_item_id

 order by i_item_id

      ,total_sales

  ) A limit  100;

 

Q61.sql

select * from (select  promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100

from

  (select sum(ss_ext_sales_price) promotions

   from  store_sales

        ,store

        ,promotion

        ,date_dim

        ,customer

        ,customer_address

        ,item

   where ss_sold_date_sk = d_date_sk

   and   ss_store_sk = s_store_sk

   and   ss_promo_sk = p_promo_sk

   and   ss_customer_sk= c_customer_sk

   and   ca_address_sk = c_current_addr_sk

   and   ss_item_sk = i_item_sk

   and   ca_gmt_offset = -7

   and   i_category = 'Books'

   and   (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')

   and   s_gmt_offset = -7

   and   d_year = 1999

   and   d_moy  = 11) promotional_sales,

  (select sum(ss_ext_sales_price) total

   from  store_sales

        ,store

        ,date_dim

        ,customer

        ,customer_address

        ,item

   where ss_sold_date_sk = d_date_sk

   and   ss_store_sk = s_store_sk

   and   ss_customer_sk= c_customer_sk

   and   ca_address_sk = c_current_addr_sk

   and   ss_item_sk = i_item_sk

   and   ca_gmt_offset = -7

   and   i_category = 'Books'

   and   s_gmt_offset = -7

   and   d_year = 1999

   and   d_moy  = 11) all_sales

order by promotions, total

 ) A limit  100;

Q62.sql

 select * from (select  

   substr(w_warehouse_name,1,20)

  ,sm_type

  ,web_name

  ,sum(case when (ws_ship_date_sk - ws_sold_date_sk <= 30 ) then 1 else 0 end)  as "30 days"

  ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 30) and

                 (ws_ship_date_sk - ws_sold_date_sk <= 60) then 1 else 0 end )  as "31-60 days"

  ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 60) and

                 (ws_ship_date_sk - ws_sold_date_sk <= 90) then 1 else 0 end)  as "61-90 days"

  ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 90) and

                 (ws_ship_date_sk - ws_sold_date_sk <= 120) then 1 else 0 end)  as "91-120 days"

  ,sum(case when (ws_ship_date_sk - ws_sold_date_sk  > 120) then 1 else 0 end)  as ">120 days"

from

   web_sales

  ,warehouse

  ,ship_mode

  ,web_site

  ,date_dim

where

    d_month_seq between 1212 and 1212 + 11

and ws_ship_date_sk   = d_date_sk

and ws_warehouse_sk   = w_warehouse_sk

and ws_ship_mode_sk   = sm_ship_mode_sk

and ws_web_site_sk    = web_site_sk

group by

   substr(w_warehouse_name,1,20)

  ,sm_type

  ,web_name

order by substr(w_warehouse_name,1,20)

        ,sm_type

       ,web_name

 ) A limit  100;

 

Q63.sql

select * from (select  *

from (select i_manager_id

             ,sum(ss_sales_price) sum_sales

             ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales

      from item

          ,store_sales

          ,date_dim

          ,store

      where ss_item_sk = i_item_sk

        and ss_sold_date_sk = d_date_sk

        and ss_store_sk = s_store_sk

        and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)

        and ((    i_category in ('Books','Children','Electronics')

              and i_class in ('personal','portable','reference','self-help')

              and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',

                  'exportiunivamalg #9','scholaramalgamalg #9'))

           or(    i_category in ('Women','Music','Men')

              and i_class in ('accessories','classical','fragrances','pants')

              and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',

                 'importoamalg #1')))

group by i_manager_id, d_moy) tmp1

where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1

order by i_manager_id

        ,avg_monthly_sales

        ,sum_sales

 ) A limit  100;

Q64.sql

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_street_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(*) cnt

     ,sum(ss_wholesale_cost) s1

     ,sum(ss_list_price) s2

     ,sum(ss_coupon_amt) 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 ('maroon','burnished','dim','steel','navajo','chocolate') and

         i_current_price between 35 and 35 + 10 and

         i_current_price between 35 + 1 and 35 + 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_street_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 as s11

     ,cs1.s2 as s21

     ,cs1.s3 as s31

     ,cs2.s1 as s12

     ,cs2.s2 as s22

     ,cs2.s3 as s32

     ,cs2.syear

     ,cs2.cnt

from cross_sales cs1,cross_sales cs2

where cs1.item_sk=cs2.item_sk and

     cs1.syear = 2000 and

     cs2.syear = 2000 + 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;

Q65.sql

select * from (select

s_store_name,

i_item_desc,

sc.revenue,

i_current_price,

i_wholesale_cost,

i_brand

 from store, item,

     (select ss_store_sk, avg(revenue) as ave

  from

      (select  ss_store_sk, ss_item_sk,

       sum(ss_sales_price) as revenue

  from store_sales, date_dim

  where ss_sold_date_sk = d_date_sk and d_month_seq between 1212 and 1212+11

  group by ss_store_sk, ss_item_sk) sa

  group by ss_store_sk) sb,

     (select  ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue

  from store_sales, date_dim

  where ss_sold_date_sk = d_date_sk and d_month_seq between 1212 and 1212+11

  group by ss_store_sk, ss_item_sk) sc

 where sb.ss_store_sk = sc.ss_store_sk and

       sc.revenue <= 0.1 * sb.ave and

       s_store_sk = sc.ss_store_sk and

       i_item_sk = sc.ss_item_sk

 order by s_store_name, i_item_desc

 ) A limit  100;

 

Q66.sql

select * from (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

  ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers

       ,d_year as year

  ,sum(case when d_moy = 1

  then ws_sales_price* ws_quantity else 0 end) as jan_sales

  ,sum(case when d_moy = 2

  then ws_sales_price* ws_quantity else 0 end) as feb_sales

  ,sum(case when d_moy = 3

  then ws_sales_price* ws_quantity else 0 end) as mar_sales

  ,sum(case when d_moy = 4

  then ws_sales_price* ws_quantity else 0 end) as apr_sales

  ,sum(case when d_moy = 5

  then ws_sales_price* ws_quantity else 0 end) as may_sales

  ,sum(case when d_moy = 6

  then ws_sales_price* ws_quantity else 0 end) as jun_sales

  ,sum(case when d_moy = 7

  then ws_sales_price* ws_quantity else 0 end) as jul_sales

  ,sum(case when d_moy = 8

  then ws_sales_price* ws_quantity else 0 end) as aug_sales

  ,sum(case when d_moy = 9

  then ws_sales_price* ws_quantity else 0 end) as sep_sales

  ,sum(case when d_moy = 10

  then ws_sales_price* ws_quantity else 0 end) as oct_sales

  ,sum(case when d_moy = 11

  then ws_sales_price* ws_quantity else 0 end) as nov_sales

  ,sum(case when d_moy = 12

  then ws_sales_price* ws_quantity else 0 end) as dec_sales

  ,sum(case when d_moy = 1

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net

  ,sum(case when d_moy = 2

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net

  ,sum(case when d_moy = 3

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net

  ,sum(case when d_moy = 4

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net

  ,sum(case when d_moy = 5

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net

  ,sum(case when d_moy = 6

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net

  ,sum(case when d_moy = 7

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net

  ,sum(case when d_moy = 8

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net

  ,sum(case when d_moy = 9

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net

  ,sum(case when d_moy = 10

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net

  ,sum(case when d_moy = 11

  then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net

  ,sum(case when d_moy = 12

  then ws_net_paid_inc_tax * 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 = 2002

  and t_time between 49530 and 49530+28800

  and sm_carrier in ('DIAMOND','AIRBORNE')

     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

  ,'DIAMOND' || ',' || 'AIRBORNE' 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_inc_ship_tax * cs_quantity else 0 end) as jan_net

  ,sum(case when d_moy = 2

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as feb_net

  ,sum(case when d_moy = 3

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as mar_net

  ,sum(case when d_moy = 4

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as apr_net

  ,sum(case when d_moy = 5

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as may_net

  ,sum(case when d_moy = 6

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jun_net

  ,sum(case when d_moy = 7

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jul_net

  ,sum(case when d_moy = 8

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as aug_net

  ,sum(case when d_moy = 9

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as sep_net

  ,sum(case when d_moy = 10

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as oct_net

  ,sum(case when d_moy = 11

  then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as nov_net

  ,sum(case when d_moy = 12

  then cs_net_paid_inc_ship_tax * 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 = 2002

  and t_time between 49530 AND 49530+28800

  and sm_carrier in ('DIAMOND','AIRBORNE')

     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

  ) A limit  100;

Q67.sql

select * from (select  *

from (select i_category

            ,i_class

            ,i_brand

            ,i_product_name

            ,d_year

            ,d_qoy

            ,d_moy

            ,s_store_id

            ,sumsales

            ,rank() over (partition by i_category order by sumsales desc) rk

      from (select i_category

                  ,i_class

                  ,i_brand

                  ,i_product_name

                  ,d_year

                  ,d_qoy

                  ,d_moy

                  ,s_store_id

                  ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales

            from store_sales

                ,date_dim

                ,store

                ,item

       where  ss_sold_date_sk=d_date_sk

          and ss_item_sk=i_item_sk

          and ss_store_sk = s_store_sk

          and d_month_seq between 1212 and 1212+11

       group by  rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2

where rk <= 100

order by i_category

        ,i_class

        ,i_brand

        ,i_product_name

        ,d_year

        ,d_qoy

        ,d_moy

        ,s_store_id

        ,sumsales

        ,rk

 ) A limit  100;

Q68.sql

select * from (select  c_last_name

       ,c_first_name

       ,ca_city

       ,bought_city

       ,ss_ticket_number

       ,extended_price

       ,extended_tax

       ,list_price

 from (select ss_ticket_number

             ,ss_customer_sk

             ,ca_city bought_city

             ,sum(ss_ext_sales_price) extended_price

             ,sum(ss_ext_list_price) list_price

             ,sum(ss_ext_tax) extended_tax

       from store_sales

           ,date_dim

           ,store

           ,household_demographics

           ,customer_address

       where store_sales.ss_sold_date_sk = date_dim.d_date_sk

         and store_sales.ss_store_sk = store.s_store_sk  

        and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk

        and store_sales.ss_addr_sk = customer_address.ca_address_sk

        and date_dim.d_dom between 1 and 2

        and (household_demographics.hd_dep_count = 5 or

             household_demographics.hd_vehicle_count= 3)

        and date_dim.d_year in (1999,1999+1,1999+2)

        and store.s_city in ('Midway','Fairview')

       group by ss_ticket_number

               ,ss_customer_sk

               ,ss_addr_sk,ca_city) dn

      ,customer

      ,customer_address current_addr

 where ss_customer_sk = c_customer_sk

   and customer.c_current_addr_sk = current_addr.ca_address_sk

   and current_addr.ca_city <> bought_city

 order by c_last_name

         ,ss_ticket_number

  ) A limit  100;

Q69.sql

select * from (select  

  cd_gender,

  cd_marital_status,

  cd_education_status,

  count(*) cnt1,

  cd_purchase_estimate,

  count(*) cnt2,

  cd_credit_rating,

  count(*) cnt3

 from

  customer c,customer_address ca,customer_demographics

 where

  c.c_current_addr_sk = ca.ca_address_sk and

  ca_state in ('CO','IL','MN') and

  cd_demo_sk = c.c_current_cdemo_sk and

  exists (select *

          from store_sales,date_dim

          where c.c_customer_sk = ss_customer_sk and

                ss_sold_date_sk = d_date_sk and

                d_year = 1999 and

                d_moy between 1 and 1+2) and

   (not exists (select *

            from web_sales,date_dim

            where c.c_customer_sk = ws_bill_customer_sk and

                  ws_sold_date_sk = d_date_sk and

                  d_year = 1999 and

                  d_moy between 1 and 1+2) and

    not exists (select *

            from catalog_sales,date_dim

            where c.c_customer_sk = cs_ship_customer_sk and

                  cs_sold_date_sk = d_date_sk and

                  d_year = 1999 and

                  d_moy between 1 and 1+2))

 group by cd_gender,

          cd_marital_status,

          cd_education_status,

          cd_purchase_estimate,

          cd_credit_rating

 order by cd_gender,

          cd_marital_status,

          cd_education_status,

          cd_purchase_estimate,

          cd_credit_rating

  ) A limit  100;

Q70.sql

select * from (select  

    sum(ss_net_profit) as total_sum

   ,s_state

   ,s_county

   ,grouping(s_state)+grouping(s_county) as lochierarchy

   ,rank() over (

  partition by grouping(s_state)+grouping(s_county),

  case when grouping(s_county) = 0 then s_state end

  order by sum(ss_net_profit) desc) as rank_within_parent

 from

    store_sales

   ,date_dim       d1

   ,store

 where

    d1.d_month_seq between 1212 and 1212+11

 and d1.d_date_sk = ss_sold_date_sk

 and s_store_sk  = ss_store_sk

 and s_state in

             ( select s_state

               from  (select s_state as s_state,

      rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking

                      from   store_sales, store, date_dim

                      where  d_month_seq between 1212 and 1212+11

      and d_date_sk = ss_sold_date_sk

      and s_store_sk  = ss_store_sk

                      group by s_state

                     ) tmp1

               where ranking <= 5

             )

 group by rollup(s_state,s_county)

 order by

   lochierarchy desc

  ,case when lochierarchy = 0 then s_state end

  ,rank_within_parent

  ) A limit  100;

 

Q71.sql

select i_brand_id brand_id, i_brand brand,t_hour,t_minute,

  sum(ext_price) ext_price

 from item, (select ws_ext_sales_price as ext_price,

                        ws_sold_date_sk as sold_date_sk,

                        ws_item_sk as sold_item_sk,

                        ws_sold_time_sk as time_sk  

                 from web_sales,date_dim

                 where d_date_sk = ws_sold_date_sk

                   and d_moy=12

                   and d_year=2000

                 union all

                 select cs_ext_sales_price as ext_price,

                        cs_sold_date_sk as sold_date_sk,

                        cs_item_sk as sold_item_sk,

                        cs_sold_time_sk as time_sk

                 from catalog_sales,date_dim

                 where d_date_sk = cs_sold_date_sk

                   and d_moy=12

                   and d_year=2000

                 union all

                 select ss_ext_sales_price as ext_price,

                        ss_sold_date_sk as sold_date_sk,

                        ss_item_sk as sold_item_sk,

                        ss_sold_time_sk as time_sk

                 from store_sales,date_dim

                 where d_date_sk = ss_sold_date_sk

                   and d_moy=12

                   and d_year=2000

                 ) tmp,time_dim

 where

   sold_item_sk = i_item_sk

   and i_manager_id=1

   and time_sk = t_time_sk

   and (t_meal_time = 'breakfast' or t_meal_time = 'dinner')

 group by i_brand, i_brand_id,t_hour,t_minute

 order by ext_price desc, i_brand_id

 ;

Q72.sql

select * from (select  i_item_desc

      ,w_warehouse_name

      ,d1.d_week_seq

      ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo

      ,sum(case when p_promo_sk is not null then 1 else 0 end) promo

      ,count(*) total_cnt

from catalog_sales

join inventory on (cs_item_sk = inv_item_sk)

join warehouse on (w_warehouse_sk=inv_warehouse_sk)

join item on (i_item_sk = cs_item_sk)

join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)

join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)

join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)

join date_dim d2 on (inv_date_sk = d2.d_date_sk)

join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)

left outer join promotion on (cs_promo_sk=p_promo_sk)

left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)

where d1.d_week_seq = d2.d_week_seq

  and inv_quantity_on_hand < cs_quantity

  and d3.d_date > d1.d_date + 5

  and hd_buy_potential = '1001-5000'

  and d1.d_year = 2001

  and cd_marital_status = 'M'

group by i_item_desc,w_warehouse_name,d1.d_week_seq

order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq

 ) A limit  100;

Q73.sql

select c_last_name

       ,c_first_name

       ,c_salutation

       ,c_preferred_cust_flag

       ,ss_ticket_number

       ,cnt from

   (select ss_ticket_number

          ,ss_customer_sk

          ,count(*) cnt

    from store_sales,date_dim,store,household_demographics

    where store_sales.ss_sold_date_sk = date_dim.d_date_sk

    and store_sales.ss_store_sk = store.s_store_sk  

    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk

    and date_dim.d_dom between 1 and 2

    and (household_demographics.hd_buy_potential = '>10000' or

         household_demographics.hd_buy_potential = 'Unknown')

    and household_demographics.hd_vehicle_count > 0

    and case when household_demographics.hd_vehicle_count > 0 then

             household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1

    and date_dim.d_year in (1998,1998+1,1998+2)

    and store.s_county in ('Williamson County','Williamson County','Williamson County','Williamson County')

    group by ss_ticket_number,ss_customer_sk) dj,customer

    where ss_customer_sk = c_customer_sk

      and cnt between 1 and 5

    order by cnt desc, c_last_name asc;

Q74.sql

with year_total as (

 select c_customer_id customer_id

       ,c_first_name customer_first_name

       ,c_last_name customer_last_name

       ,d_year as year

       ,max(ss_net_paid) 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

   and d_year in (2001,2001+1)

 group by c_customer_id

         ,c_first_name

         ,c_last_name

         ,d_year

 union all

 select c_customer_id customer_id

       ,c_first_name customer_first_name

       ,c_last_name customer_last_name

       ,d_year as year

       ,max(ws_net_paid) 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

   and d_year in (2001,2001+1)

 group by c_customer_id

         ,c_first_name

         ,c_last_name

         ,d_year

         )

 select * from ( select

        t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name

 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.year = 2001

         and t_s_secyear.year = 2001+1

         and t_w_firstyear.year = 2001

         and t_w_secyear.year = 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 null end

           > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end

 order by 2,1,3

 ) A limit  100;

Q75.sql

WITH all_sales AS (

 SELECT d_year

       ,i_brand_id

       ,i_class_id

       ,i_category_id

       ,i_manufact_id

       ,SUM(sales_cnt) AS sales_cnt

       ,SUM(sales_amt) AS sales_amt

 FROM (SELECT d_year

             ,i_brand_id

             ,i_class_id

             ,i_category_id

             ,i_manufact_id

             ,cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt

             ,cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt

       FROM catalog_sales JOIN item ON i_item_sk=cs_item_sk

                          JOIN date_dim ON d_date_sk=cs_sold_date_sk

                          LEFT JOIN catalog_returns ON (cs_order_number=cr_order_number

                                                    AND cs_item_sk=cr_item_sk)

       WHERE i_category='Sports'

       UNION

       SELECT d_year

             ,i_brand_id

             ,i_class_id

             ,i_category_id

             ,i_manufact_id

             ,ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt

             ,ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt

       FROM store_sales JOIN item ON i_item_sk=ss_item_sk

                        JOIN date_dim ON d_date_sk=ss_sold_date_sk

                        LEFT JOIN store_returns ON (ss_ticket_number=sr_ticket_number

                                                AND ss_item_sk=sr_item_sk)

       WHERE i_category='Sports'

       UNION

       SELECT d_year

             ,i_brand_id

             ,i_class_id

             ,i_category_id

             ,i_manufact_id

             ,ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt

             ,ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt

       FROM web_sales JOIN item ON i_item_sk=ws_item_sk

                      JOIN date_dim ON d_date_sk=ws_sold_date_sk

                      LEFT JOIN web_returns ON (ws_order_number=wr_order_number

                                            AND ws_item_sk=wr_item_sk)

       WHERE i_category='Sports') sales_detail

 GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id)

select * from ( SELECT  prev_yr.d_year AS prev_year

                          ,curr_yr.d_year AS year

                          ,curr_yr.i_brand_id

                          ,curr_yr.i_class_id

                          ,curr_yr.i_category_id

                          ,curr_yr.i_manufact_id

                          ,prev_yr.sales_cnt AS prev_yr_cnt

                          ,curr_yr.sales_cnt AS curr_yr_cnt

                          ,curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff

                          ,curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff

 FROM all_sales curr_yr, all_sales prev_yr

 WHERE curr_yr.i_brand_id=prev_yr.i_brand_id

   AND curr_yr.i_class_id=prev_yr.i_class_id

   AND curr_yr.i_category_id=prev_yr.i_category_id

   AND curr_yr.i_manufact_id=prev_yr.i_manufact_id

   AND curr_yr.d_year=2002

   AND prev_yr.d_year=2002-1

   AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9

 ORDER BY sales_cnt_diff

  ) A limit  100;

 

Q76.sql

select * from (select  channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM (

        SELECT 'store' as channel, 'ss_addr_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price

         FROM store_sales, item, date_dim

         WHERE ss_addr_sk IS NULL

           AND ss_sold_date_sk=d_date_sk

           AND ss_item_sk=i_item_sk

        UNION ALL

        SELECT 'web' as channel, 'ws_web_page_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price

         FROM web_sales, item, date_dim

         WHERE ws_web_page_sk IS NULL

           AND ws_sold_date_sk=d_date_sk

           AND ws_item_sk=i_item_sk

        UNION ALL

        SELECT 'catalog' as channel, 'cs_warehouse_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price

         FROM catalog_sales, item, date_dim

         WHERE cs_warehouse_sk IS NULL

           AND cs_sold_date_sk=d_date_sk

           AND cs_item_sk=i_item_sk) foo

GROUP BY channel, col_name, d_year, d_qoy, i_category

ORDER BY channel, col_name, d_year, d_qoy, i_category

 ) A limit  100;

Q77.sql

with ss as

 (select s_store_sk,

         sum(ss_ext_sales_price) as sales,

         sum(ss_net_profit) as profit

 from store_sales,

      date_dim,

      store

 where ss_sold_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

       and ss_store_sk = s_store_sk

 group by s_store_sk)

 ,

 sr as

 (select s_store_sk,

         sum(sr_return_amt) as returns,

         sum(sr_net_loss) as profit_loss

 from store_returns,

      date_dim,

      store

 where sr_returned_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

       and sr_store_sk = s_store_sk

 group by s_store_sk),

 cs as

 (select cs_call_center_sk,

        sum(cs_ext_sales_price) as sales,

        sum(cs_net_profit) as profit

 from catalog_sales,

      date_dim

 where cs_sold_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

 group by cs_call_center_sk

 ),

 cr as

 (select cr_call_center_sk,

         sum(cr_return_amount) as returns,

         sum(cr_net_loss) as profit_loss

 from catalog_returns,

      date_dim

 where cr_returned_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

 group by cr_call_center_sk

 ),

 ws as

 ( select wp_web_page_sk,

        sum(ws_ext_sales_price) as sales,

        sum(ws_net_profit) as profit

 from web_sales,

      date_dim,

      web_page

 where ws_sold_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

       and ws_web_page_sk = wp_web_page_sk

 group by wp_web_page_sk),

 wr as

 (select wp_web_page_sk,

        sum(wr_return_amt) as returns,

        sum(wr_net_loss) as profit_loss

 from web_returns,

      date_dim,

      web_page

 where wr_returned_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

       and wr_web_page_sk = wp_web_page_sk

 group by wp_web_page_sk)

 select * from ( select  channel

        , id

        , sum(sales) as sales

        , sum(returns) as returns

        , sum(profit) as profit

 from

 (select 'store channel' as channel

        , ss.s_store_sk as id

        , sales

        , coalesce(returns, 0) as returns

        , (profit - coalesce(profit_loss,0)) as profit

 from   ss left join sr

        on  ss.s_store_sk = sr.s_store_sk

 union all

 select 'catalog channel' as channel

        , cs_call_center_sk as id

        , sales

        , returns

        , (profit - profit_loss) as profit

 from  cs

       , cr

 union all

 select 'web channel' as channel

        , ws.wp_web_page_sk as id

        , sales

        , coalesce(returns, 0) returns

        , (profit - coalesce(profit_loss,0)) as profit

 from   ws left join wr

        on  ws.wp_web_page_sk = wr.wp_web_page_sk

 ) x

 group by rollup (channel, id)

 order by channel

         ,id

  ) A limit  100;

Q78.sql

with ws as

  (select d_year AS ws_sold_year, ws_item_sk,

    ws_bill_customer_sk ws_customer_sk,

    sum(ws_quantity) ws_qty,

    sum(ws_wholesale_cost) ws_wc,

    sum(ws_sales_price) ws_sp

   from web_sales

   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk

   join date_dim on ws_sold_date_sk = d_date_sk

   where wr_order_number is null

   group by d_year, ws_item_sk, ws_bill_customer_sk

   ),

cs as

  (select d_year AS cs_sold_year, cs_item_sk,

    cs_bill_customer_sk cs_customer_sk,

    sum(cs_quantity) cs_qty,

    sum(cs_wholesale_cost) cs_wc,

    sum(cs_sales_price) cs_sp

   from catalog_sales

   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk

   join date_dim on cs_sold_date_sk = d_date_sk

   where cr_order_number is null

   group by d_year, cs_item_sk, cs_bill_customer_sk

   ),

ss as

  (select d_year AS ss_sold_year, ss_item_sk,

    ss_customer_sk,

    sum(ss_quantity) ss_qty,

    sum(ss_wholesale_cost) ss_wc,

    sum(ss_sales_price) ss_sp

   from store_sales

   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk

   join date_dim on ss_sold_date_sk = d_date_sk

   where sr_ticket_number is null

   group by d_year, ss_item_sk, ss_customer_sk

   )

select * from ( select

ss_sold_year, ss_item_sk, ss_customer_sk,

round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,

ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,

coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,

coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,

coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price

from ss

left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)

left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)

where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000

order by

  ss_sold_year, ss_item_sk, ss_customer_sk,

  ss_qty desc, ss_wc desc, ss_sp desc,

  other_chan_qty,

  other_chan_wholesale_cost,

  other_chan_sales_price,

  round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)

 ) A limit  100;

Q79.sql

 select * from (select

  c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit

  from

   (select ss_ticket_number

          ,ss_customer_sk

          ,store.s_city

          ,sum(ss_coupon_amt) amt

          ,sum(ss_net_profit) profit

    from store_sales,date_dim,store,household_demographics

    where store_sales.ss_sold_date_sk = date_dim.d_date_sk

    and store_sales.ss_store_sk = store.s_store_sk  

    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk

    and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)

    and date_dim.d_dow = 1

    and date_dim.d_year in (1998,1998+1,1998+2)

    and store.s_number_employees between 200 and 295

    group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer

    where ss_customer_sk = c_customer_sk

 order by c_last_name,c_first_name,substr(s_city,1,30), profit

 ) A limit  100;

 

Q80.sql

with ssr as

 (select  s_store_id as store_id,

          sum(ss_ext_sales_price) as sales,

          sum(coalesce(sr_return_amt, 0)) as returns,

          sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit

  from store_sales left outer join store_returns on

         (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number),

     date_dim,

     store,

     item,

     promotion

 where ss_sold_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

       and ss_store_sk = s_store_sk

       and ss_item_sk = i_item_sk

       and i_current_price > 50

       and ss_promo_sk = p_promo_sk

       and p_channel_tv = 'N'

 group by s_store_id)

 ,

 csr as

 (select  cp_catalog_page_id as catalog_page_id,

          sum(cs_ext_sales_price) as sales,

          sum(coalesce(cr_return_amount, 0)) as returns,

          sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit

  from catalog_sales left outer join catalog_returns on

         (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),

     date_dim,

     catalog_page,

     item,

     promotion

 where cs_sold_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

        and cs_catalog_page_sk = cp_catalog_page_sk

       and cs_item_sk = i_item_sk

       and i_current_price > 50

       and cs_promo_sk = p_promo_sk

       and p_channel_tv = 'N'

group by cp_catalog_page_id)

 ,

 wsr as

 (select  web_site_id,

          sum(ws_ext_sales_price) as sales,

          sum(coalesce(wr_return_amt, 0)) as returns,

          sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit

  from web_sales left outer join web_returns on

         (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),

     date_dim,

     web_site,

     item,

     promotion

 where ws_sold_date_sk = d_date_sk

       and d_date between cast('1998-08-04' as date)

                  and (cast('1998-08-04' as date) +  30 days)

        and ws_web_site_sk = web_site_sk

       and ws_item_sk = i_item_sk

       and i_current_price > 50

       and ws_promo_sk = p_promo_sk

       and p_channel_tv = 'N'

group by web_site_id)

 select * from ( select  channel

        , id

        , sum(sales) as sales

        , sum(returns) as returns

        , sum(profit) as profit

 from

 (select 'store channel' as channel

        , 'store' || store_id as id

        , sales

        , returns

        , profit

 from   ssr

 union all

 select 'catalog channel' as channel

        , 'catalog_page' || catalog_page_id as id

        , sales

        , returns

        , profit

 from  csr

 union all

 select 'web channel' as channel

        , 'web_site' || web_site_id as id

        , sales

        , returns

        , profit

 from   wsr

 ) x

 group by rollup (channel, id)

 order by channel

         ,id

  ) A limit  100;

 

 

Q81.sql

with customer_total_return as

 (select cr_returning_customer_sk as ctr_customer_sk

        ,ca_state as ctr_state,

  sum(cr_return_amt_inc_tax) as ctr_total_return

 from catalog_returns

     ,date_dim

     ,customer_address

 where cr_returned_date_sk = d_date_sk

   and d_year =1998

   and cr_returning_addr_sk = ca_address_sk

 group by cr_returning_customer_sk

         ,ca_state )

 select * from ( select  c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name

                   ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset

                  ,ca_location_type,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 = 'IL'

       and ctr1.ctr_customer_sk = c_customer_sk

 order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name

                   ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset

                  ,ca_location_type,ctr_total_return

  ) A limit  100;

 

Q82.sql

select * from (select  i_item_id

       ,i_item_desc

       ,i_current_price

 from item, inventory, date_dim, store_sales

 where i_current_price between 30 and 30+30

 and inv_item_sk = i_item_sk

 and d_date_sk=inv_date_sk

 and d_date between cast('2002-05-30' as date) and (cast('2002-05-30' as date) +  60 days)

 and i_manufact_id in (437,129,727,663)

 and inv_quantity_on_hand between 100 and 500

 and ss_item_sk = i_item_sk

 group by i_item_id,i_item_desc,i_current_price

 order by i_item_id

  ) A limit  100;

Q83.sql

with sr_items as

 (select i_item_id item_id,

        sum(sr_return_quantity) sr_item_qty

 from store_returns,

      item,

      date_dim

 where sr_item_sk = i_item_sk

 and   d_date    in

(select d_date

from date_dim

where d_week_seq in

(select d_week_seq

from date_dim

  where d_date in ('1998-01-02','1998-10-15','1998-11-10')))

 and   sr_returned_date_sk   = d_date_sk

 group by i_item_id),

 cr_items as

 (select i_item_id item_id,

        sum(cr_return_quantity) cr_item_qty

 from catalog_returns,

      item,

      date_dim

 where cr_item_sk = i_item_sk

 and   d_date    in

(select d_date

from date_dim

where d_week_seq in

(select d_week_seq

from date_dim

  where d_date in ('1998-01-02','1998-10-15','1998-11-10')))

 and   cr_returned_date_sk   = d_date_sk

 group by i_item_id),

 wr_items as

 (select i_item_id item_id,

        sum(wr_return_quantity) wr_item_qty

 from web_returns,

      item,

      date_dim

 where wr_item_sk = i_item_sk

 and   d_date    in

(select d_date

from date_dim

where d_week_seq in

(select d_week_seq

from date_dim

where d_date in ('1998-01-02','1998-10-15','1998-11-10')))

 and   wr_returned_date_sk   = d_date_sk

 group by i_item_id)

 select * from ( select  sr_items.item_id

       ,sr_item_qty

       ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev

       ,cr_item_qty

       ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev

       ,wr_item_qty

       ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev

       ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average

 from sr_items

     ,cr_items

     ,wr_items

 where sr_items.item_id=cr_items.item_id

   and sr_items.item_id=wr_items.item_id

 order by sr_items.item_id

         ,sr_item_qty

  ) A limit  100;

Q84.sql

select * from (select  c_customer_id as customer_id

       , coalesce(c_last_name,'') || ', ' || coalesce(c_first_name,'') as customername

 from customer

     ,customer_address

     ,customer_demographics

     ,household_demographics

     ,income_band

     ,store_returns

 where ca_city         =  'Hopewell'

   and c_current_addr_sk = ca_address_sk

   and ib_lower_bound   >=  32287

   and ib_upper_bound   <=  32287 + 50000

   and ib_income_band_sk = hd_income_band_sk

   and cd_demo_sk = c_current_cdemo_sk

   and hd_demo_sk = c_current_hdemo_sk

   and sr_cdemo_sk = cd_demo_sk

 order by c_customer_id

  ) A limit  100;

Q85.sql

select * from (select  substr(r_reason_desc,1,20)

       ,avg(ws_quantity)

       ,avg(wr_refunded_cash)

       ,avg(wr_fee)

 from web_sales, web_returns, web_page, customer_demographics cd1,

      customer_demographics cd2, customer_address, date_dim, reason

 where ws_web_page_sk = wp_web_page_sk

   and ws_item_sk = wr_item_sk

   and ws_order_number = wr_order_number

   and ws_sold_date_sk = d_date_sk and d_year = 1998

   and cd1.cd_demo_sk = wr_refunded_cdemo_sk

   and cd2.cd_demo_sk = wr_returning_cdemo_sk

   and ca_address_sk = wr_refunded_addr_sk

   and r_reason_sk = wr_reason_sk

   and

   (

    (

     cd1.cd_marital_status = 'M'

     and

     cd1.cd_marital_status = cd2.cd_marital_status

     and

     cd1.cd_education_status = '4 yr Degree'

     and

     cd1.cd_education_status = cd2.cd_education_status

     and

     ws_sales_price between 100.00 and 150.00

    )

   or

    (

     cd1.cd_marital_status = 'D'

     and

     cd1.cd_marital_status = cd2.cd_marital_status

     and

     cd1.cd_education_status = 'Primary'

     and

     cd1.cd_education_status = cd2.cd_education_status

     and

     ws_sales_price between 50.00 and 100.00

    )

   or

    (

     cd1.cd_marital_status = 'U'

     and

     cd1.cd_marital_status = cd2.cd_marital_status

     and

     cd1.cd_education_status = 'Advanced Degree'

     and

     cd1.cd_education_status = cd2.cd_education_status

     and

     ws_sales_price between 150.00 and 200.00

    )

   )

   and

   (

    (

     ca_country = 'United States'

     and

     ca_state in ('KY', 'GA', 'NM')

     and ws_net_profit between 100 and 200  

    )

    or

    (

     ca_country = 'United States'

     and

     ca_state in ('MT', 'OR', 'IN')

     and ws_net_profit between 150 and 300  

    )

    or

    (

     ca_country = 'United States'

     and

     ca_state in ('WI', 'MO', 'WV')

     and ws_net_profit between 50 and 250  

    )

   )

group by r_reason_desc

order by substr(r_reason_desc,1,20)

        ,avg(ws_quantity)

        ,avg(wr_refunded_cash)

        ,avg(wr_fee)

 ) A limit  100;

 

Q86.sql

select * from (select   

    sum(ws_net_paid) as total_sum

   ,i_category

   ,i_class

   ,grouping(i_category)+grouping(i_class) as lochierarchy

   ,rank() over (

  partition by grouping(i_category)+grouping(i_class),

  case when grouping(i_class) = 0 then i_category end

  order by sum(ws_net_paid) desc) as rank_within_parent

 from

    web_sales

   ,date_dim       d1

   ,item

 where

    d1.d_month_seq between 1212 and 1212+11

 and d1.d_date_sk = ws_sold_date_sk

 and i_item_sk  = ws_item_sk

 group by rollup(i_category,i_class)

 order by

   lochierarchy desc,

   case when lochierarchy = 0 then i_category end,

   rank_within_parent

  ) A limit  100;

Q87.sql

select count(*)

from ((select distinct c_last_name, c_first_name, d_date

       from store_sales, date_dim, customer

       where store_sales.ss_sold_date_sk = date_dim.d_date_sk

         and store_sales.ss_customer_sk = customer.c_customer_sk

         and d_month_seq between 1212 and 1212+11)

       except

      (select distinct c_last_name, c_first_name, d_date

       from catalog_sales, date_dim, customer

       where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk

         and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk

         and d_month_seq between 1212 and 1212+11)

       except

      (select distinct c_last_name, c_first_name, d_date

       from web_sales, date_dim, customer

       where web_sales.ws_sold_date_sk = date_dim.d_date_sk

         and web_sales.ws_bill_customer_sk = customer.c_customer_sk

         and d_month_seq between 1212 and 1212+11)

) cool_cust

;

Q88.sql

select  *

from

 (select count(*) h8_30_to_9

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk   

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 8

     and time_dim.t_minute >= 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s1,

 (select count(*) h9_to_9_30

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 9

     and time_dim.t_minute < 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s2,

 (select count(*) h9_30_to_10

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 9

     and time_dim.t_minute >= 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s3,

 (select count(*) h10_to_10_30

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 10

     and time_dim.t_minute < 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s4,

 (select count(*) h10_30_to_11

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 10

     and time_dim.t_minute >= 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s5,

 (select count(*) h11_to_11_30

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 11

     and time_dim.t_minute < 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s6,

 (select count(*) h11_30_to_12

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 11

     and time_dim.t_minute >= 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s7,

 (select count(*) h12_to_12_30

 from store_sales, household_demographics , time_dim, store

 where ss_sold_time_sk = time_dim.t_time_sk

     and ss_hdemo_sk = household_demographics.hd_demo_sk

     and ss_store_sk = s_store_sk

     and time_dim.t_hour = 12

     and time_dim.t_minute < 30

     and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or

          (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or

          (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))

     and store.s_store_name = 'ese') s8

;

 

Q89.sql

select * from (select  *

from(

select i_category, i_class, i_brand,

       s_store_name, s_company_name,

       d_moy,

       sum(ss_sales_price) sum_sales,

       avg(sum(ss_sales_price)) over

         (partition by i_category, i_brand, s_store_name, s_company_name)

         avg_monthly_sales

from item, store_sales, date_dim, store

where ss_item_sk = i_item_sk and

      ss_sold_date_sk = d_date_sk and

      ss_store_sk = s_store_sk and

      d_year in (2000) and

        ((i_category in ('Home','Books','Electronics') and

          i_class in ('wallpaper','parenting','musical')

         )

      or (i_category in ('Shoes','Jewelry','Men') and

          i_class in ('womens','birdal','pants')

        ))

group by i_category, i_class, i_brand,

         s_store_name, s_company_name, d_moy) tmp1

where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1

order by sum_sales - avg_monthly_sales, s_store_name

 ) A limit  100;

Q90.sql

select * from (select  cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio

 from ( select count(*) amc

       from web_sales, household_demographics , time_dim, web_page

       where ws_sold_time_sk = time_dim.t_time_sk

         and ws_ship_hdemo_sk = household_demographics.hd_demo_sk

         and ws_web_page_sk = web_page.wp_web_page_sk

         and time_dim.t_hour between 6 and 6+1

         and household_demographics.hd_dep_count = 8

         and web_page.wp_char_count between 5000 and 5200) at,

      ( select count(*) pmc

       from web_sales, household_demographics , time_dim, web_page

       where ws_sold_time_sk = time_dim.t_time_sk

         and ws_ship_hdemo_sk = household_demographics.hd_demo_sk

         and ws_web_page_sk = web_page.wp_web_page_sk

         and time_dim.t_hour between 14 and 14+1

         and household_demographics.hd_dep_count = 8

         and web_page.wp_char_count between 5000 and 5200) pt

 order by am_pm_ratio

  ) A limit  100;

Q91.sql

select  

        cc_call_center_id Call_Center,

        cc_name Call_Center_Name,

        cc_manager Manager,

        sum(cr_net_loss) Returns_Loss

from

        call_center,

        catalog_returns,

        date_dim,

        customer,

        customer_address,

        customer_demographics,

        household_demographics

where

        cr_call_center_sk       = cc_call_center_sk

and     cr_returned_date_sk     = d_date_sk

and     cr_returning_customer_sk= c_customer_sk

and     cd_demo_sk              = c_current_cdemo_sk

and     hd_demo_sk              = c_current_hdemo_sk

and     ca_address_sk           = c_current_addr_sk

and     d_year                  = 1999

and     d_moy                   = 11

and     ( (cd_marital_status       = 'M' and cd_education_status     = 'Unknown')

        or(cd_marital_status       = 'W' and cd_education_status     = 'Advanced Degree'))

and     hd_buy_potential like '0-500%'

and     ca_gmt_offset           = -7

group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status

order by sum(cr_net_loss) desc;

 

Q92.sql

select * from (select  

   sum(ws_ext_discount_amt)  as "Excess Discount Amount"

from

    web_sales

   ,item

   ,date_dim

where

i_manufact_id = 269

and i_item_sk = ws_item_sk

and d_date between '1998-03-18' and

        (cast('1998-03-18' as date) + 90 days)

and d_date_sk = ws_sold_date_sk

and ws_ext_discount_amt  

     > (

         SELECT

            1.3 * avg(ws_ext_discount_amt)

         FROM

            web_sales

           ,date_dim

         WHERE

              ws_item_sk = i_item_sk

          and d_date between '1998-03-18' and

                             (cast('1998-03-18' as date) + 90 days)

          and d_date_sk = ws_sold_date_sk

      )

order by sum(ws_ext_discount_amt)

 ) A limit  100;

Q93.sql

select * from (select  ss_customer_sk

            ,sum(act_sales) sumsales

      from (select ss_item_sk

                  ,ss_ticket_number

                  ,ss_customer_sk

                  ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price

                                                            else (ss_quantity*ss_sales_price) end act_sales

            from store_sales left outer join store_returns on (sr_item_sk = ss_item_sk

                                                               and sr_ticket_number = ss_ticket_number)

                ,reason

            where sr_reason_sk = r_reason_sk

              and r_reason_desc = 'Did not like the warranty') t

      group by ss_customer_sk

      order by sumsales, ss_customer_sk

 ) A limit  100;

Q94.sql

select * from (select  

   count(distinct ws_order_number) as "order count"

  ,sum(ws_ext_ship_cost) as "total shipping cost"

  ,sum(ws_net_profit) as "total net profit"

from

   web_sales ws1

  ,date_dim

  ,customer_address

  ,web_site

where

    d_date between '1999-5-01' and

           (cast('1999-5-01' as date) + 60 days)

and ws1.ws_ship_date_sk = d_date_sk

and ws1.ws_ship_addr_sk = ca_address_sk

and ca_state = 'TX'

and ws1.ws_web_site_sk = web_site_sk

and web_company_name = 'pri'

and exists (select *

            from web_sales ws2

            where ws1.ws_order_number = ws2.ws_order_number

              and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)

and not exists(select *

               from web_returns wr1

               where ws1.ws_order_number = wr1.wr_order_number)

order by count(distinct ws_order_number)

 ) A limit  100;

Q95.sql

with ws_wh as

(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2

 from web_sales ws1,web_sales ws2

 where ws1.ws_order_number = ws2.ws_order_number

   and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)

select * from ( select  

   count(distinct ws_order_number) as "order count"

  ,sum(ws_ext_ship_cost) as "total shipping cost"

  ,sum(ws_net_profit) as "total net profit"

from

   web_sales ws1

  ,date_dim

  ,customer_address

  ,web_site

where

    d_date between '1999-5-01' and

           (cast('1999-5-01' as date) + 60 days)

and ws1.ws_ship_date_sk = d_date_sk

and ws1.ws_ship_addr_sk = ca_address_sk

and ca_state = 'TX'

and ws1.ws_web_site_sk = web_site_sk

and web_company_name = 'pri'

and ws1.ws_order_number in (select ws_order_number

                            from ws_wh)

and ws1.ws_order_number in (select wr_order_number

                            from web_returns,ws_wh

                            where wr_order_number = ws_wh.ws_order_number)

order by count(distinct ws_order_number)

 ) A limit  100;

Q96.sql

select * from (select  count(*)

from store_sales

    ,household_demographics

    ,time_dim, store

where ss_sold_time_sk = time_dim.t_time_sk   

    and ss_hdemo_sk = household_demographics.hd_demo_sk

    and ss_store_sk = s_store_sk

    and time_dim.t_hour = 8

    and time_dim.t_minute >= 30

    and household_demographics.hd_dep_count = 5

    and store.s_store_name = 'ese'

order by count(*)

 ) A limit  100;

 

Q97.sql

with ssci as (

select ss_customer_sk customer_sk

      ,ss_item_sk item_sk

from store_sales,date_dim

where ss_sold_date_sk = d_date_sk

  and d_month_seq between 1212 and 1212 + 11

group by ss_customer_sk

        ,ss_item_sk),

csci as(

 select cs_bill_customer_sk customer_sk

      ,cs_item_sk item_sk

from catalog_sales,date_dim

where cs_sold_date_sk = d_date_sk

  and d_month_seq between 1212 and 1212 + 11

group by cs_bill_customer_sk

        ,cs_item_sk)

select * from ( select  sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only

      ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only

      ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog

from ssci full outer join csci on (ssci.customer_sk=csci.customer_sk

                               and ssci.item_sk = csci.item_sk)

 ) A limit  100;

 

Q98.sql

select i_item_id

      ,i_item_desc

      ,i_category

      ,i_class

      ,i_current_price

      ,sum(ss_ext_sales_price) as itemrevenue

      ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over

          (partition by i_class) as revenueratio

from

store_sales

     ,item

     ,date_dim

where

ss_item_sk = i_item_sk

   and i_category in ('Jewelry', 'Sports', 'Books')

   and ss_sold_date_sk = d_date_sk

and d_date between cast('2001-01-12' as date)

and (cast('2001-01-12' as date) + 30 days)

group by

i_item_id

        ,i_item_desc

        ,i_category

        ,i_class

        ,i_current_price

order by

i_category

        ,i_class

        ,i_item_id

        ,i_item_desc

        ,revenueratio;

 

Q99.sql

select * from (select  

   substr(w_warehouse_name,1,20)

  ,sm_type

  ,cc_name

  ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end)  as "30 days"

  ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and

                 (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end )  as "31-60 days"

  ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and

                 (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end)  as "61-90 days"

  ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and

                 (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end)  as "91-120 days"

  ,sum(case when (cs_ship_date_sk - cs_sold_date_sk  > 120) then 1 else 0 end)  as ">120 days"

from

   catalog_sales

  ,warehouse

  ,ship_mode

  ,call_center

  ,date_dim

where

    d_month_seq between 1212 and 1212 + 11

and cs_ship_date_sk   = d_date_sk

and cs_warehouse_sk   = w_warehouse_sk

and cs_ship_mode_sk   = sm_ship_mode_sk

and cs_call_center_sk = cc_call_center_sk

group by

   substr(w_warehouse_name,1,20)

  ,sm_type

  ,cc_name

order by substr(w_warehouse_name,1,20)

        ,sm_type

        ,cc_name

 ) A limit  100;

 

#!/usr/bin/python

#coding=utf-8

import subprocess

import os

import sys

import time

import re

#origin = sys.stdout

#sys.stdout = f

import datetime

#os.system('hive -f q1.sql')

success = 0

#sys.stdout = origin

#f.close()

for i in range(1,99):

start_time = datetime.datetime.now()

print "log%s"%str(i)+"start"

f = open('../sql_1TB/q%s.log'%str(i), 'w')

r = subprocess.Popen('spark-sql --num-executors 36 --master yarn -f q%s.sql' %i, shell=True, stdout = subprocess.PIPE,stderr=subprocess.STDOUT)

output,err_msg = r.communicate()

r.returncode

f.write(output)

f.close()

times =  re.findall('(?<=Time taken: ).+?(?= seconds)',str(output))

#if times[1]:

# print 'success'

# os.system("mv ../logs/q%s.log ../logs/q%ssuccess.log "%(str(i),str(i)))

print "log%s"%str(i)+"stop"

time_used = str(datetime.datetime.now()-start_time)

print time_used

if 'Total MapReduce' in output:

print 'sucess!'

success = success +1

print 'sleep 5s'

time.sleep(5)

根据自己文件目录创建log文件

 

© 著作权归作者所有

d
粉丝 1
博文 8
码字总数 24514
作品 0
丰台
私信 提问
解析大数据基准测试——TPC-H or TPC-DS

转自:https://www.csdn.net/article/2015-05-14/2824680 摘要:为了方便企业选择合适的大数据测试基准,本文将在分析总结现有成果的基础,进一步讨论大数据测试基准应该具有的要素;并以此为...

yntmdr
2018/07/16
0
0
通关TPC-DS,中国数据库领域首破纪录诞生!

  【IT168 评论】“不服跑个分?”是一个老梗了。虽然,我们不能盲目崇信跑分,但跑分的确是一种比较直观反映软硬件性能的方式。不过,相比手机跑个分,数据库性能测试却要复杂的多……  ...

it168网站
2018/05/15
0
0
TPC-DS 2.0第一个基于SQL的大数据系统基准测试标准

今天(译注:December 17, 2015),TPC发布了第一个衡量基于SQL的大数据系统性能的工业基准测试标准,TPC-DS 2.0。构建在充分研究的TPC-DS之上,2.0版本特别针对基于SQL的大数据而设计、同时...

yntmdr
2018/06/28
0
0
Hadoop发行版某厂商发布TPC-DS评测结果

转自:http://www.cctime.com/html/2014-11-5/20141151514541991.htm 引言 大数据基础技术领域中Hadoop的地位已获得广泛认同,但目前国内外市场上的Hadoop版本也是林林总总,到底该参照什么标...

yntmdr
2018/06/28
0
0
SQL on Hadoop 和 SQL off Hadoop 介绍

  【IT168 资讯】最初,Apache Hadoop被视为是批处理非结构化数据的平台。从本质上讲,Hadoop是一种存储和处理大量数据的廉价又可靠的方式,吸引了更多的客户。   随着时间的推移,Data ...

it168网站
2017/11/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周日乱弹 —— 我,小小编辑,食人族酋长

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @宇辰OSC :分享娃娃的单曲《飘洋过海来看你》: #今日歌曲推荐# 《飘洋过海来看你》- 娃娃 手机党少年们想听歌,请使劲儿戳(这里) @宇辰OSC...

小小编辑
今天
527
10
MongoDB系列-- SpringBoot 中对 MongoDB 的 基本操作

SpringBoot 中对 MongoDB 的 基本操作 Database 库的创建 首先 在MongoDB 操作客户端 Robo 3T 中 创建数据库: 增加用户User: 创建 Collections 集合(类似mysql 中的 表): 后面我们大部分都...

TcWong
今天
29
0
spring cloud

一、从面试题入手 1.1、什么事微服务 1.2、微服务之间如何独立通讯的 1.3、springCloud和Dubbo有哪些区别 1.通信机制:DUbbo基于RPC远程过程调用;微服务cloud基于http restFUL API 1.4、spr...

榴莲黑芝麻糊
今天
16
0
Executor线程池原理与源码解读

线程池为线程生命周期的开销和资源不足问题提供了解决方 案。通过对多个任务重用线程,线程创建的开销被分摊到了多个任务上。 线程实现方式 Thread、Runnable、Callable //实现Runnable接口的...

小强的进阶之路
昨天
42
0
maven 环境隔离

解决问题 即 在 resource 文件夹下面 ,新增对应的资源配置文件夹,对应 开发,测试,生产的不同的配置内容 <resources> <resource> <directory>src/main/resources.${deplo......

之渊
昨天
54
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部