TPC-DS Benchmark性能测试_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

TPC-DS（Transaction Processing Performance Council Decision Support Benchmark）是一项决策支持系统基准测试标准，主要用于衡量数据仓库的性能。本文为您介绍云原生数据仓库 AnalyticDB PostgreSQL 版在TPC-DS 100 GB测试集上的性能表现。

前提条件

云原生数据仓库 AnalyticDB PostgreSQL 版实例和ECS实例位于同一VPC，详情请参见创建实例、创建ECS。
云原生数据仓库 AnalyticDB PostgreSQL 版实例和OSS存储空间Bucket位于同一地域，详情请参见创建存储空间。
已将ECS实例的私网IP地址添加到云原生数据仓库 AnalyticDB PostgreSQL 版实例的白名单中，详情请参见设置白名单。
已在ECS上安装psql，如需安装请参见psql。
使用阿里云账号或具备AliyunGPDBFullAccess、AliyunECSFullAccess和AliyunOSSFullAccess权限的RAM用户，并为其创建AccessKey ID和AccessKey Secret。详情请参见RAM用户、创建AccessKey。

测试环境

云原生数据仓库 AnalyticDB PostgreSQL 版实例规格	ECS实例规格
实例资源类型：存储弹性模式。引擎版本：7.0 标准版。产品类型：标准版。实例系列：高可用版。 master资源：8 CU。节点规格：2C16G。节点数量：8 个。存储磁盘类型：ESSD云盘 PL1。数据库内核小版本：V7.1.1.1。	实例规格：内存型 r7 / ecs.r7.large (2 vCPU 16 GiB)。镜像：Alibaba Cloud Linux 3.2104 LTS 64位（安全加固）。系统盘：ESSD云盘40GiB PL0（单盘IOPS性能上限1万）。数据盘：ESSD AutoPL1 2300GiB。

测试数据

本次测试使用TPC官方提供的toolkit生成TPC-DS数据。

下载tpcds-kit.tar，解压并进入工具目录。
```
tar -xvf tpcds-kit.tar
cd tpcds-kit/tools  
```

将以下脚本保存为.sh格式的文件，使用./命令执行该文件生成TPC-DS 100 GB测试数据。

# 参数说明： 
# SCALE为生成的tpcds数据集大小，1对应1 GB，100即100 GB。
# PARALLEL为数据切分的文件数量，文件数量最佳为segment数量的整数倍。
FOR ((i=1;i<=16;i++));
DO
./dsdgen -TERMINATE N -SCALE 100 -PARALLEL 16 -CHILD $i &
done

wait

说明

如果遇到报错./dsdgen: cannot execute binary file: Exec format error，请参考如下代码重新编译，编译完成后再执行步骤2生成测试数据。

sudo yum install byacc flex # 安装yacc和flex
make clean 
make OS=LINUX

使用ossutil将测试数据上传至OSS存储空间。ossutil工具的安装与使用，请参见命令行工具ossutil。

./ossutil64 cp -r <测试数据所在的路径> <OSS Bucket路径> --exclude "*.dat*"

示例如下。

./ossutil64 cp -r /mnt/dbqgen/ oss://testBucketName --exclude "*.dat*"

创建数据表。

点击查看完整的查询SQL

CREATE EXTENSION IF NOT EXISTS fastdecimal; -- 安装扩展

CREATE TABLE call_center
(
    cc_call_center_sk  integer NOT NULL,
    cc_call_center_id  char(16) NOT NULL,
    cc_rec_start_date  date,
    cc_rec_end_date    date,
    cc_closed_date_sk  integer,
    cc_open_date_sk    integer,
    cc_name            varchar(50),
    cc_class           varchar(50),
    cc_employees       integer,
    cc_sq_ft           integer,
    cc_hours           char(20),
    cc_manager         varchar(40) ,
    cc_mkt_id          integer,
    cc_mkt_class       char(50),
    cc_mkt_desc        varchar(100),
    cc_market_manager  varchar(40),
    cc_division        integer,
    cc_division_name   varchar(50),
    cc_company         integer,
    cc_company_name    char(50),
    cc_street_number   char(10),
    cc_street_name     varchar(60),
    cc_street_type     char(15),
    cc_suite_number    char(10),
    cc_city            varchar(60),
    cc_county          varchar(30),
    cc_state           char(2) ,
    cc_zip             char(10),
    cc_country         varchar(20) ,
    cc_gmt_offset      decimal64,
    cc_tax_percentage  decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE catalog_page
(
    cp_catalog_page_sk     integer NOT NULL,
    cp_catalog_page_id     char(16) NOT NULL,
    cp_start_date_sk       integer,
    cp_end_date_sk         integer,
    cp_department          varchar(50),
    cp_catalog_number      integer,
    cp_catalog_page_number integer,
    cp_description         varchar(100),
    cp_type                varchar(100)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE customer
(
    c_customer_sk          integer NOT NULL,
    c_customer_id          char(16) NOT NULL,
    c_current_cdemo_sk     integer,
    c_current_hdemo_sk     integer,
    c_current_addr_sk      integer,
    c_first_shipto_date_sk integer,
    c_first_sales_date_sk  integer,
    c_salutation           char(10),
    c_first_name           char(20),
    c_last_name            char(30),
    c_preferred_cust_flag  char(1) ,
    c_birth_day            integer ,
    c_birth_month          integer ,
    c_birth_year           integer ,
    c_birth_country        varchar(20) ,
    c_login                char(13),
    c_email_address        char(50),
    c_last_review_date     char(10)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (c_customer_sk);

CREATE TABLE customer_address
(
    ca_address_sk    integer NOT NULL,
    ca_address_id    char(16) NOT NULL,
    ca_street_number char(10),
    ca_street_name   varchar(60),
    ca_street_type   char(15),
    ca_suite_number  char(10),
    ca_city          varchar(60),
    ca_county        varchar(30),
    ca_state         char(2),
    ca_zip           char(10),
    ca_country       varchar(20),
    ca_gmt_offset    decimal64,
    ca_location_type char(20)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (ca_address_sk);

CREATE TABLE customer_demographics
(
    cd_demo_sk            integer NOT NULL,
    cd_gender             char(1),
    cd_marital_status     char(1),
    cd_education_status   char(20),
    cd_purchase_estimate  integer,
    cd_credit_rating      char(10),
    cd_dep_count          integer,
    cd_dep_employed_count integer,
    cd_dep_college_count  integer
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (cd_demo_sk);

CREATE TABLE date_dim
(
    d_date_sk           integer NOT NULL,
    d_date_id           char(16) NOT NULL,
    d_date              date,
    d_month_seq         integer,
    d_week_seq          integer,
    d_quarter_seq       integer,
    d_year              integer,
    d_dow               integer,
    d_moy               integer,
    d_dom               integer,
    d_qoy               integer,
    d_fy_year           integer,
    d_fy_quarter_seq    integer,
    d_fy_week_seq       integer,
    d_day_name          char(9),
    d_quarter_name      char(6),
    d_holiday           char(1),
    d_weekend           char(1),
    d_following_holiday char(1),
    d_first_dom         integer,
    d_last_dom          integer,
    d_same_day_ly       integer,
    d_same_day_lq       integer,
    d_current_day       char(1),
    d_current_week      char(1),
    d_current_month     char(1),
    d_current_quarter   char(1),
    d_current_year      char(1)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE household_demographics
(
    hd_demo_sk        integer NOT NULL,
    hd_income_band_sk integer,
    hd_buy_potential  char(15),
    hd_dep_count      integer,
    hd_vehicle_count  integer
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE income_band
(
    ib_income_band_sk integer NOT NULL,
    ib_lower_bound    integer,
    ib_upper_bound    integer
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE inventory
(
    inv_date_sk          integer NOT NULL,
    inv_item_sk          integer NOT NULL,
    inv_warehouse_sk     integer NOT NULL,
    inv_quantity_on_her
)
USING BEAM
WITH(compresstype=autslevel=1)
DISTRIBUTED BY (inv_i
PARTITION BY RANGE (ik)
(
    START(2450815) INND(2453005) INCLUSIVE EVERY (100),
    DEFAULT PARTITION
);

CREATE TABLE item
(
    i_item_sk        integer NOT NULL,
    i_item_id        char(16) NOT NULL,
    i_rec_start_date date,
    i_rec_end_date   date,
    i_item_desc      varchar(200),
    i_current_price  decimal64,
    i_wholesale_cost decimal64,
    i_brand_id       integer,
    i_brand          char(50),
    i_class_id       integer,
    i_class          char(50),
    i_category_id    integer,
    i_category       char(50),
    i_manufact_id    integer,
    i_manufact       char(50),
    i_size           char(20),
    i_formulation    char(20),
    i_color          char(20),
    i_units          char(10),
    i_container      char(10),
    i_manager_id     integer,
    i_product_name   char(50)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE promotion
(
    p_promo_sk        integer NOT NULL,
    p_promo_id        char(16) NOT NULL,
    p_start_date_sk   integer,
    p_end_date_sk     integer,
    p_item_sk         integer,
    p_cost            decimal64,
    p_response_target integer,
    p_promo_name      char(50),
    p_channel_dmail   char(1),
    p_channel_email   char(1),
    p_channel_catalog char(1),
    p_channel_tv      char(1),
    p_channel_radio   char(1),
    p_channel_press   char(1),
    p_channel_event   char(1),
    p_channel_demo    char(1),
    p_channel_details varchar(100),
    p_purpose         char(15),
    p_discount_active char(1) 
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE reason
(
    r_reason_sk   integer NOT NULL,
    r_reason_id   char(16) NOT NULL,
    r_reason_desc char(100) 
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE ship_mode
(
    sm_ship_mode_sk integer NOT NULL,
    sm_ship_mode_id char(16) NOT NULL,
    sm_type         char(30),
    sm_code         char(10),
    sm_carrier      char(20),
    sm_contract     char(20)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE store
(
    s_store_sk         integer NOT NULL,
    s_store_id         char(16) NOT NULL,
    s_rec_start_date   date,
    s_rec_end_date     date,
    s_closed_date_sk   integer,
    s_store_name       varchar(50),
    s_number_employees integer,
    s_floor_space      integer,
    s_hours            char(20),
    s_manager          varchar(40),
    s_market_id        integer,
    s_geography_class  varchar(100),
    s_market_desc      varchar(100),
    s_market_manager   varchar(40),
    s_division_id      integer,
    s_division_name    varchar(50),
    s_company_id       integer,
    s_company_name     varchar(50),
    s_street_number    varchar(10),
    s_street_name      varchar(60),
    s_street_type      char(15),
    s_suite_number     char(10),
    s_city             varchar(60),
    s_county           varchar(30),
    s_state            char(2),
    s_zip              char(10),
    s_country          varchar(20) ,
    s_gmt_offset       decimal64,
    s_tax_precentage   decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE time_dim
(
    t_time_sk   integer NOT NULL,
    t_time_id   char(16) NOT NULL,
    t_time      integer,
    t_hour      integer,
    t_minute    integer,
    t_second    integer,
    t_am_pm     char(2),
    t_shift     char(20),
    t_sub_shift char(20),
    t_meal_time char(20)
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE warehouse
(
    w_warehouse_sk    integer NOT NULL,
    w_warehouse_id    char(16) NOT NULL,
    w_warehouse_name  varchar(20),
    w_warehouse_sq_ft integer ,
    w_street_number   char(10),
    w_street_name     varchar(60),
    w_street_type     char(15),
    w_suite_number    char(10),
    w_city            varchar(60),
    w_county          varchar(30),
    w_state           char(2),
    w_zip             char(10),
    w_country         varchar(20),
    w_gmt_offset      decimal64 
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE web_page
(
    wp_web_page_sk      integer NOT NULL,
    wp_web_page_id      char(16) NOT NULL,
    wp_rec_start_date   date,
    wp_rec_end_date     date,
    wp_creation_date_sk integer,
    wp_access_date_sk   integer,
    wp_autogen_flag     char(1),
    wp_customer_sk      integer,
    wp_url              varchar(100),
    wp_type             char(50),
    wp_char_count       integer,
    wp_link_count       integer,
    wp_image_count      integer,
    wp_max_ad_count     integer 
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE web_site
(
    web_site_sk        integer NOT NULL,
    web_site_id        char(16) NOT NULL,
    web_rec_start_date date,
    web_rec_end_date   date,
    web_name           varchar(50),
    web_open_date_sk   integer,
    web_close_date_sk  integer,
    web_class          varchar(50),
    web_manager        varchar(40),
    web_mkt_id         integer,
    web_mkt_class      varchar(50),
    web_mkt_desc       varchar(100),
    web_market_manager varchar(40) ,
    web_company_id     integer,
    web_company_name   char(50),
    web_street_number  char(10),
    web_street_name    varchar(60),
    web_street_type    char(15),
    web_suite_number   char(10),
    web_city           varchar(60),
    web_county         varchar(30),
    web_state          char(2),
    web_zip            char(10),
    web_country        varchar(20),
    web_gmt_offset     decimal64,
    web_tax_percentage decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED REPLICATED;

CREATE TABLE catalog_returns
(
    cr_returned_date_sk      integer,
    cr_returned_time_sk      integer,
    cr_item_sk               integer NOT NULL,
    cr_refunded_customer_sk  integer,
    cr_refunded_cdemo_sk     integer,
    cr_refunded_hdemo_sk     integer,
    cr_refunded_addr_sk      integer,
    cr_returning_customer_sk integer,
    cr_returning_cdemo_sk    integer,
    cr_returning_hdemo_sk    integer,
    cr_returning_addr_sk     integer,
    cr_call_center_sk        integer,
    cr_catalog_page_sk       integer,
    cr_ship_mode_sk          integer,
    cr_warehouse_sk          integer,
    cr_reason_sk             integer,
    cr_order_number          bigint NOT NULL,
    cr_return_quantity       integer  ,
    cr_return_amount         decimal64,
    cr_return_tax            decimal64,
    cr_return_amt_inc_tax    decimal64,
    cr_fee                   decimal64,
    cr_return_ship_cost      decimal64,
    cr_refunded_cash         decimal64,
    cr_reversed_charge       decimal64,
    cr_store_credit          decimal64,
    cr_net_loss              decimal64 
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (cr_item_sk)
PARTITION BY RANGE (cr_returned_date_sk)
(
    START(2450815) INCLUSIVE END(2453005) INCLUSIVE EVERY (8),
    DEFAULT PARTITION ot 
);

CREATE TABLE catalog_sales
(
    cs_sold_date_sk          integer,
    cs_sold_time_sk          integer,
    cs_ship_date_sk          integer,
    cs_bill_customer_sk      integer,
    cs_bill_cdemo_sk         integer,
    cs_bill_hdemo_sk         integer,
    cs_bill_addr_sk          integer,
    cs_ship_customer_sk      integer,
    cs_ship_cdemo_sk         integer,
    cs_ship_hdemo_sk         integer,
    cs_ship_addr_sk          integer,
    cs_call_center_sk        integer,
    cs_catalog_page_sk       integer,
    cs_ship_mode_sk          integer,
    cs_warehouse_sk          integer,
    cs_item_sk               integer NOT NULL,
    cs_promo_sk              integer,
    cs_order_number          bigint NOT NULL,
    cs_quantity              integer,
    cs_wholesale_cost        decimal64,
    cs_list_price            decimal64,
    cs_sales_price           decimal64,
    cs_ext_discount_amt      decimal64,
    cs_ext_sales_price       decimal64,
    cs_ext_wholesale_cost    decimal64,
    cs_ext_list_price        decimal64,
    cs_ext_tax               decimal64,
    cs_coupon_amt            decimal64,
    cs_ext_ship_cost         decimal64,
    cs_net_paid              decimal64,
    cs_net_paid_inc_tax      decimal64,
    cs_net_paid_inc_ship     decimal64,
    cs_net_paid_inc_ship_tax decimal64,
    cs_net_profit            decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (cs_item_sk)
PARTITION BY RANGE (cs_sold_date_sk)
(
    START(2450815) INCLUSIVE END(2453005) INCLUSIVE EVERY (28),
    DEFAULT PARTITION others
);

CREATE TABLE web_returns
(
    wr_returned_date_sk      integer,
    wr_returned_time_sk      integer,
    wr_item_sk               integer NOT NULL,
    wr_refunded_customer_sk  integer,
    wr_refunded_cdemo_sk     integer,
    wr_refunded_hdemo_sk     integer,
    wr_refunded_addr_sk      integer,
    wr_returning_customer_sk integer,
    wr_returning_cdemo_sk    integer,
    wr_returning_hdemo_sk    integer,
    wr_returning_addr_sk     integer,
    wr_web_page_sk           integer,
    wr_reason_sk             integer,
    wr_order_number          bigint NOT NULL,
    wr_return_quantity       integer,
    wr_return_amt            decimal64,
    wr_return_tax            decimal64,
    wr_return_amt_inc_tax    decimal64,
    wr_fee                   decimal64,
    wr_return_ship_cost      decimal64,
    wr_refunded_cash         decimal64,
    wr_reversed_charge       decimal64,
    wr_account_credit        decimal64,
    wr_net_loss              decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (wr_item_sk)
PARTITION BY RANGE (wr_returned_date_sk)
(
    START(2450815) INCLUSIVE END(2453005) INCLUSIVE EVERY (180),
    DEFAULT PARTITION others
);

CREATE TABLE web_sales
(
    ws_sold_date_sk          integer,
    ws_sold_time_sk          integer,
    ws_ship_date_sk          integer,
    ws_item_sk               integer NOT NULL,
    ws_bill_customer_sk      integer,
    ws_bill_cdemo_sk         integer,
    ws_bill_hdemo_sk         integer,
    ws_bill_addr_sk          integer,
    ws_ship_customer_sk      integer,
    ws_ship_cdemo_sk         integer,
    ws_ship_hdemo_sk         integer,
    ws_ship_addr_sk          integer,
    ws_web_page_sk           integer,
    ws_web_site_sk           integer,
    ws_ship_mode_sk          integer,
    ws_warehouse_sk          integer,
    ws_promo_sk              integer,
    ws_order_number          bigint NOT NULL,
    ws_quantity              integer,
    ws_wholesale_cost        decimal64,
    ws_list_price            decimal64,
    ws_sales_price           decimal64,
    ws_ext_discount_amt      decimal64,
    ws_ext_sales_price       decimal64,
    ws_ext_wholesale_cost    decimal64,
    ws_ext_list_price        decimal64,
    ws_ext_tax               decimal64,
    ws_coupon_amt            decimal64,
    ws_ext_ship_cost         decimal64,
    ws_net_paid              decimal64,
    ws_net_paid_inc_tax      decimal64,
    ws_net_paid_inc_ship     decimal64,
    ws_net_paid_inc_ship_tax decimal64,
    ws_net_profit            decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (ws_item_sk)
PARTITION BY RANGE (ws_sold_date_sk)
(
    START(2450815) INCLUSIVE END(2453005) INCLUSIVE EVERY (40),
    DEFAULT PARTITION others
);

CREATE TABLE store_returns
(
    sr_returned_date_sk   integer,
    sr_return_time_sk     integer,
    sr_item_sk            integer NOT NULL,
    sr_customer_sk        integer,
    sr_cdemo_sk           integer,
    sr_hdemo_sk           integer,
    sr_addr_sk            integer,
    sr_store_sk           integer,
    sr_reason_sk          integer,
    sr_ticket_number      bigint NOT NULL,
    sr_return_quantity    integer,
    sr_return_amt         decimal64,
    sr_return_tax         decimal64,
    sr_return_amt_inc_tax decimal64,
    sr_fee                decimal64,
    sr_return_ship_cost   decimal64,
    sr_refunded_cash      decimal64,
    sr_reversed_charge    decimal64,
    sr_store_credit       decimal64,
    sr_net_loss           decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (sr_item_sk)
PARTITION BY RANGE (sr_returned_date_sk)
(
    START(2450815) INCLUSIVE END(2453005) INCLUSIVE EVERY (100),
    DEFAULT PARTITION others
);

CREATE TABLE store_sales
(
    ss_sold_date_sk       integer,
    ss_sold_time_sk       integer,
    ss_item_sk            integer NOT NULL,
    ss_customer_sk        integer,
    ss_cdemo_sk           integer,
    ss_hdemo_sk           integer,
    ss_addr_sk            integer,
    ss_store_sk           integer,
    ss_promo_sk           integer,
    ss_ticket_number      bigint NOT NULL,
    ss_quantity           integer,
    ss_wholesale_cost     decimal64,
    ss_list_price         decimal64,
    ss_sales_price        decimal64,
    ss_ext_discount_amt   decimal64,
    ss_ext_sales_price    decimal64,
    ss_ext_wholesale_cost decimal64,
    ss_ext_list_price     decimal64,
    ss_ext_tax            decimal64,
    ss_coupon_amt         decimal64,
    ss_net_paid           decimal64,
    ss_net_paid_inc_tax   decimal64,
    ss_net_profit         decimal64
)
USING BEAM
WITH(compresstype=auto,compresslevel=1)
DISTRIBUTED BY (ss_item_sk)
PARTITION BY RANGE (ss_sold_date_sk)
(
    START(2450815) INCLUSIVE END(2453005) INCLUSIVE EVERY (10),
    DEFAULT PARTITION others
);

创建OSS Foreign Table。利用OSS Foreign Table将OSS上的测试数据导入外表。

Endpoint和Bucket需指定为测试数据所在的OSS Bucket名称和Endpoint。获取Endpoint和Bucket请参见获取OSS Bucket信息。
ID和Key即为阿里云账号或RAM用户的AccessKey ID和AccessKey Secret。获取AccessKey ID和AccessKey Secret请参见创建AccessKey。

点击查看完整的查询SQL

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-hang****.aliyuncs.com',
        bucket 'testBucketName'
  );
CREATE USER MAPPING FOR PUBLIC  SERVER oss_serv OPTIONS ( id 'LTAI5t5uGDuSBRXd6GLu****', key 'qlPCnIB2g8c3YbPWpHvMyNC5PX****' );

CREATE FOREIGN TABLE ext_call_center(
    cc_call_center_sk integer,
    cc_call_center_id char(16),
    cc_rec_start_date date,
    cc_rec_end_date   date,
    cc_closed_date_sk integer,
    cc_open_date_sk   integer,
    cc_name           varchar(50),
    cc_class          varchar(50),
    cc_employees      integer,
    cc_sq_ft          integer,
    cc_hours          char(20),
    cc_manager        varchar(40),
    cc_mkt_id         integer,
    cc_mkt_class      char(50),
    cc_mkt_desc       varchar(100),
    cc_market_manager varchar(40),
    cc_division       integer,
    cc_division_name  varchar(50),
    cc_company        integer,
    cc_company_name   char(50),
    cc_street_number  char(10),
    cc_street_name    varchar(60),
    cc_street_type    char(15),
    cc_suite_number   char(10),
    cc_city           varchar(60),
    cc_county         varchar(30),
    cc_state          char(2),
    cc_zip            char(10),
    cc_country        varchar(20) ,
    cc_gmt_offset     decimal64,
    cc_tax_percentage decimal64)
server oss_serv
    options (
        prefix 'tpcds100gb/call_center_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_catalog_page
(
    cp_catalog_page_sk     integer ,
    cp_catalog_page_id     char(16),
    cp_start_date_sk       integer,
    cp_end_date_sk         integer,
    cp_department          varchar(50) ,
    cp_catalog_number      integer,
    cp_catalog_page_number integer,
    cp_description         varchar(100),
    cp_type                varchar(100)
)
server oss_serv
    options (
        prefix 'tpcds100gb/catalog_page_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_customer
(
    c_customer_sk          integer,
    c_customer_id          char(16),
    c_current_cdemo_sk     integer,
    c_current_hdemo_sk     integer,
    c_current_addr_sk      integer,
    c_first_shipto_date_sk integer,
    c_first_sales_date_sk  integer,
    c_salutation           char(10),
    c_first_name           char(20),
    c_last_name            char(30),
    c_preferred_cust_flag  char(1),
    c_birth_day            integer,
    c_birth_month          integer,
    c_birth_year           integer,
    c_birth_country        varchar(20),
    c_login                char(13),
    c_email_address        char(50),
    c_last_review_date     char(10)
)
server oss_serv
    options (
        prefix 'tpcds100gb/customer/customer_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_customer_address
(
    ca_address_sk    integer,
    ca_address_id    char(16),
    ca_street_number char(10),
    ca_street_name   varchar(60),
    ca_street_type   char(15),
    ca_suite_number  char(10),
    ca_city          varchar(60),
    ca_county        varchar(30),
    ca_state         char(2),
    ca_zip           char(10),
    ca_country       varchar(20),
    ca_gmt_offset    decimal64,
    ca_location_type char(20)
)
server oss_serv
    options (
        prefix 'tpcds100gb/customer_address_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_customer_demographics
(
    cd_demo_sk            integer,
    cd_gender             char(1),
    cd_marital_status     char(1),
    cd_education_status   char(20),
    cd_purchase_estimate  integer,
    cd_credit_rating      char(10),
    cd_dep_count          integer,
    cd_dep_employed_count integer,
    cd_dep_college_count  integer
)
server oss_serv
    options (
        prefix 'tpcds100gb/customer_demographics_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_date_dim
(
    d_date_sk           integer,
    d_date_id           char(16),
    d_date              date,
    d_month_seq         integer,
    d_week_seq          integer,
    d_quarter_seq       integer,
    d_year              integer,
    d_dow               integer,
    d_moy               integer,
    d_dom               integer,
    d_qoy               integer,
    d_fy_year           integer,
    d_fy_quarter_seq    integer,
    d_fy_week_seq       integer,
    d_day_name          char(9),
    d_quarter_name      char(6),
    d_holiday           char(1),
    d_weekend           char(1),
    d_following_holiday char(1),
    d_first_dom         integer,
    d_last_dom          integer,
    d_same_day_ly       integer,
    d_same_day_lq       integer,
    d_current_day       char(1),
    d_current_week      char(1),
    d_current_month     char(1),
    d_current_quarter   char(1),
    d_current_year      char(1)
)
server oss_serv
    options (
        prefix 'tpcds100gb/date_dim_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_household_demographics
(
    hd_demo_sk        integer,
    hd_income_band_sk integer ,
    hd_buy_potential  char(15),
    hd_dep_count      integer,
    hd_vehicle_count  integer
)
server oss_serv
    options (
        prefix 'tpcds100gb/household_demographics_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_income_band
(
    ib_income_band_sk integer,
    ib_lower_bound    integer,
    ib_upper_bound    integer
)
server oss_serv
    options (
        prefix 'tpcds100gb/income_band_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_inventory
(
    inv_date_sk          integer,
    inv_item_sk          integer,
    inv_warehouse_sk     integer,
    inv_quantity_on_hand integer
)
server oss_serv
    options (
        prefix 'tpcds100gb/inventory_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_item
(
    i_item_sk        integer,
    i_item_id        char(16),
    i_rec_start_date date,
    i_rec_end_date   date,
    i_item_desc      varchar(200),
    i_current_price  decimal64,
    i_wholesale_cost decimal64,
    i_brand_id       integer,
    i_brand          char(50),
    i_class_id       integer,
    i_class          char(50),
    i_category_id    integer,
    i_category       char(50),
    i_manufact_id    integer,
    i_manufact       char(50),
    i_size           char(20),
    i_formulation    char(20),
    i_color          char(20),
    i_units          char(10),
    i_container      char(10),
    i_manager_id     integer,
    i_product_name   char(50)
)
server oss_serv
    options (
        prefix 'tpcds100gb/item_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_promotion
(
    p_promo_sk        integer,
    p_promo_id        char(16),
    p_start_date_sk   integer,
    p_end_date_sk     integer,
    p_item_sk         integer,
    p_cost            decimal64,
    p_response_target integer,
    p_promo_name      char(50),
    p_channel_dmail   char(1),
    p_channel_email   char(1),
    p_channel_catalog char(1),
    p_channel_tv      char(1),
    p_channel_radio   char(1),
    p_channel_press   char(1),
    p_channel_event   char(1),
    p_channel_demo    char(1),
    p_channel_details varchar(100),
    p_purpose         char(15),
    p_discount_active char(1)
)
server oss_serv
    options (
        prefix 'tpcds100gb/promotion_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_reason
(
    r_reason_sk   integer,
    r_reason_id   char(16),
    r_reason_desc char(100)
)
server oss_serv
    options (
        prefix 'tpcds100gb/reason_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_ship_mode
(
    sm_ship_mode_sk integer,
    sm_ship_mode_id char(16),
    sm_type         char(30),
    sm_code         char(10),
    sm_carrier      char(20),
    sm_contract     char(20)
)
server oss_serv
    options (
        prefix 'tpcds100gb/ship_mode_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_store
(
    s_store_sk         integer,
    s_store_id         char(16),
    s_rec_start_date   date,
    s_rec_end_date     date,
    s_closed_date_sk   integer,
    s_store_name       varchar(50),
    s_number_employees integer,
    s_floor_space      integer,
    s_hours            char(20),
    s_manager          varchar(40),
    s_market_id        integer,
    s_geography_class  varchar(100),
    s_market_desc      varchar(100),
    s_market_manager   varchar(40),
    s_division_id      integer,
    s_division_name    varchar(50),
    s_company_id       integer,
    s_company_name     varchar(50),
    s_street_number    varchar(10),
    s_street_name      varchar(60),
    s_street_type      char(15),
    s_suite_number     char(10),
    s_city             varchar(60),
    s_county           varchar(30),
    s_state            char(2),
    s_zip              char(10),
    s_country          varchar(20),
    s_gmt_offset       decimal64,
    s_tax_precentage   decimal64 
)
server oss_serv
    options (
        filepath 'tpcds100gb/store_1',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_time_dim
(
    t_time_sk   integer,
    t_time_id   char(16),
    t_time      integer,
    t_hour      integer,
    t_minute    integer,
    t_second    integer,
    t_am_pm     char(2),
    t_shift     char(20),
    t_sub_shift char(20),
    t_meal_time char(20)
)
server oss_serv
    options (
        prefix 'tpcds100gb/time_dim_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_warehouse
(
    w_warehouse_sk    integer,
    w_warehouse_id    char(16),
    w_warehouse_name  varchar(20),
    w_warehouse_sq_ft integer,
    w_street_number   char(10),
    w_street_name     varchar(60),
    w_street_type     char(15),
    w_suite_number    char(10),
    w_city            varchar(60),
    w_county          varchar(30),
    w_state           char(2),
    w_zip             char(10),
    w_country         varchar(20),
    w_gmt_offset      decimal64  
)
server oss_serv
    options (
        prefix 'tpcds100gb/warehouse_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_web_page
(
    wp_web_page_sk      integer,
    wp_web_page_id      char(16),
    wp_rec_start_date   date,
    wp_rec_end_date     date,
    wp_creation_date_sk integer,
    wp_access_date_sk   integer,
    wp_autogen_flag     char(1),
    wp_customer_sk      integer,
    wp_url              varchar(100),
    wp_type             char(50),
    wp_char_count       integer,
    wp_link_count       integer,
    wp_image_count      integer,
    wp_max_ad_count     integer
)
server oss_serv
    options (
        prefix 'tpcds100gb/web_page_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_web_site
(
    web_site_sk        integer ,
    web_site_id        char(16),
    web_rec_start_date date,
    web_rec_end_date   date,
    web_name           varchar(50),
    web_open_date_sk   integer,
    web_close_date_sk  integer,
    web_class          varchar(50),
    web_manager        varchar(40),
    web_mkt_id         integer,
    web_mkt_class      varchar(50) ,
    web_mkt_desc       varchar(100),
    web_market_manager varchar(40),
    web_company_id     integer,
    web_company_name   char(50),
    web_street_number  char(10),
    web_street_name    varchar(60),
    web_street_type    char(15),
    web_suite_number   char(10),
    web_city           varchar(60),
    web_county         varchar(30),
    web_state          char(2),
    web_zip            char(10),
    web_country        varchar(20),
    web_gmt_offset     decimal64,
    web_tax_percentage decimal64
)
server oss_serv
    options (
        prefix 'tpcds100gb/web_site_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_catalog_returns
(
    cr_returned_date_sk      integer,
    cr_returned_time_sk      integer,
    cr_item_sk               integer,
    cr_refunded_customer_sk  integer,
    cr_refunded_cdemo_sk     integer,
    cr_refunded_hdemo_sk     integer,
    cr_refunded_addr_sk      integer,
    cr_returning_customer_sk integer,
    cr_returning_cdemo_sk    integer,
    cr_returning_hdemo_sk    integer,
    cr_returning_addr_sk     integer,
    cr_call_center_sk        integer,
    cr_catalog_page_sk       integer,
    cr_ship_mode_sk          integer,
    cr_warehouse_sk          integer,
    cr_reason_sk             integer,
    cr_order_number          bigint ,
    cr_return_quantity       integer,
    cr_return_amount         decimal6,
    cr_return_tax            decimal6,
    cr_return_amt_inc_tax    decimal6,
    cr_fee                   decimal6,
    cr_return_ship_cost      decimal6,
    cr_refunded_cash         decimal6,
    cr_reversed_charge       decimal6,
    cr_store_credit          decimal6,
    cr_net_loss              decimal6
)
server oss_serv
    options (
        prefix 'tpcds100gb/catalog_returns_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_catalog_sales
(
    cs_sold_date_sk          integer,
    cs_sold_time_sk          integer,
    cs_ship_date_sk          integer,
    cs_bill_customer_sk      integer,
    cs_bill_cdemo_sk         integer,
    cs_bill_hdemo_sk         integer,
    cs_bill_addr_sk          integer,
    cs_ship_customer_sk      integer,
    cs_ship_cdemo_sk         integer,
    cs_ship_hdemo_sk         integer,
    cs_ship_addr_sk          integer,
    cs_call_center_sk        integer,
    cs_catalog_page_sk       integer,
    cs_ship_mode_sk          integer,
    cs_warehouse_sk          integer,
    cs_item_sk               integer,
    cs_promo_sk              integer,
    cs_order_number          bigint,
    cs_quantity              integer,
    cs_wholesale_cost        decimal64,
    cs_list_price            decimal64,
    cs_sales_price           decimal64,
    cs_ext_discount_amt      decimal64,
    cs_ext_sales_price       decimal64,
    cs_ext_wholesale_cost    decimal64,
    cs_ext_list_price        decimal64,
    cs_ext_tax               decimal64,
    cs_coupon_amt            decimal64,
    cs_ext_ship_cost         decimal64,
    cs_net_paid              decimal64,
    cs_net_paid_inc_tax      decimal64,
    cs_net_paid_inc_ship     decimal64,
    cs_net_paid_inc_ship_tax decimal64,
    cs_net_profit            decimal64
)
server oss_serv
    options (
        prefix 'tpcds100gb/catalog_sales_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_web_returns
(
    wr_returned_date_sk      integer,
    wr_returned_time_sk      integer,
    wr_item_sk               integer,
    wr_refunded_customer_sk  integer,
    wr_refunded_cdemo_sk     integer,
    wr_refunded_hdemo_sk     integer,
    wr_refunded_addr_sk      integer,
    wr_returning_customer_sk integer,
    wr_returning_cdemo_sk    integer,
    wr_returning_hdemo_sk    integer,
    wr_returning_addr_sk     integer,
    wr_web_page_sk           integer,
    wr_reason_sk             integer,
    wr_order_number          bigint,
    wr_return_quantity       integer,
    wr_return_amt            decimal64,
    wr_return_tax            decimal64,
    wr_return_amt_inc_tax    decimal64,
    wr_fee                   decimal64,
    wr_return_ship_cost      decimal64,
    wr_refunded_cash         decimal64,
    wr_reversed_charge       decimal64,
    wr_account_credit        decimal64,
    wr_net_loss              decimal64
)
server oss_serv
    options (
        prefix 'tpcds100gb/web_returns_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_web_sales
(
    ws_sold_date_sk          integer,
    ws_sold_time_sk          integer,
    ws_ship_date_sk          integer,
    ws_item_sk               integer,
    ws_bill_customer_sk      integer,
    ws_bill_cdemo_sk         integer,
    ws_bill_hdemo_sk         integer,
    ws_bill_addr_sk          integer,
    ws_ship_customer_sk      integer,
    ws_ship_cdemo_sk         integer,
    ws_ship_hdemo_sk         integer,
    ws_ship_addr_sk          integer,
    ws_web_page_sk           integer,
    ws_web_site_sk           integer,
    ws_ship_mode_sk          integer,
    ws_warehouse_sk          integer,
    ws_promo_sk              integer,
    ws_order_number          bigint,
    ws_quantity              integer,
    ws_wholesale_cost        decimal64,
    ws_list_price            decimal64,
    ws_sales_price           decimal64,
    ws_ext_discount_amt      decimal64,
    ws_ext_sales_price       decimal64,
    ws_ext_wholesale_cost    decimal64,
    ws_ext_list_price        decimal64,
    ws_ext_tax               decimal64,
    ws_coupon_amt            decimal64,
    ws_ext_ship_cost         decimal64,
    ws_net_paid              decimal64,
    ws_net_paid_inc_tax      decimal64,
    ws_net_paid_inc_ship     decimal64,
    ws_net_paid_inc_ship_tax decimal64,
    ws_net_profit            decimal64
)
server oss_serv
    options (
        prefix 'tpcds100gb/web_sales_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_store_returns
(
    sr_returned_date_sk   integer,
    sr_return_time_sk     integer,
    sr_item_sk            integer,
    sr_customer_sk        integer,
    sr_cdemo_sk           integer,
    sr_hdemo_sk           integer,
    sr_addr_sk            integer,
    sr_store_sk           integer,
    sr_reason_sk          integer,
    sr_ticket_number      bigint,
    sr_return_quantity    integer,
    sr_return_amt         decimal64,
    sr_return_tax         decimal64,
    sr_return_amt_inc_tax decimal64,
    sr_fee                decimal64,
    sr_return_ship_cost   decimal64,
    sr_refunded_cash      decimal64,
    sr_reversed_charge    decimal64,
    sr_store_credit       decimal64,
    sr_net_loss           decimal64
)
server oss_serv
    options (
        prefix 'tpcds100gb/store_returns_',
        format 'csv',
        DELIMITER '|'
    );

CREATE FOREIGN TABLE ext_store_sales
(
    ss_sold_date_sk       integer,
    ss_sold_time_sk       integer,
    ss_item_sk            integer NOT NULL,
    ss_customer_sk        integer,
    ss_cdemo_sk           integer,
    ss_hdemo_sk           integer,
    ss_addr_sk            integer,
    ss_store_sk           integer,
    ss_promo_sk           integer,
    ss_ticket_number      bigint NOT NULL,
    ss_quantity           integer,
    ss_wholesale_cost     decimal64,
    ss_list_price         decimal64,
    ss_sales_price        decimal64,
    ss_ext_discount_amt   decimal64,
    ss_ext_sales_price    decimal64,
    ss_ext_wholesale_cost decimal64,
    ss_ext_list_price     decimal64,
    ss_ext_tax            decimal64,
    ss_coupon_amt         decimal64,
    ss_net_paid           decimal64,
    ss_net_paid_inc_tax   decimal64,
    ss_net_profit         decimal64
)
server oss_serv
    options (
        prefix 'tpcds100gb/store_sales_',
        format 'csv',
        DELIMITER '|'
    );

将数据导入步骤4创建的表并采集统计信息。

INSERT INTO call_center SELECT * FROM ext_call_center;           
INSERT INTO catalog_page SELECT * FROM ext_catalog_page;          
INSERT INTO catalog_returns SELECT * FROM ext_catalog_returns;       
INSERT INTO catalog_sales SELECT * FROM ext_catalog_sales;         
INSERT INTO customer SELECT * FROM ext_customer;              
INSERT INTO customer_address SELECT * FROM ext_customer_address;      
INSERT INTO customer_demographics SELECT * FROM ext_customer_demographics; 
INSERT INTO date_dim SELECT * FROM ext_date_dim;                         
INSERT INTO household_demographics SELECT * FROM ext_household_demographics;
INSERT INTO income_band SELECT * FROM ext_income_band;          
INSERT INTO inventory SELECT * FROM ext_inventory;             
INSERT INTO item SELECT * FROM ext_item;                  
INSERT INTO promotion SELECT * FROM ext_promotion;             
INSERT INTO reason SELECT * FROM ext_reason;                
INSERT INTO ship_mode SELECT * FROM ext_ship_mode;             
INSERT INTO store SELECT * FROM ext_store;                 
INSERT INTO store_returns SELECT * FROM ext_store_returns;         
INSERT INTO store_sales SELECT * FROM ext_store_sales;           
INSERT INTO time_dim SELECT * FROM ext_time_dim;              
INSERT INTO warehouse SELECT * FROM ext_warehouse;             
INSERT INTO web_page SELECT * FROM ext_web_page;
INSERT INTO web_returns SELECT * FROM ext_web_returns;           
INSERT INTO web_sales SELECT * FROM ext_web_sales;             
INSERT INTO web_site SELECT * FROM ext_web_site;
-- 采集统计信息
ANALYZE fullscan call_center;
ANALYZE fullscan catalog_page;
ANALYZE fullscan catalog_returns;
ANALYZE fullscan catalog_sales;
ANALYZE fullscan customer;
ANALYZE fullscan customer_address;
ANALYZE fullscan customer_demographics;
ANALYZE fullscan date_dim;
ANALYZE fullscan household_demographics;
ANALYZE fullscan income_band;
ANALYZE fullscan inventory;
ANALYZE fullscan item;
ANALYZE fullscan promotion;
ANALYZE fullscan reason;
ANALYZE fullscan ship_mode;
ANALYZE fullscan store;
ANALYZE fullscan store_returns;
ANALYZE fullscan store_sales;
ANALYZE fullscan time_dim;
ANALYZE fullscan warehouse;
ANALYZE fullscan web_page;
ANALYZE fullscan web_returns;
ANALYZE fullscan web_sales;
ANALYZE fullscan web_site;

统计信息采集完成后，可开始测试查询。

测试查询

本次测试包含99个查询，查询语句较长，您可以点击下方查看完整的查询SQL。

点击查看完整的查询SQL

--Q1
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 c_customer_id
FROM customer_total_return ctr1,
  store,
  customer
WHERE ctr1.ctr_total_return > (
    SELECT AVG(ctr_total_return) * 1.2
    FROM customer_total_return ctr2
    WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk
  )
  AND s_store_sk = ctr1.ctr_store_sk
  AND s_state = 'SD'
  AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
LIMIT 100;
--Q2
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
    ) AS alias1
),
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
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
LIMIT 100;
--Q4
WITH year_total AS (
  SELECT c_customer_id customer_id,
    c_first_name customer_first_name,
    c_last_name customer_last_name,
    c_preferred_cust_flag customer_preferred_cust_flag,
    c_birth_country customer_birth_country,
    c_login customer_login,
    c_email_address customer_email_address,
    d_year dyear,
    SUM(
      (
        (
          ss_ext_list_price - ss_ext_wholesale_cost - ss_ext_discount_amt
        ) + ss_ext_sales_price
      ) / 2
    ) year_total,
    's' sale_type
  FROM customer,
    store_sales,
    date_dim
  WHERE c_customer_sk = ss_customer_sk
    AND ss_sold_date_sk = d_date_sk
  GROUP BY c_customer_id,
    c_first_name,
    c_last_name,
    c_preferred_cust_flag,
    c_birth_country,
    c_login,
    c_email_address,
    d_year
  UNION ALL
  SELECT c_customer_id customer_id,
    c_first_name customer_first_name,
    c_last_name customer_last_name,
    c_preferred_cust_flag customer_preferred_cust_flag,
    c_birth_country customer_birth_country,
    c_login customer_login,
    c_email_address customer_email_address,
    d_year dyear,
    SUM(
      (
        (
          (
            cs_ext_list_price - cs_ext_wholesale_cost - cs_ext_discount_amt
          ) + cs_ext_sales_price
        ) / 2
      )
    ) year_total,
    'c' sale_type
  FROM customer,
    catalog_sales,
    date_dim
  WHERE c_customer_sk = cs_bill_customer_sk
    AND cs_sold_date_sk = d_date_sk
  GROUP BY c_customer_id,
    c_first_name,
    c_last_name,
    c_preferred_cust_flag,
    c_birth_country,
    c_login,
    c_email_address,
    d_year
  UNION ALL
  SELECT c_customer_id customer_id,
    c_first_name customer_first_name,
    c_last_name customer_last_name,
    c_preferred_cust_flag customer_preferred_cust_flag,
    c_birth_country customer_birth_country,
    c_login customer_login,
    c_email_address customer_email_address,
    d_year dyear,
    SUM(
      (
        (
          (
            ws_ext_list_price - ws_ext_wholesale_cost - ws_ext_discount_amt
          ) + ws_ext_sales_price
        ) / 2
      )
    ) year_total,
    'w' sale_type
  FROM customer,
    web_sales,
    date_dim
  WHERE c_customer_sk = ws_bill_customer_sk
    AND ws_sold_date_sk = d_date_sk
  GROUP BY c_customer_id,
    c_first_name,
    c_last_name,
    c_preferred_cust_flag,
    c_birth_country,
    c_login,
    c_email_address,
    d_year
)
SELECT t_s_secyear.customer_id,
  t_s_secyear.customer_first_name,
  t_s_secyear.customer_last_name,
  t_s_secyear.customer_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
LIMIT 100;
--Q5
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) + interval '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) + interval '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) + interval '14 days')
    AND wsr_web_site_sk = web_site_sk
  GROUP BY web_site_id
)
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
LIMIT 100;
--Q6
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.ca_state
LIMIT 100;
--Q7
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
LIMIT 100;
--Q8
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
LIMIT 100;
--Q9
SELECT CASE
    WHEN (
      SELECT COUNT(*)
      FROM store_sales
      WHERE ss_quantity BETWEEN 1 AND 20
    ) > 409437 THEN (
      SELECT AVG(ss_ext_tax)
      FROM store_sales
      WHERE ss_quantity BETWEEN 1 AND 20
    )
    ELSE (
      SELECT AVG(ss_net_paid)
      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
    ) > 4595804 THEN (
      SELECT AVG(ss_ext_tax)
      FROM store_sales
      WHERE ss_quantity BETWEEN 21 AND 40
    )
    ELSE (
      SELECT AVG(ss_net_paid)
      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
    ) > 1333710 THEN (
      SELECT AVG(ss_ext_tax)
      FROM store_sales
      WHERE ss_quantity BETWEEN 41 AND 60
    )
    ELSE (
      SELECT AVG(ss_net_paid)
      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
    ) > 2361102 THEN (
      SELECT AVG(ss_ext_tax)
      FROM store_sales
      WHERE ss_quantity BETWEEN 61 AND 80
    )
    ELSE (
      SELECT AVG(ss_net_paid)
      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
    ) > 1517817 THEN (
      SELECT AVG(ss_ext_tax)
      FROM store_sales
      WHERE ss_quantity BETWEEN 81 AND 100
    )
    ELSE (
      SELECT AVG(ss_net_paid)
      FROM store_sales
      WHERE ss_quantity BETWEEN 81 AND 100
    )
  END bucket5
FROM reason
WHERE r_reason_sk = 1;
--Q10
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
LIMIT 100;
--Q11
WITH year_total AS (
  SELECT c_customer_id customer_id,
    c_first_name customer_first_name,
    c_last_name customer_last_name,
    c_preferred_cust_flag customer_preferred_cust_flag,
    c_birth_country customer_birth_country,
    c_login customer_login,
    c_email_address customer_email_address,
    d_year dyear,
    SUM(ss_ext_list_price - ss_ext_discount_amt) year_total,
    's' sale_type
  FROM customer,
    store_sales,
    date_dim
  WHERE c_customer_sk = ss_customer_sk
    AND ss_sold_date_sk = d_date_sk
  GROUP BY c_customer_id,
    c_first_name,
    c_last_name,
    c_preferred_cust_flag,
    c_birth_country,
    c_login,
    c_email_address,
    d_year
  UNION ALL
  SELECT c_customer_id customer_id,
    c_first_name customer_first_name,
    c_last_name customer_last_name,
    c_preferred_cust_flag customer_preferred_cust_flag,
    c_birth_country customer_birth_country,
    c_login customer_login,
    c_email_address customer_email_address,
    d_year dyear,
    SUM(ws_ext_list_price - ws_ext_discount_amt) year_total,
    'w' sale_type
  FROM customer,
    web_sales,
    date_dim
  WHERE c_customer_sk = ws_bill_customer_sk
    AND ws_sold_date_sk = d_date_sk
  GROUP BY c_customer_id,
    c_first_name,
    c_last_name,
    c_preferred_cust_flag,
    c_birth_country,
    c_login,
    c_email_address,
    d_year
)
SELECT t_s_secyear.customer_id,
  t_s_secyear.customer_first_name,
  t_s_secyear.customer_last_name,
  t_s_secyear.customer_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
LIMIT 100;
--Q12
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) + interval '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
LIMIT 100;
--Q13
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
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
    ) AS alias1
  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 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
      AND d_moy = 11
    GROUP BY i_brand_id,
      i_class_id,
      i_category_id
    having SUM(ss_quantity * ss_list_price) > (
        SELECT average_sales
        FROM avg_sales
      )
    UNION ALL
    SELECT 'catalog' channel,
      i_brand_id,
      i_class_id,
      i_category_id,
      SUM(cs_quantity * cs_list_price) sales,
      COUNT(*) number_sales
    FROM catalog_sales,
      item,
      date_dim
    WHERE cs_item_sk IN (
        SELECT ss_item_sk
        FROM cross_items
      )
      AND cs_item_sk = i_item_sk
      AND cs_sold_date_sk = d_date_sk
      AND d_year = 1998 + 2
      AND d_moy = 11
    GROUP BY i_brand_id,
      i_class_id,
      i_category_id
    having SUM(cs_quantity * cs_list_price) > (
        SELECT average_sales
        FROM avg_sales
      )
    UNION ALL
    SELECT 'web' channel,
      i_brand_id,
      i_class_id,
      i_category_id,
      SUM(ws_quantity * ws_list_price) sales,
      COUNT(*) number_sales
    FROM web_sales,
      item,
      date_dim
    WHERE ws_item_sk IN (
        SELECT ss_item_sk
        FROM cross_items
      )
      AND ws_item_sk = i_item_sk
      AND ws_sold_date_sk = d_date_sk
      AND d_year = 1998 + 2
      AND d_moy = 11
    GROUP BY i_brand_id,
      i_class_id,
      i_category_id
    having SUM(ws_quantity * ws_list_price) > (
        SELECT average_sales
        FROM avg_sales
      )
  ) y
GROUP BY ROLLUP(channel, i_brand_id, i_class_id, i_category_id)
ORDER BY channel,
  i_brand_id,
  i_class_id,
  i_category_id
LIMIT 100;
--Q15
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
LIMIT 100;
--Q16
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-4-01' AND (CAST('1999-4-01' AS DATE) + interval '60 days')
  AND cs1.cs_ship_date_sk = d_date_sk
  AND cs1.cs_ship_addr_sk = ca_address_sk
  AND ca_state = 'GA'
  AND cs1.cs_call_center_sk = cc_call_center_sk
  AND cc_county IN (
    'Daviess County',
    'Franklin Parish',
    'Barrow County',
    'Luce County',
    'Fairfield 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)
LIMIT 100;
--Q17
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
LIMIT 100;
--Q18
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
LIMIT 100;
--Q19
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
LIMIT 100;
--Q20
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) + interval '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
LIMIT 100;
--Q21
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) - interval '30 days')
      AND (CAST ('1998-04-08' AS DATE) + interval '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
LIMIT 100;
--Q22
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
LIMIT 100;
--Q23
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) tpcds_cmax
  FROM (
      SELECT c_customer_sk,
        SUM(ss_quantity * ss_sales_price) csales
      FROM store_sales,
        customer,
        date_dim
      WHERE ss_customer_sk = c_customer_sk
        AND ss_sold_date_sk = d_date_sk
        AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
      GROUP BY c_customer_sk
    ) AS alias1
),
best_ss_customer AS (
  SELECT c_customer_sk,
    SUM(ss_quantity * ss_sales_price) ssales
  FROM store_sales,
    customer
  WHERE ss_customer_sk = c_customer_sk
  GROUP BY c_customer_sk
  having SUM(ss_quantity * ss_sales_price) > (95 / 100.0) * (
      SELECT *
      FROM max_store_sales
    )
)
SELECT SUM(sales)
FROM (
    SELECT cs_quantity * cs_list_price sales
    FROM catalog_sales,
      date_dim
    WHERE d_year = 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
      )
  ) AS alias2
LIMIT 100;
--Q24
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_current_addr_sk = ca_address_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
  )
ORDER BY c_last_name,
  c_first_name,
  s_store_name;
--Q25
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
LIMIT 100;
--Q26
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
LIMIT 100;
--Q27
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 = 'M'
  AND cd_marital_status = 'W'
  AND cd_education_status = 'College'
  AND d_year = 2002
  AND s_state IN ('MO', 'LA', 'GA', 'MI', 'SC', 'OH')
GROUP BY ROLLUP(i_item_id, s_state)
ORDER BY i_item_id,
  s_state
LIMIT 100;
--Q28
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
LIMIT 100;
--Q29
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
LIMIT 100;
--Q30
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 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
LIMIT 100;
--Q31
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
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) + interval '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) + interval '90 days')
      AND d_date_sk = cs_sold_date_sk
  )
LIMIT 100;
--Q33
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 = -6
  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 = -6
  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 = -6
  GROUP BY i_manufact_id
)
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
LIMIT 100;
--Q34
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 = '5001-10000'
      )
      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 (1999, 1999 + 1, 1999 + 2)
      AND store.s_county IN (
        'Daviess County',
        'Franklin Parish',
        'Barrow County',
        'Luce County',
        'Fairfield County',
        'Richland County',
        'Ziebach County',
        'Walker 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,
  ss_ticket_number;
--Q35
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
LIMIT 100;
--Q36
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 (
    'MO',
    'LA',
    'GA',
    'MI',
    'SC',
    'OH',
    'SD',
    'AL'
  )
GROUP BY ROLLUP(i_category, i_class)
ORDER BY lochierarchy DESC,
  CASE
    WHEN GROUPING(i_category) + GROUPING(i_class) = 0 THEN i_category
  END,
  rank_within_parent
LIMIT 100;
--Q37
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) + interval '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
LIMIT 100;
--Q38
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
LIMIT 100;
--Q39
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;
--Q40
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) - interval '30 days')
  AND (CAST ('1998-04-08' AS DATE) + interval '30 days')
GROUP BY w_state,
  i_item_id
ORDER BY w_state,
  i_item_id
LIMIT 100;
--Q41
SET adbpg_enable_encode_optimize TO OFF;
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
LIMIT 100;
RESET adbpg_enable_encode_optimize;
--Q42
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
LIMIT 100;
--Q43
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 = -6
  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
LIMIT 100;
--Q44
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 = 50
            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 = 50
                  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 = 50
            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 = 50
                  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
LIMIT 100;
--Q45
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
LIMIT 100;
--Q46
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 = 6
        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 (
        'Oakland',
        'Riverside',
        'Union',
        'Salem',
        'Greenwood'
      )
    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
LIMIT 100;
--Q47
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.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.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 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,
  nsum
LIMIT 100;
--Q48
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
SELECT channel,
  item,
  return_ratio,
  return_rank,
  currency_rank
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 decimal(15, 4)
                ) / CAST(
                  SUM(COALESCE(ws.ws_quantity, 0)) AS decimal(15, 4)
                )
              ) AS return_ratio,
              (
                CAST(
                  SUM(COALESCE(wr.wr_return_amt, 0)) AS decimal(15, 4)
                ) / CAST(
                  SUM(COALESCE(ws.ws_net_paid, 0)) AS decimal(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 decimal(15, 4)
                ) / CAST(
                  SUM(COALESCE(cs.cs_quantity, 0)) AS decimal(15, 4)
                )
              ) AS return_ratio,
              (
                CAST(
                  SUM(COALESCE(cr.cr_return_amount, 0)) AS decimal(15, 4)
                ) / CAST(
                  SUM(COALESCE(cs.cs_net_paid, 0)) AS decimal(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 decimal(15, 4)
                ) / CAST(
                  SUM(COALESCE(sts.ss_quantity, 0)) AS decimal(15, 4)
                )
              ) AS return_ratio,
              (
                CAST(
                  SUM(COALESCE(sr.sr_return_amt, 0)) AS decimal(15, 4)
                ) / CAST(
                  SUM(COALESCE(sts.ss_net_paid, 0)) AS decimal(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
      )
  ) AS alias1
ORDER BY 1,
  4,
  5,
  2
LIMIT 100;
--Q50
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
LIMIT 100;
--Q51
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 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
LIMIT 100;
--Q52
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
LIMIT 100;
--Q53
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
LIMIT 100;
--Q54
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 segment,
  COUNT(*) AS num_customers,
  segment * 50 AS segment_base
FROM segments
GROUP BY segment
ORDER BY segment,
  num_customers
LIMIT 100;
--Q55
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
LIMIT 100;
--Q56
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 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,
  i_item_id
LIMIT 100;
--Q57
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.cc_name,
    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 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,
  nsum
LIMIT 100;
--Q58
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 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
LIMIT 100;
--Q59
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 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
LIMIT 100;
--Q60
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 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
LIMIT 100;
--Q61
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
LIMIT 100;
--Q62
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
LIMIT 100;
--Q63
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
LIMIT 100;
--Q64
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,
  cs1.s1,
  cs2.s1;
--Q65
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
LIMIT 100;
--Q66
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
LIMIT 100;
--Q67
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
LIMIT 100;
--Q68
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 = 6
        OR household_demographics.hd_vehicle_count = 3
      )
      AND date_dim.d_year IN (1999, 1999 + 1, 1999 + 2)
      AND store.s_city IN ('Oakland', 'Riverside')
    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
LIMIT 100;
--Q69
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
LIMIT 100;
--Q70
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 GROUPING(s_state) + GROUPING(s_county) = 0 THEN s_state
  END,
  rank_within_parent
LIMIT 100;
--Q71
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
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
LIMIT 100;
--Q73
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 = '5001-10000'
      )
      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 (1999, 1999 + 1, 1999 + 2)
      AND store.s_county IN (
        'Daviess County',
        'Franklin Parish',
        'Barrow County',
        'Luce 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
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 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
LIMIT 100;
--Q75
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 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,
  sales_amt_diff
LIMIT 100;
--Q76
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
LIMIT 100;
--Q77
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) + interval '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) + interval '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) + interval '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) + interval '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) + interval '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) + interval '30 days')
    AND wr_web_page_sk = wp_web_page_sk
  GROUP BY wp_web_page_sk
)
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
LIMIT 100;
--Q78
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 ss_sold_year,
  ss_item_sk,
  ss_customer_sk,
  ROUND(
    (
      ss_qty /(COALESCE(ws_qty, 0) + COALESCE(cs_qty, 0))
    )::decimal,
    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 = ss_item_sk
    AND cs_customer_sk = ss_customer_sk
  )
WHERE (
    COALESCE(ws_qty, 0) > 0
    OR 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,
  ratio
LIMIT 100;
--Q79
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
LIMIT 100;
--Q80
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) + interval '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) + interval '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) + interval '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 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
LIMIT 100;
--Q81
SET adbpg_enable_encode_optimize TO OFF;
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 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
LIMIT 100;
RESET adbpg_enable_encode_optimize;
--Q82
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) + interval '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
LIMIT 100;
--Q83
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 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
LIMIT 100;
--Q84
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
LIMIT 100;
--Q85
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)
LIMIT 100;
--Q86
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 GROUPING(i_category) + GROUPING(i_class) = 0 THEN i_category
  END,
  rank_within_parent
LIMIT 100;
--Q87
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
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
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
LIMIT 100;
--Q90
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
LIMIT 100;
--Q91
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
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) + interval '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) + interval '90 days')
      AND d_date_sk = ws_sold_date_sk
  )
ORDER BY SUM(ws_ext_discount_amt)
LIMIT 100;
--Q93
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
LIMIT 100;
--Q94
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) + interval '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)
LIMIT 100;
--Q95
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 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) + interval '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)
LIMIT 100;
--Q96
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(*)
LIMIT 100;
--Q97
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 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
  )
LIMIT 100;
--Q98
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) + interval '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
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
LIMIT 100;

测试结果

测试查询所需时间如下。

Query	Time(s)
SUM	370.62
Q1	0.6
Q2	3.6
Q3	0.4
Q4	16.3
Q5	1.07
Q6	0.38
Q7	1.02
Q8	0.41
Q9	6.99
Q10	0.64
Q11	10.6
Q12	0.17
Q13	1.23
Q14	6.7
Q15	0.43
Q16	2.94
Q17	0.85
Q18	1.37
Q19	0.34
Q20	0.19
Q21	0.3
Q22	13.4
Q23	26.8
Q24	2.69
Q25	0.76
Q26	0.86
Q27	1.18
Q28	5.73
Q29	1.22
Q30	0.42
Q31	1.64
Q32	0.17
Q33	0.99
Q34	0.2
Q35	1.67
Q36	3.34
Q37	0.57
Q38	6.1
Q39	2
Q40	0.5
Q41	0.2
Q42	0.17
Q43	1
Q44	1.95
Q45	0.5
Q46	2.14
Q47	2.21
Q48	1.36
Q49	0.95
Q50	2.22
Q51	12.48
Q52	0.17
Q53	0.47
Q54	0.64
Q55	0.15
Q56	0.96
Q57	1.46
Q58	0.58
Q59	7.66
Q60	1.3
Q61	0.6
Q62	1.41
Q63	0.58
Q64	7.06
Q65	3.25
Q66	1.34
Q67	104.12
Q68	1.51
Q69	1.03
Q70	2.75
Q71	0.63
Q72	3.88
Q73	0.19
Q74	9.09
Q75	5.24
Q76	3.09
Q77	0.96
Q78	7.73
Q79	3.56
Q80	1.8
Q81	0.56
Q82	1.04
Q83	0.69
Q84	0.44
Q85	1.65
Q86	1.41
Q87	8.81
Q88	7.42
Q89	0.57
Q90	0.69
Q91	0.59
Q92	0.15
Q93	2.2
Q94	1.57
Q95	11.16
Q96	1.15
Q97	2.35
Q98	0.58
Q99	2.43