2 安装数据库

scp shenkun@172.16.100.134:/home/shenkun/g100/pkg-5-19.tar.gz ./
vast@123

export GAUSSHOME=`pwd`
export PATH=$PATH:$GAUSSHOME/bin
export LD_

alias vinit='vb_initdb -D $GAUSSHOME/data -w vb.12345 --nodename=db1'
alias vstart='vb_ctl start -D $GAUSSHOME/data'
alias vrestart='vb_ctl restart -D $GAUSSHOME/data'

3 安装benchmarksql

一、下载benchmarksql

# 1.安装java openjdk
yum install java
yum install java-1.8.0-openjdk-devel
# 2.下载benchmark安装包
https://sourceforge.net/projects/benchmarksql/files/latest/download
# 3.下载ant安装包
https://dlcdn.apache.org//ant/binaries/apache-ant-1.10.15-bin.tar.gz
# 4.解压benchmark与ant安装包
    # 建议git初始化benchmark安装包，如果改错了可以回退

二、配置环境变量

# 1.生成环境变量脚本
touch envbm
# 在envbm中添加一下内容
export BMROOT=`pwd`
export BMCODE=$BMROOT/benchmarksql-5.0
export PATH=$PATH:$BMROOT/apache-ant-1.10.15/bin
export DBIP=127.0.0.1
export DBPORT=5432
# 2.配置环境变量
source envbm

三、配置benchmarksql配置文件

# 1.生成配置文件
mkdir cfg
cp $BMCODE/run/props.pg $BMROOT/cfg/props.pg
# 2.编辑配置文件
sed -i '/^user=/s/=.*/=u1/' f1
    # user=bmuser
    # password=bmuser@123

    # warehouses=10
    # loadWorkers=10

    # terminals=10
    # runMins=5
    # limitTxnsPerMin=0

# 3. 设置表的fillfictor=80
cp $BMCODE/run/sql.common/tableCreates.sql $BMCODE/run/sql.common/_tableCreates.sql

sed -i '/^);/s/);.*/) with (fillfactor=80);/' $BMCODE/run/sql.common/tableCreates.sql

四、生成便捷使用benchmarksql的脚本

touch toolbm
# 复制以下内容
sed -i "s/^conn=.*/conn=jdbc:postgresql:\/\/$DBIP:$DBPORT\/bmdb?prepareThreshold=1\&batchMode=on\&fetchsize=10/" $BMROOT/cfg/props.pg

case $1 in
    init)
        cd $BMCODE
        ant
        cd -
        gsql -p $DBPORT -d postgres -c "
            DROP DATABASE IF EXISTS bmdb;
            DROP USER IF EXISTS bmuser;
            CREATE USER bmuser PASSWORD 'bmuser@123' SYSADMIN;
            CREATE DATABASE bmdb OWNER bmuser; "
    ;;
    check)
        gsql -p $DBPORT -d bmdb -U bmuser -W 'bmuser@123' -c "\d+"
    ;;
    create)
        cd $BMCODE/run
        ./runDatabaseBuild.sh  $BMROOT/cfg/props.pg
        cd -
    ;;
    run)
        cd $BMCODE/run
        ./runBenchmark.sh   $BMROOT/cfg/props.pg
        cd -
    ;;
    run_full)
        # run 5 min first
        sed -i '/^runMins=/s/=.*/=5/' $BMROOT/cfg/props.pg
        cd $BMCODE/run
        ./runBenchmark.sh   $BMROOT/cfg/props.pg
        # then run 30 min
        sed -i '/^runMins=/s/=.*/=30/' $BMROOT/cfg/props.pg
        ./runBenchmark.sh   $BMROOT/cfg/props.pg
        cd -
    ;;
    drop)
        cd $BMCODE/run
        ./runDatabaseDestroy.sh   $BMROOT/cfg/props.pg
        cd -
    ;;
    *)
        echo " command   |  function "
        echo "-----------+------------------------------------------------"
        echo "  init     : create user, database"
        echo "  create   : create table, index, ..; insert data"
        echo "  check    ：show all tables"
        echo "  run      : select, update"
        echo "  run_full : select, update (run 5 min, then run 30 min)"
        echo "  drop     : drop table, index, .."
    ;;
esac
# 设置脚本执行权限
chmod +x toolbm

3 使用benchmarksql测试tpcc

# 1.创建用户、创建database
./toolbm init
# 2.创建表，导入数据
./toolbm create
# 3.运行tpcc
./toolbm run
# 4.删除数据
./toolbm drop

测试模型

tpc-c (oltp)

neworder(45%)

-- 1. 查询客户信用额度、折扣和余额 (点查)
SELECT c_discount, c_last, c_credit, w_tax
FROM customer, warehouse
WHERE w_id = ? AND c_w_id = ? AND c_d_id = ? AND c_id = ?;

-- 2. 查询地区税率 (点查)
SELECT d_tax, d_next_o_id
FROM district
WHERE d_w_id = ? AND d_id = ? FOR UPDATE; -- ⚠️ 关键: FOR UPDATE 行锁

-- 3. 更新地区下一个订单ID (UPDATE)
UPDATE district SET d_next_o_id = d_next_o_id + 1
WHERE d_w_id = ? AND d_id = ?;

-- 4. 插入订单头 (INSERT)
INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local)
VALUES (?, ?, ?, ?, ?, ?, ?);

-- 5. 插入新订单记录 (INSERT)
INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (?, ?, ?);

-- 6. 循环执行以下3条 (每个订单项一次, 平均10次):
-- 6a. 查询商品信息 (点查)
SELECT i_price, i_name, i_data FROM item WHERE i_id = ?;

-- 6b. 查询并锁定库存 (FOR UPDATE)
SELECT s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, s_dist_xx
FROM stock WHERE s_i_id = ? AND s_w_id = ? FOR UPDATE;

-- 6c. 更新库存 (条件UPDATE)
UPDATE stock SET s_quantity = ?, s_ytd = ?, s_order_cnt = ?, s_remote_cnt = ?
WHERE s_i_id = ? AND s_w_id = ?;

-- 6d. 插入订单明细 (INSERT)
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id,
                        ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);

payment(43%)

-- 1. 更新仓库余额 (点查+UPDATE)
UPDATE warehouse SET w_ytd = w_ytd + ? WHERE w_id = ?;

-- 2. 查询仓库信息 (用于历史记录)
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name
FROM warehouse WHERE w_id = ?;

-- 3. 更新地区余额 (点查+UPDATE)
UPDATE district SET d_ytd = d_ytd + ? WHERE d_w_id = ? AND d_id = ?;

-- 4. 查询地区信息
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name
FROM district WHERE d_w_id = ? AND d_id = ?;

-- 5. 按ID或姓名查找客户 (⚠️ 两种路径)
-- 路径A (60%概率): 按主键点查
SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city,
    c_state, c_zip, c_phone, c_credit, c_credit_lim, c_discount,
    c_balance, c_since
FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ? FOR UPDATE;

-- 路径B (40%概率): 按姓名范围扫描 + 排序取中间值
SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city,
    c_state, c_zip, c_phone, c_credit, c_credit_lim, c_discount,
    c_balance, c_since
FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ?
ORDER BY c_first FOR UPDATE;

-- 6. 更新客户余额和历史计数
UPDATE customer SET c_balance = ?, c_ytd_payment = ?, c_payment_cnt = ?
WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?;

-- 7. 如果客户信用为 'BC', 追加数据到 c_data (TEXT/CLOB 操作)
UPDATE customer SET c_data = substring(? || c_data, 1, 500)
WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?;

-- 8. 插入历史流水 (INSERT)
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id,
                    h_date, h_amount, h_data)
VALUES (?, ?, ?, ?, ?, ?, ?, ?);

Order-Status (%4)

-- 1. 查找客户 (同 Payment 的路径A/B)
-- ... (同上)

-- 2. 查询客户最近一笔订单 (⚠️ 相关子查询 / LIMIT)
SELECT o_id, o_carrier_id, o_entry_d
FROM orders
WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?
ORDER BY o_id DESC LIMIT 1;

-- 3. 查询该订单的所有明细 (范围扫描)
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d
FROM order_line
WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?;

delivery (4%)

-- 循环处理10个地区:
-- 1. 删除并获取最早的新订单 (DELETE + RETURNING)
DELETE FROM new_order
WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = (
    SELECT min(no_o_id) FROM new_order
    WHERE no_w_id = ? AND no_d_id = ?
) RETURNING no_o_id;

-- 2. 查询订单关联的客户ID
SELECT o_c_id FROM orders
WHERE o_id = ? AND o_d_id = ? AND o_w_id = ?;

-- 3. 更新订单承运商
UPDATE orders SET o_carrier_id = ?
WHERE o_id = ? AND o_d_id = ? AND o_w_id = ?;

-- 4. 更新所有订单明细的配送时间
UPDATE order_line SET ol_delivery_d = ?
WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?;

-- 5. 汇总订单金额并更新客户余额
UPDATE customer SET c_balance = c_balance + ?, c_delivery_cnt = c_delivery_cnt + 1
WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?;

stock-level(4%)

-- 1. 查询地区阈值
SELECT d_next_o_id FROM district WHERE d_w_id = ? AND d_id = ?;

-- 2. 统计低库存商品种类数 (⚠️ 三表JOIN + 子查询 + GROUP BY)
SELECT COUNT(DISTINCT s_i_id) AS stock_count
FROM order_line, stock
WHERE ol_w_id = ? AND ol_d_id = ?
AND ol_o_id < ? AND ol_o_id >= ? - 20
AND s_w_id = ? AND s_i_id = ol_i_id
AND s_quantity < ?;

tpc-h (olap)

tpc-ds (优化器，99条查询)

\timing on

-- start query 1 in stream 0 using template query96.tpl
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;

-- end query 1 in stream 0 using template query96.tpl
-- start query 2 in stream 0 using template query7.tpl
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 = 'M'
and cd_marital_status = 'M'
and cd_education_status = '4 yr Degree'
and (p_channel_email = 'N' or p_channel_event = 'N')
and d_year = 2001
group by i_item_id
order by i_item_id LIMIT 100;

-- end query 2 in stream 0 using template query7.tpl
-- start query 3 in stream 0 using template query75.tpl
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 = 'Shoes'
                        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 = 'Shoes'
                        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 = 'Shoes') 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=2000
AND prev_yr.d_year=2000-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;

-- end query 3 in stream 0 using template query75.tpl
-- start query 4 in stream 0 using template query44.tpl
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 = 6
                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 = 6
                                                    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 = 6
                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 = 6
                                                    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;

-- end query 4 in stream 0 using template query44.tpl
-- start query 5 in stream 0 using template query39.tpl
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 = 2001
                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 = 1
and inv2.d_moy = 1 + 1
order by inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov
    , inv2.d_moy, inv2.mean, inv2.cov
;
with inv as
        (select w_warehouse_name
            , w_warehouse_sk
            , i_item_sk
            , d_moy
            , stdev
            , mean
            , case mean when 0 then null else stdev / mean end cov
        from (select w_warehouse_name
                    , w_warehouse_sk
                    , i_item_sk
                    , d_moy
                    , stddev_samp(inv_quantity_on_hand) stdev
                    , avg(inv_quantity_on_hand)         mean
                from inventory
                , item
                , warehouse
                , date_dim
                where inv_item_sk = i_item_sk
                and inv_warehouse_sk = w_warehouse_sk
                and inv_date_sk = d_date_sk
                and d_year = 2001
                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 = 1
and inv2.d_moy = 1 + 1
and inv1.cov > 1.5
order by inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov
    , inv2.d_moy, inv2.mean, inv2.cov
;

-- end query 5 in stream 0 using template query39.tpl
-- start query 6 in stream 0 using template query80.tpl
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('2002-08-04' as date)
            and (cast('2002-08-04' as date) + 30 days)
            and ss_store_sk = s_store_sk
            and ss_item_sk = i_item_sk
            and i_current_price > 50
            and ss_promo_sk = p_promo_sk
            and p_channel_tv = 'N'
        group by s_store_id)
        ,
    csr as
        (select cp_catalog_page_id                            as catalog_page_id,
                sum(cs_ext_sales_price)                       as sales,
                sum(coalesce(cr_return_amount, 0))            as returns,
                sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
        from catalog_sales
                left outer join catalog_returns on
            (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
            date_dim,
            catalog_page,
            item,
            promotion
        where cs_sold_date_sk = d_date_sk
            and d_date between cast('2002-08-04' as date)
            and (cast('2002-08-04' as date) + 30 days)
            and cs_catalog_page_sk = cp_catalog_page_sk
            and cs_item_sk = i_item_sk
            and i_current_price > 50
            and cs_promo_sk = p_promo_sk
            and p_channel_tv = 'N'
        group by cp_catalog_page_id)
        ,
    wsr as
        (select web_site_id,
                sum(ws_ext_sales_price)                       as sales,
                sum(coalesce(wr_return_amt, 0))               as returns,
                sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
        from web_sales
                left outer join web_returns on
            (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),
            date_dim,
            web_site,
            item,
            promotion
        where ws_sold_date_sk = d_date_sk
            and d_date between cast('2002-08-04' as date)
            and (cast('2002-08-04' as date) + 30 days)
            and ws_web_site_sk = web_site_sk
            and ws_item_sk = i_item_sk
            and i_current_price > 50
            and ws_promo_sk = p_promo_sk
            and p_channel_tv = 'N'
        group by web_site_id)
select 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;

-- end query 6 in stream 0 using template query80.tpl
-- start query 7 in stream 0 using template query32.tpl
select sum(cs_ext_discount_amt) as "excess discount amount"
from catalog_sales
, item
, date_dim
where i_manufact_id = 283
and i_item_sk = cs_item_sk
and d_date between '1999-02-22' and
    (cast('1999-02-22' as date) + 90 days)
and d_date_sk = cs_sold_date_sk
and cs_ext_discount_amt
    > (select 1.3 * avg(cs_ext_discount_amt)
    from catalog_sales
        , date_dim
    where cs_item_sk = i_item_sk
        and d_date between '1999-02-22' and
        (cast('1999-02-22' as date) + 90 days)
        and d_date_sk = cs_sold_date_sk)
    LIMIT 100;

-- end query 7 in stream 0 using template query32.tpl
-- start query 8 in stream 0 using template query19.tpl
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 = 8
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;

-- end query 8 in stream 0 using template query19.tpl
-- start query 9 in stream 0 using template query25.tpl
select i_item_id
    , i_item_desc
    , s_store_id
    , s_store_name
    , min(ss_net_profit) as store_sales_profit
    , min(sr_net_loss)   as store_returns_loss
    , min(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 = 2002
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 = 2002
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 = 2002
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;

-- end query 9 in stream 0 using template query25.tpl
-- start query 10 in stream 0 using template query78.tpl
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_customer_sk,
    round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 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 = 2001
order by 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;

-- end query 10 in stream 0 using template query78.tpl
-- start query 11 in stream 0 using template query86.tpl
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 1205 and 1205 + 11
and d1.d_date_sk = ws_sold_date_sk
and i_item_sk = ws_item_sk
group by rollup (i_category, i_class)
order by lochierarchy desc,
        case when lochierarchy = 0 then i_category end,
        rank_within_parent LIMIT 100;

-- end query 11 in stream 0 using template query86.tpl
-- start query 12 in stream 0 using template query1.tpl
with customer_total_return as
        (select sr_customer_sk             as ctr_customer_sk
            , sr_store_sk                as ctr_store_sk
            , sum(SR_RETURN_AMT_INC_TAX) as ctr_total_return
        from store_returns
            , date_dim
        where sr_returned_date_sk = d_date_sk
            and d_year = 1999
        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 = 'TN'
and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id LIMIT 100;

-- end query 12 in stream 0 using template query1.tpl
-- start query 13 in stream 0 using template query91.tpl
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 = 2002
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 'Unknown%'
and ca_gmt_offset = -6
group by cc_call_center_id, cc_name, cc_manager, cd_marital_status, cd_education_status
order by sum(cr_net_loss) desc;

-- end query 13 in stream 0 using template query91.tpl
-- start query 14 in stream 0 using template query21.tpl
select *
from (select w_warehouse_name
        , i_item_id
        , sum(case
                    when (cast(d_date as date) < cast('2000-05-19' as date))
                        then inv_quantity_on_hand
                    else 0 end) as inv_before
        , sum(case
                    when (cast(d_date as date) >= cast('2000-05-19' 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('2000-05-19' as date) - 30 days)
        and (cast('2000-05-19' as date) + 30 days)
    group by w_warehouse_name, i_item_id) x
where (case
        when inv_before > 0
            then inv_after / inv_before
        else null
    end) between 2.0 / 3.0 and 3.0 / 2.0
order by w_warehouse_name
    , i_item_id LIMIT 100;

-- end query 14 in stream 0 using template query21.tpl
-- start query 15 in stream 0 using template query43.tpl
select s_store_name,
    s_store_id,
    sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end)    sun_sales,
    sum(case when (d_day_name = 'Monday') then ss_sales_price else null end)    mon_sales,
    sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end)   tue_sales,
    sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end) wed_sales,
    sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end)  thu_sales,
    sum(case when (d_day_name = 'Friday') then ss_sales_price else null end)    fri_sales,
    sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end)  sat_sales
from date_dim,
    store_sales,
    store
where d_date_sk = ss_sold_date_sk
and s_store_sk = ss_store_sk
and s_gmt_offset = -5
and d_year = 2000
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;

-- end query 15 in stream 0 using template query43.tpl
-- start query 16 in stream 0 using template query27.tpl
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 = 'U'
and cd_education_status = 'Secondary'
and d_year = 2000
and s_state in ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
group by rollup (i_item_id, s_state)
order by i_item_id
    , s_state LIMIT 100;

-- end query 16 in stream 0 using template query27.tpl
-- start query 17 in stream 0 using template query94.tpl
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-4-01' and
    (cast('1999-4-01' as date) + 60 days)
and ws1.ws_ship_date_sk = d_date_sk
and ws1.ws_ship_addr_sk = ca_address_sk
and ca_state = 'WI'
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;

-- end query 17 in stream 0 using template query94.tpl
-- start query 18 in stream 0 using template query45.tpl
select ca_zip, ca_city, 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_city
order by ca_zip, ca_city LIMIT 100;

-- end query 18 in stream 0 using template query45.tpl
-- start query 19 in stream 0 using template query58.tpl
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 = '2000-02-12'))
            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 = '2000-02-12'))
            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 = '2000-02-12'))
            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;

-- end query 19 in stream 0 using template query58.tpl
-- start query 20 in stream 0 using template query64.tpl
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 ('light', 'cyan', 'burnished', 'green', 'almond', 'smoke')
            and i_current_price between 22 and 22 + 10
            and i_current_price between 22 + 1 and 22 + 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 = 2001
and cs2.syear = 2001 + 1
and cs2.cnt <= cs1.cnt
and cs1.store_name = cs2.store_name
and cs1.store_zip = cs2.store_zip
order by cs1.product_name
    , cs1.store_name
    , cs2.cnt
    , cs1.s1
    , cs2.s1;

-- end query 20 in stream 0 using template query64.tpl
-- start query 21 in stream 0 using template query36.tpl
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 = 2001
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and s_state in ('TN', 'TN', 'TN', 'TN',
                'TN', 'TN', 'TN', 'TN')
group by rollup (i_category, i_class)
order by lochierarchy desc
    , case when lochierarchy = 0 then i_category end
    , rank_within_parent LIMIT 100;

-- end query 21 in stream 0 using template query36.tpl
-- start query 22 in stream 0 using template query33.tpl
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 = 4
            and ss_addr_sk = ca_address_sk
            and ca_gmt_offset = -5
            group by i_manufact_id),
    cs as (select i_manufact_id,
                sum(cs_ext_sales_price) total_sales
            from catalog_sales,
                date_dim,
                customer_address,
                item
            where i_manufact_id in (select i_manufact_id
                                    from item
                                    where i_category in ('Books'))
            and cs_item_sk = i_item_sk
            and cs_sold_date_sk = d_date_sk
            and d_year = 1999
            and d_moy = 4
            and cs_bill_addr_sk = ca_address_sk
            and ca_gmt_offset = -5
            group by i_manufact_id),
    ws as (select i_manufact_id,
                sum(ws_ext_sales_price) total_sales
            from web_sales,
                date_dim,
                customer_address,
                item
            where i_manufact_id in (select i_manufact_id
                                    from item
                                    where i_category in ('Books'))
            and ws_item_sk = i_item_sk
            and ws_sold_date_sk = d_date_sk
            and d_year = 1999
            and d_moy = 4
            and ws_bill_addr_sk = ca_address_sk
            and ca_gmt_offset = -5
            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;

-- end query 22 in stream 0 using template query33.tpl
-- start query 23 in stream 0 using template query46.tpl
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 = 3 or
            household_demographics.hd_vehicle_count = 1)
        and date_dim.d_dow in (6, 0)
        and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
        and store.s_city in ('Midway', 'Fairview', 'Fairview', 'Midway', 'Fairview')
    group by ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city) dn,
    customer,
    customer_address current_addr
where ss_customer_sk = c_customer_sk
and customer.c_current_addr_sk = current_addr.ca_address_sk
and current_addr.ca_city <> bought_city
order by c_last_name
    , c_first_name
    , ca_city
    , bought_city
    , ss_ticket_number LIMIT 100;

-- end query 23 in stream 0 using template query46.tpl
-- start query 24 in stream 0 using template query62.tpl
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 1217 and 1217 + 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;

-- end query 24 in stream 0 using template query62.tpl
-- start query 25 in stream 0 using template query16.tpl
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-5-01' and
    (cast('1999-5-01' as date) + 60 days)
and cs1.cs_ship_date_sk = d_date_sk
and cs1.cs_ship_addr_sk = ca_address_sk
and ca_state = 'ID'
and cs1.cs_call_center_sk = cc_call_center_sk
and cc_county in ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County',
                    'Williamson County'
    )
and exists (select *
            from catalog_sales cs2
            where cs1.cs_order_number = cs2.cs_order_number
                and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
and not exists(select *
                from catalog_returns cr1
                where cs1.cs_order_number = cr1.cr_order_number)
order by count(distinct cs_order_number) LIMIT 100;

-- end query 25 in stream 0 using template query16.tpl
-- start query 26 in stream 0 using template query10.tpl
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 ('Clinton County', 'Platte County', 'Franklin County', 'Louisa County', 'Harmon 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 3 and 3 + 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 3 ANd 3 + 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 3 and 3 + 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;

-- end query 26 in stream 0 using template query10.tpl
-- start query 27 in stream 0 using template query63.tpl
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
            (1181, 1181 + 1, 1181 + 2, 1181 + 3, 1181 + 4, 1181 + 5, 1181 + 6, 1181 + 7, 1181 + 8, 1181 + 9, 1181 + 10,
            1181 + 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;

-- end query 27 in stream 0 using template query63.tpl
-- start query 28 in stream 0 using template query69.tpl
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 ('IN', 'VA', 'MS')
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 2 and 2 + 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 = 2002
                    and d_moy between 2 and 2 + 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 = 2002
                    and d_moy between 2 and 2 + 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;

-- end query 28 in stream 0 using template query69.tpl
-- start query 29 in stream 0 using template query60.tpl
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 ('Shoes'))
            and ss_item_sk = i_item_sk
            and ss_sold_date_sk = d_date_sk
            and d_year = 2001
            and d_moy = 10
            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 ('Shoes'))
            and cs_item_sk = i_item_sk
            and cs_sold_date_sk = d_date_sk
            and d_year = 2001
            and d_moy = 10
            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 ('Shoes'))
            and ws_item_sk = i_item_sk
            and ws_sold_date_sk = d_date_sk
            and d_year = 2001
            and d_moy = 10
            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;

-- end query 29 in stream 0 using template query60.tpl
-- start query 30 in stream 0 using template query59.tpl
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 1206 and 1206 + 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 1206 + 12 and 1206 + 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;

-- end query 30 in stream 0 using template query59.tpl
-- start query 31 in stream 0 using template query37.tpl
select i_item_id
    , i_item_desc
    , i_current_price
from item,
    inventory,
    date_dim,
    catalog_sales
where i_current_price between 26 and 26 + 30
and inv_item_sk = i_item_sk
and d_date_sk = inv_date_sk
and d_date between cast('2001-06-09' as date) and (cast('2001-06-09' as date) + 60 days)
and i_manufact_id in (744, 884, 722, 693)
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;

-- end query 31 in stream 0 using template query37.tpl
-- start query 32 in stream 0 using template query98.tpl
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 ('Shoes', 'Music', 'Men')
and ss_sold_date_sk = d_date_sk
and d_date between cast('2000-01-05' as date)
    and (cast('2000-01-05' as date) + 30 days)
group by i_item_id
    , i_item_desc
    , i_category
    , i_class
    , i_current_price
order by i_category
    , i_class
    , i_item_id
    , i_item_desc
    , revenueratio;

-- end query 32 in stream 0 using template query98.tpl
-- start query 33 in stream 0 using template query85.tpl
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 = 2001
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 = '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 100.00 and 150.00
            )
        or
        (
                    cd1.cd_marital_status = 'U'
                and
                    cd1.cd_marital_status = cd2.cd_marital_status
                and
                    cd1.cd_education_status = 'Unknown'
                and
                    cd1.cd_education_status = cd2.cd_education_status
                and
                    ws_sales_price between 50.00 and 100.00
            )
        or
        (
                    cd1.cd_marital_status = 'M'
                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 ('SC', 'IN', 'VA')
                and ws_net_profit between 100 and 200
            )
        or
        (
                    ca_country = 'United States'
                and
                    ca_state in ('WA', 'KS', 'KY')
                and ws_net_profit between 150 and 300
            )
        or
        (
                    ca_country = 'United States'
                and
                    ca_state in ('SD', 'WI', 'NE')
                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;

-- end query 33 in stream 0 using template query85.tpl
-- start query 34 in stream 0 using template query70.tpl
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 1180 and 1180 + 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 1180 and 1180 + 11
            and d_date_sk = ss_sold_date_sk
            and s_store_sk = ss_store_sk
            group by s_state) tmp1
    where ranking <= 5)
group by rollup (s_state, s_county)
order by lochierarchy desc
    , case when lochierarchy = 0 then s_state end
    , rank_within_parent LIMIT 100;

-- end query 34 in stream 0 using template query70.tpl
-- start query 35 in stream 0 using template query67.tpl
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 1194 and 1194 + 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;

-- end query 35 in stream 0 using template query67.tpl
-- start query 36 in stream 0 using template query28.tpl
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 28 and 28 + 10
        or ss_coupon_amt between 12573 and 12573 + 1000
        or ss_wholesale_cost between 33 and 33 + 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 143 and 143 + 10
        or ss_coupon_amt between 5562 and 5562 + 1000
        or ss_wholesale_cost between 45 and 45 + 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 159 and 159 + 10
        or ss_coupon_amt between 2807 and 2807 + 1000
        or ss_wholesale_cost between 24 and 24 + 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 24 and 24 + 10
        or ss_coupon_amt between 3706 and 3706 + 1000
        or ss_wholesale_cost between 46 and 46 + 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 76 and 76 + 10
        or ss_coupon_amt between 2096 and 2096 + 1000
        or ss_wholesale_cost between 50 and 50 + 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 169 and 169 + 10
        or ss_coupon_amt between 10672 and 10672 + 1000
        or ss_wholesale_cost between 58 and 58 + 20)) B6 LIMIT 100;

-- end query 36 in stream 0 using template query28.tpl
-- start query 37 in stream 0 using template query81.tpl
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 = 'TX'
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;

-- end query 37 in stream 0 using template query81.tpl
-- start query 38 in stream 0 using template query97.tpl
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 1211 and 1211 + 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 1211 and 1211 + 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;

-- end query 38 in stream 0 using template query97.tpl
-- start query 39 in stream 0 using template query66.tpl
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
        , 'FEDEX' || ',' || 'GERMA' as ship_carriers
        , d_year as year
        , sum (case when d_moy = 1
    then ws_ext_list_price* ws_quantity else 0 end) as jan_sales
        , sum (case when d_moy = 2
    then ws_ext_list_price* ws_quantity else 0 end) as feb_sales
        , sum (case when d_moy = 3
    then ws_ext_list_price* ws_quantity else 0 end) as mar_sales
        , sum (case when d_moy = 4
    then ws_ext_list_price* ws_quantity else 0 end) as apr_sales
        , sum (case when d_moy = 5
    then ws_ext_list_price* ws_quantity else 0 end) as may_sales
        , sum (case when d_moy = 6
    then ws_ext_list_price* ws_quantity else 0 end) as jun_sales
        , sum (case when d_moy = 7
    then ws_ext_list_price* ws_quantity else 0 end) as jul_sales
        , sum (case when d_moy = 8
    then ws_ext_list_price* ws_quantity else 0 end) as aug_sales
        , sum (case when d_moy = 9
    then ws_ext_list_price* ws_quantity else 0 end) as sep_sales
        , sum (case when d_moy = 10
    then ws_ext_list_price* ws_quantity else 0 end) as oct_sales
        , sum (case when d_moy = 11
    then ws_ext_list_price* ws_quantity else 0 end) as nov_sales
        , sum (case when d_moy = 12
    then ws_ext_list_price* ws_quantity else 0 end) as dec_sales
        , sum (case when d_moy = 1
    then ws_net_profit * ws_quantity else 0 end) as jan_net
        , sum (case when d_moy = 2
    then ws_net_profit * ws_quantity else 0 end) as feb_net
        , sum (case when d_moy = 3
    then ws_net_profit * ws_quantity else 0 end) as mar_net
        , sum (case when d_moy = 4
    then ws_net_profit * ws_quantity else 0 end) as apr_net
        , sum (case when d_moy = 5
    then ws_net_profit * ws_quantity else 0 end) as may_net
        , sum (case when d_moy = 6
    then ws_net_profit * ws_quantity else 0 end) as jun_net
        , sum (case when d_moy = 7
    then ws_net_profit * ws_quantity else 0 end) as jul_net
        , sum (case when d_moy = 8
    then ws_net_profit * ws_quantity else 0 end) as aug_net
        , sum (case when d_moy = 9
    then ws_net_profit * ws_quantity else 0 end) as sep_net
        , sum (case when d_moy = 10
    then ws_net_profit * ws_quantity else 0 end) as oct_net
        , sum (case when d_moy = 11
    then ws_net_profit * ws_quantity else 0 end) as nov_net
        , sum (case when d_moy = 12
    then ws_net_profit * 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 = 2001
    and t_time between 19072 and 19072+28800
    and sm_carrier in ('FEDEX', 'GERMA')
    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
        , 'FEDEX' || ',' || 'GERMA' as ship_carriers
        , d_year as year
        , sum (case when d_moy = 1
    then cs_sales_price* cs_quantity else 0 end) as jan_sales
        , sum (case when d_moy = 2
    then cs_sales_price* cs_quantity else 0 end) as feb_sales
        , sum (case when d_moy = 3
    then cs_sales_price* cs_quantity else 0 end) as mar_sales
        , sum (case when d_moy = 4
    then cs_sales_price* cs_quantity else 0 end) as apr_sales
        , sum (case when d_moy = 5
    then cs_sales_price* cs_quantity else 0 end) as may_sales
        , sum (case when d_moy = 6
    then cs_sales_price* cs_quantity else 0 end) as jun_sales
        , sum (case when d_moy = 7
    then cs_sales_price* cs_quantity else 0 end) as jul_sales
        , sum (case when d_moy = 8
    then cs_sales_price* cs_quantity else 0 end) as aug_sales
        , sum (case when d_moy = 9
    then cs_sales_price* cs_quantity else 0 end) as sep_sales
        , sum (case when d_moy = 10
    then cs_sales_price* cs_quantity else 0 end) as oct_sales
        , sum (case when d_moy = 11
    then cs_sales_price* cs_quantity else 0 end) as nov_sales
        , sum (case when d_moy = 12
    then cs_sales_price* cs_quantity else 0 end) as dec_sales
        , sum (case when d_moy = 1
    then cs_net_paid * cs_quantity else 0 end) as jan_net
        , sum (case when d_moy = 2
    then cs_net_paid * cs_quantity else 0 end) as feb_net
        , sum (case when d_moy = 3
    then cs_net_paid * cs_quantity else 0 end) as mar_net
        , sum (case when d_moy = 4
    then cs_net_paid * cs_quantity else 0 end) as apr_net
        , sum (case when d_moy = 5
    then cs_net_paid * cs_quantity else 0 end) as may_net
        , sum (case when d_moy = 6
    then cs_net_paid * cs_quantity else 0 end) as jun_net
        , sum (case when d_moy = 7
    then cs_net_paid * cs_quantity else 0 end) as jul_net
        , sum (case when d_moy = 8
    then cs_net_paid * cs_quantity else 0 end) as aug_net
        , sum (case when d_moy = 9
    then cs_net_paid * cs_quantity else 0 end) as sep_net
        , sum (case when d_moy = 10
    then cs_net_paid * cs_quantity else 0 end) as oct_net
        , sum (case when d_moy = 11
    then cs_net_paid * cs_quantity else 0 end) as nov_net
        , sum (case when d_moy = 12
    then cs_net_paid * cs_quantity else 0 end) as dec_net
    from
    catalog_sales
        , warehouse
        , date_dim
        , time_dim
        , ship_mode
    where
    cs_warehouse_sk = w_warehouse_sk
    and cs_sold_date_sk = d_date_sk
    and cs_sold_time_sk = t_time_sk
    and cs_ship_mode_sk = sm_ship_mode_sk
    and d_year = 2001
    and t_time between 19072 AND 19072+28800
    and sm_carrier in ('FEDEX', 'GERMA')
    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;

-- end query 39 in stream 0 using template query66.tpl
-- start query 40 in stream 0 using template query90.tpl
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 9 and 9 + 1
        and household_demographics.hd_dep_count = 2
        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 15 and 15+1
        and household_demographics.hd_dep_count = 2
        and web_page.wp_char_count between 5000 and 5200) pt
order by am_pm_ratio
    LIMIT 100;

-- end query 40 in stream 0 using template query90.tpl
-- start query 41 in stream 0 using template query17.tpl
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 = '1999Q1'
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 ('1999Q1', '1999Q2', '1999Q3')
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 ('1999Q1', '1999Q2', '1999Q3')
group by i_item_id
    , i_item_desc
    , s_state
order by i_item_id
    , i_item_desc
    , s_state LIMIT 100;

-- end query 41 in stream 0 using template query17.tpl
-- start query 42 in stream 0 using template query47.tpl
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 = 2001 or
                        (d_year = 2001 - 1 and d_moy = 12) or
                        (d_year = 2001 + 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.s_store_name
                , v1.s_company_name
                , v1.d_year
                , 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 = 2001
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;

-- end query 42 in stream 0 using template query47.tpl
-- start query 43 in stream 0 using template query95.tpl
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 '2002-5-01' and
    (cast('2002-5-01' as date) + 60 days)
and ws1.ws_ship_date_sk = d_date_sk
and ws1.ws_ship_addr_sk = ca_address_sk
and ca_state = 'MA'
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;

-- end query 43 in stream 0 using template query95.tpl
-- start query 44 in stream 0 using template query92.tpl
select sum(ws_ext_discount_amt) as "Excess Discount Amount"
from web_sales
, item
, date_dim
where i_manufact_id = 914
and i_item_sk = ws_item_sk
and d_date between '2001-01-25' and
    (cast('2001-01-25' as date) + 90 days)
and d_date_sk = ws_sold_date_sk
and ws_ext_discount_amt
    > (SELECT 1.3 * avg(ws_ext_discount_amt)
    FROM web_sales
        , date_dim
    WHERE ws_item_sk = i_item_sk
        and d_date between '2001-01-25' and
        (cast('2001-01-25' as date) + 90 days)
        and d_date_sk = ws_sold_date_sk)
order by sum(ws_ext_discount_amt) LIMIT 100;

-- end query 44 in stream 0 using template query92.tpl
-- start query 45 in stream 0 using template query3.tpl
select dt.d_year
    , item.i_brand_id    brand_id
    , item.i_brand       brand
    , sum(ss_net_profit) 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 = 445
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;

-- end query 45 in stream 0 using template query3.tpl
-- start query 46 in stream 0 using template query51.tpl
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 1215 and 1215 + 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 1215 and 1215 + 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;

-- end query 46 in stream 0 using template query51.tpl
-- start query 47 in stream 0 using template query35.tpl
select ca_state,
    cd_gender,
    cd_marital_status,
    cd_dep_count,
    count(*) cnt1,
    max(cd_dep_count),
    stddev_samp(cd_dep_count),
    stddev_samp(cd_dep_count),
    cd_dep_employed_count,
    count(*) cnt2,
    max(cd_dep_employed_count),
    stddev_samp(cd_dep_employed_count),
    stddev_samp(cd_dep_employed_count),
    cd_dep_college_count,
    count(*) cnt3,
    max(cd_dep_college_count),
    stddev_samp(cd_dep_college_count),
    stddev_samp(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 = 2000
                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 = 2000
                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 = 2000
                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;

-- end query 47 in stream 0 using template query35.tpl
-- start query 48 in stream 0 using template query49.tpl
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
        ))
order by 1, 4, 5, 2 LIMIT 100;

-- end query 48 in stream 0 using template query49.tpl
-- start query 49 in stream 0 using template query9.tpl
select case
        when (select count(*)
                from store_sales
                where ss_quantity between 1 and 20) > 31002
            then (select avg(ss_ext_discount_amt)
                    from store_sales
                    where ss_quantity between 1 and 20)
        else (select avg(ss_net_profit)
                from store_sales
                where ss_quantity between 1 and 20) end   bucket1,
    case
        when (select count(*)
                from store_sales
                where ss_quantity between 21 and 40) > 588
            then (select avg(ss_ext_discount_amt)
                    from store_sales
                    where ss_quantity between 21 and 40)
        else (select avg(ss_net_profit)
                from store_sales
                where ss_quantity between 21 and 40) end  bucket2,
    case
        when (select count(*)
                from store_sales
                where ss_quantity between 41 and 60) > 2456
            then (select avg(ss_ext_discount_amt)
                    from store_sales
                    where ss_quantity between 41 and 60)
        else (select avg(ss_net_profit)
                from store_sales
                where ss_quantity between 41 and 60) end  bucket3,
    case
        when (select count(*)
                from store_sales
                where ss_quantity between 61 and 80) > 21645
            then (select avg(ss_ext_discount_amt)
                    from store_sales
                    where ss_quantity between 61 and 80)
        else (select avg(ss_net_profit)
                from store_sales
                where ss_quantity between 61 and 80) end  bucket4,
    case
        when (select count(*)
                from store_sales
                where ss_quantity between 81 and 100) > 20553
            then (select avg(ss_ext_discount_amt)
                    from store_sales
                    where ss_quantity between 81 and 100)
        else (select avg(ss_net_profit)
                from store_sales
                where ss_quantity between 81 and 100) end bucket5
from reason
where r_reason_sk = 1
;

-- end query 49 in stream 0 using template query9.tpl
-- start query 50 in stream 0 using template query31.tpl
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 = 1999
and ss1.ca_county = ss2.ca_county
and ss2.d_qoy = 2
and ss2.d_year = 1999
and ss2.ca_county = ss3.ca_county
and ss3.d_qoy = 3
and ss3.d_year = 1999
and ss1.ca_county = ws1.ca_county
and ws1.d_qoy = 1
and ws1.d_year = 1999
and ws1.ca_county = ws2.ca_county
and ws2.d_qoy = 2
and ws2.d_year = 1999
and ws1.ca_county = ws3.ca_county
and ws3.d_qoy = 3
and ws3.d_year = 1999
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.ca_county;

-- end query 50 in stream 0 using template query31.tpl
-- start query 51 in stream 0 using template query11.tpl
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 = 1999
and t_s_secyear.dyear = 1999 + 1
and t_w_firstyear.dyear = 1999
and t_w_secyear.dyear = 1999 + 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;

-- end query 51 in stream 0 using template query11.tpl
-- start query 52 in stream 0 using template query93.tpl
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 get it on time') t
group by ss_customer_sk
order by sumsales, ss_customer_sk LIMIT 100;

-- end query 52 in stream 0 using template query93.tpl
-- start query 53 in stream 0 using template query29.tpl
select i_item_id
    , i_item_desc
    , s_store_id
    , s_store_name
    , stddev_samp(ss_quantity)        as store_sales_quantity
    , stddev_samp(sr_return_quantity) as store_returns_quantity
    , stddev_samp(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;

-- end query 53 in stream 0 using template query29.tpl
-- start query 54 in stream 0 using template query38.tpl
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 1190 and 1190 + 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 1190 and 1190 + 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 1190 and 1190 + 11) hot_cust LIMIT 100;

-- end query 54 in stream 0 using template query38.tpl
-- start query 55 in stream 0 using template query22.tpl
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 1201 and 1201 + 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;

-- end query 55 in stream 0 using template query22.tpl
-- start query 56 in stream 0 using template query89.tpl
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 (2001)
        and ((i_category in ('Children', 'Jewelry', 'Home') and
            i_class in ('infants', 'birdal', 'flatware')
                )
        or (i_category in ('Electronics', 'Music', 'Books') and
            i_class in ('audio', 'classical', 'science')
                ))
    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;

-- end query 56 in stream 0 using template query89.tpl
-- start query 57 in stream 0 using template query15.tpl
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 = 2002
group by ca_zip
order by ca_zip LIMIT 100;

-- end query 57 in stream 0 using template query15.tpl
-- start query 58 in stream 0 using template query6.tpl
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 = 1998
        and d_moy = 3)
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;

-- end query 58 in stream 0 using template query6.tpl
-- start query 59 in stream 0 using template query52.tpl
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 = 11
and dt.d_year = 2000
group by dt.d_year
    , item.i_brand
    , item.i_brand_id
order by dt.d_year
    , ext_price desc
    , brand_id LIMIT 100;

-- end query 59 in stream 0 using template query52.tpl
-- start query 60 in stream 0 using template query50.tpl
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 = 2002
and d2.d_moy = 8
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;

-- end query 60 in stream 0 using template query50.tpl
-- start query 61 in stream 0 using template query42.tpl
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 = 11
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;

-- end query 61 in stream 0 using template query42.tpl
-- start query 62 in stream 0 using template query41.tpl
select distinct(i_product_name)
from item i1
where i_manufact_id between 668 and 668 + 40
and (select count(*) as item_cnt
    from item
    where (i_manufact = i1.i_manufact and
            ((i_category = 'Women' and
                (i_color = 'cream' or i_color = 'ghost') and
                (i_units = 'Ton' or i_units = 'Gross') and
                (i_size = 'economy' or i_size = 'small')
                ) or
            (i_category = 'Women' and
                (i_color = 'midnight' or i_color = 'burlywood') and
                (i_units = 'Tsp' or i_units = 'Bundle') and
                (i_size = 'medium' or i_size = 'extra large')
                ) or
            (i_category = 'Men' and
                (i_color = 'lavender' or i_color = 'azure') and
                (i_units = 'Each' or i_units = 'Lb') and
                (i_size = 'large' or i_size = 'N/A')
                ) or
            (i_category = 'Men' and
                (i_color = 'chocolate' or i_color = 'steel') and
                (i_units = 'N/A' or i_units = 'Dozen') and
                (i_size = 'economy' or i_size = 'small')
                )))
        or (i_manufact = i1.i_manufact and
            ((i_category = 'Women' and
                (i_color = 'floral' or i_color = 'royal') and
                (i_units = 'Unknown' or i_units = 'Tbl') and
                (i_size = 'economy' or i_size = 'small')
                ) or
            (i_category = 'Women' and
                (i_color = 'navy' or i_color = 'forest') and
                (i_units = 'Bunch' or i_units = 'Dram') and
                (i_size = 'medium' or i_size = 'extra large')
                ) or
            (i_category = 'Men' and
                (i_color = 'cyan' or i_color = 'indian') and
                (i_units = 'Carton' or i_units = 'Cup') and
                (i_size = 'large' or i_size = 'N/A')
                ) or
            (i_category = 'Men' and
                (i_color = 'coral' or i_color = 'pale') and
                (i_units = 'Pallet' or i_units = 'Gram') and
                (i_size = 'economy' or i_size = 'small')
                )))) > 0
order by i_product_name LIMIT 100;

-- end query 62 in stream 0 using template query41.tpl
-- start query 63 in stream 0 using template query8.tpl
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 (
                                        '19100', '41548', '51640', '49699', '88329', '55986',
                                        '85119', '19510', '61020', '95452', '26235',
                                        '51102', '16733', '42819', '27823', '90192',
                                        '31905', '28865', '62197', '23750', '81398',
                                        '95288', '45114', '82060', '12313', '25218',
                                        '64386', '46400', '77230', '69271', '43672',
                                        '36521', '34217', '13017', '27936', '42766',
                                        '59233', '26060', '27477', '39981', '93402',
                                        '74270', '13932', '51731', '71642', '17710',
                                        '85156', '21679', '70840', '67191', '39214',
                                        '35273', '27293', '17128', '15458', '31615',
                                        '60706', '67657', '54092', '32775', '14683',
                                        '32206', '62543', '43053', '11297', '58216',
                                        '49410', '14710', '24501', '79057', '77038',
                                        '91286', '32334', '46298', '18326', '67213',
                                        '65382', '40315', '56115', '80162', '55956',
                                        '81583', '73588', '32513', '62880', '12201',
                                        '11592', '17014', '83832', '61796', '57872',
                                        '78829', '69912', '48524', '22016', '26905',
                                        '48511', '92168', '63051', '25748', '89786',
                                        '98827', '86404', '53029', '37524', '14039',
                                        '50078', '34487', '70142', '18697', '40129',
                                        '60642', '42810', '62667', '57183', '46414',
                                        '58463', '71211', '46364', '34851', '54884',
                                        '25382', '25239', '74126', '21568', '84204',
                                        '13607', '82518', '32982', '36953', '86001',
                                        '79278', '21745', '64444', '35199', '83181',
                                        '73255', '86177', '98043', '90392', '13882',
                                        '47084', '17859', '89526', '42072', '20233',
                                        '52745', '75000', '22044', '77013', '24182',
                                        '52554', '56138', '43440', '86100', '48791',
                                        '21883', '17096', '15965', '31196', '74903',
                                        '19810', '35763', '92020', '55176', '54433',
                                        '68063', '71919', '44384', '16612', '32109',
                                        '28207', '14762', '89933', '10930', '27616',
                                        '56809', '14244', '22733', '33177', '29784',
                                        '74968', '37887', '11299', '34692', '85843',
                                        '83663', '95421', '19323', '17406', '69264',
                                        '28341', '50150', '79121', '73974', '92917',
                                        '21229', '32254', '97408', '46011', '37169',
                                        '18146', '27296', '62927', '68812', '47734',
                                        '86572', '12620', '80252', '50173', '27261',
                                        '29534', '23488', '42184', '23695', '45868',
                                        '12910', '23429', '29052', '63228', '30731',
                                        '15747', '25827', '22332', '62349', '56661',
                                        '44652', '51862', '57007', '22773', '40361',
                                        '65238', '19327', '17282', '44708', '35484',
                                        '34064', '11148', '92729', '22995', '18833',
                                        '77528', '48917', '17256', '93166', '68576',
                                        '71096', '56499', '35096', '80551', '82424',
                                        '17700', '32748', '78969', '46820', '57725',
                                        '46179', '54677', '98097', '62869', '83959',
                                        '66728', '19716', '48326', '27420', '53458',
                                        '69056', '84216', '36688', '63957', '41469',
                                        '66843', '18024', '81950', '21911', '58387',
                                        '58103', '19813', '34581', '55347', '17171',
                                        '35914', '75043', '75088', '80541', '26802',
                                        '28849', '22356', '57721', '77084', '46385',
                                        '59255', '29308', '65885', '70673', '13306',
                                        '68788', '87335', '40987', '31654', '67560',
                                        '92309', '78116', '65961', '45018', '16548',
                                        '67092', '21818', '33716', '49449', '86150',
                                        '12156', '27574', '43201', '50977', '52839',
                                        '33234', '86611', '71494', '17823', '57172',
                                        '59869', '34086', '51052', '11320', '39717',
                                        '79604', '24672', '70555', '38378', '91135',
                                        '15567', '21606', '74994', '77168', '38607',
                                        '27384', '68328', '88944', '40203', '37893',
                                        '42726', '83549', '48739', '55652', '27543',
                                        '23109', '98908', '28831', '45011', '47525',
                                        '43870', '79404', '35780', '42136', '49317',
                                        '14574', '99586', '21107', '14302', '83882',
                                        '81272', '92552', '14916', '87533', '86518',
                                        '17862', '30741', '96288', '57886', '30304',
                                        '24201', '79457', '36728', '49833', '35182',
                                        '20108', '39858', '10804', '47042', '20439',
                                        '54708', '59027', '82499', '75311', '26548',
                                        '53406', '92060', '41152', '60446', '33129',
                                        '43979', '16903', '60319', '35550', '33887',
                                        '25463', '40343', '20726', '44429')
            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 = 2000
and (substr(s_zip, 1, 2) = substr(V1.ca_zip, 1, 2))
group by s_store_name
order by s_store_name LIMIT 100;

-- end query 63 in stream 0 using template query8.tpl
-- start query 64 in stream 0 using template query12.tpl
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', 'Books', 'Women')
and ws_sold_date_sk = d_date_sk
and d_date between cast('2002-03-22' as date)
    and (cast('2002-03-22' as date) + 30 days)
group by i_item_id
    , i_item_desc
    , i_category
    , i_class
    , i_current_price
order by i_category
    , i_class
    , i_item_id
    , i_item_desc
    , revenueratio LIMIT 100;

-- end query 64 in stream 0 using template query12.tpl
-- start query 65 in stream 0 using template query20.tpl
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 ('Children', 'Sports', 'Music')
and cs_sold_date_sk = d_date_sk
and d_date between cast('2002-04-01' as date)
    and (cast('2002-04-01' as date) + 30 days)
group by i_item_id
    , i_item_desc
    , i_category
    , i_class
    , i_current_price
order by i_category
    , i_class
    , i_item_id
    , i_item_desc
    , revenueratio LIMIT 100;

-- end query 65 in stream 0 using template query20.tpl
-- start query 66 in stream 0 using template query88.tpl
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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 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 = 2 and household_demographics.hd_vehicle_count <= 2 + 2) or
            (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count <= 1 + 2) or
            (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count <= 4 + 2))
        and store.s_store_name = 'ese') s8
;

-- end query 66 in stream 0 using template query88.tpl
-- start query 67 in stream 0 using template query82.tpl
select i_item_id
    , i_item_desc
    , i_current_price
from item,
    inventory,
    date_dim,
    store_sales
where i_current_price between 69 and 69 + 30
and inv_item_sk = i_item_sk
and d_date_sk = inv_date_sk
and d_date between cast('1998-06-06' as date) and (cast('1998-06-06' as date) + 60 days)
and i_manufact_id in (105, 513, 180, 137)
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;

-- end query 67 in stream 0 using template query82.tpl
-- start query 68 in stream 0 using template query23.tpl
with frequent_ss_items as
        (select substr(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, count(*) cnt
        from store_sales
            , date_dim
            , item
        where ss_sold_date_sk = d_date_sk
            and ss_item_sk = i_item_sk
            and d_year in (2000, 2000 + 1, 2000 + 2, 2000 + 3)
        group by substr(i_item_desc, 1, 30), i_item_sk, d_date
        having count(*) > 4),
    max_store_sales as
        (select max(csales) tpcds_cmax
        from (select c_customer_sk, sum(ss_quantity * ss_sales_price) csales
                from store_sales
                , customer
                , date_dim
                where ss_customer_sk = c_customer_sk
                and ss_sold_date_sk = d_date_sk
                and d_year in (2000, 2000 + 1, 2000 + 2, 2000 + 3)
                group by c_customer_sk)),
    best_ss_customer as
        (select c_customer_sk, sum(ss_quantity * ss_sales_price) ssales
        from store_sales
            , customer
        where ss_customer_sk = c_customer_sk
        group by c_customer_sk
        having sum(ss_quantity * ss_sales_price) > (95 / 100.0) * (select *
                                                                    from max_store_sales))
select sum(sales)
from (select cs_quantity * cs_list_price sales
    from catalog_sales
        , date_dim
    where d_year = 2000
        and d_moy = 3
        and cs_sold_date_sk = d_date_sk
        and cs_item_sk in (select item_sk from frequent_ss_items)
        and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
    union all
    select ws_quantity * ws_list_price sales
    from web_sales
        , date_dim
    where d_year = 2000
        and d_moy = 3
        and ws_sold_date_sk = d_date_sk
        and ws_item_sk in (select item_sk from frequent_ss_items)
        and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) LIMIT 100;
with frequent_ss_items as
        (select substr(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, count(*) cnt
        from store_sales
            , date_dim
            , item
        where ss_sold_date_sk = d_date_sk
            and ss_item_sk = i_item_sk
            and d_year in (2000, 2000 + 1, 2000 + 2, 2000 + 3)
        group by substr(i_item_desc, 1, 30), i_item_sk, d_date
        having count(*) > 4),
    max_store_sales as
        (select max(csales) tpcds_cmax
        from (select c_customer_sk, sum(ss_quantity * ss_sales_price) csales
                from store_sales
                , customer
                , date_dim
                where ss_customer_sk = c_customer_sk
                and ss_sold_date_sk = d_date_sk
                and d_year in (2000, 2000 + 1, 2000 + 2, 2000 + 3)
                group by c_customer_sk)),
    best_ss_customer as
        (select c_customer_sk, sum(ss_quantity * ss_sales_price) ssales
        from store_sales
            , customer
        where ss_customer_sk = c_customer_sk
        group by c_customer_sk
        having sum(ss_quantity * ss_sales_price) > (95 / 100.0) * (select *
                                                                    from max_store_sales))
select c_last_name, c_first_name, sales
from (select c_last_name, c_first_name, sum(cs_quantity * cs_list_price) sales
    from catalog_sales
        , customer
        , date_dim
    where d_year = 2000
        and d_moy = 3
        and cs_sold_date_sk = d_date_sk
        and cs_item_sk in (select item_sk from frequent_ss_items)
        and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
        and cs_bill_customer_sk = c_customer_sk
    group by c_last_name, c_first_name
    union all
    select c_last_name, c_first_name, sum(ws_quantity * ws_list_price) sales
    from web_sales
        , customer
        , date_dim
    where d_year = 2000
        and d_moy = 3
        and ws_sold_date_sk = d_date_sk
        and ws_item_sk in (select item_sk from frequent_ss_items)
        and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)
        and ws_bill_customer_sk = c_customer_sk
    group by c_last_name, c_first_name)
order by c_last_name, c_first_name, sales LIMIT 100;

-- end query 68 in stream 0 using template query23.tpl
-- start query 69 in stream 0 using template query14.tpl
with cross_items as
        (select i_item_sk ss_item_sk
        from item,
            (select iss.i_brand_id    brand_id
                    , iss.i_class_id    class_id
                    , iss.i_category_id category_id
                from store_sales
                , item iss
                , date_dim d1
                where ss_item_sk = iss.i_item_sk
                and ss_sold_date_sk = d1.d_date_sk
                and d1.d_year between 1999 AND 1999 + 2
                intersect
                select ics.i_brand_id
                    , ics.i_class_id
                    , ics.i_category_id
                from catalog_sales
                , item ics
                , date_dim d2
                where cs_item_sk = ics.i_item_sk
                and cs_sold_date_sk = d2.d_date_sk
                and d2.d_year between 1999 AND 1999 + 2
                intersect
                select iws.i_brand_id
                    , iws.i_class_id
                    , iws.i_category_id
                from web_sales
                , item iws
                , date_dim d3
                where ws_item_sk = iws.i_item_sk
                and ws_sold_date_sk = d3.d_date_sk
                and d3.d_year between 1999 AND 1999 + 2)
        where i_brand_id = brand_id
            and i_class_id = class_id
            and i_category_id = category_id),
    avg_sales as
        (select avg(quantity * list_price) average_sales
        from (select ss_quantity   quantity
                    , ss_list_price list_price
                from store_sales
                , date_dim
                where ss_sold_date_sk = d_date_sk
                and d_year between 1999 and 1999 + 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 1999 and 1999 + 2
                union all
                select ws_quantity   quantity
                    , ws_list_price list_price
                from web_sales
                , date_dim
                where ws_sold_date_sk = d_date_sk
                and d_year between 1999 and 1999 + 2) x)
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 = 1999 + 2
        and d_moy = 11
    group by i_brand_id, i_class_id, i_category_id
    having sum(ss_quantity * ss_list_price) > (select average_sales from avg_sales)
    union all
    select 'catalog'                        channel,
            i_brand_id,
            i_class_id,
            i_category_id,
            sum(cs_quantity * cs_list_price) sales,
            count(*)                         number_sales
    from catalog_sales
        , item
        , date_dim
    where cs_item_sk in (select ss_item_sk from cross_items)
        and cs_item_sk = i_item_sk
        and cs_sold_date_sk = d_date_sk
        and d_year = 1999 + 2
        and d_moy = 11
    group by i_brand_id, i_class_id, i_category_id
    having sum(cs_quantity * cs_list_price) > (select average_sales from avg_sales)
    union all
    select 'web'                            channel,
            i_brand_id,
            i_class_id,
            i_category_id,
            sum(ws_quantity * ws_list_price) sales,
            count(*)                         number_sales
    from web_sales
        , item
        , date_dim
    where ws_item_sk in (select ss_item_sk from cross_items)
        and ws_item_sk = i_item_sk
        and ws_sold_date_sk = d_date_sk
        and d_year = 1999 + 2
        and d_moy = 11
    group by i_brand_id, i_class_id, i_category_id
    having sum(ws_quantity * ws_list_price) > (select average_sales from avg_sales)) y
group by 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;
with cross_items as
        (select i_item_sk ss_item_sk
        from item,
            (select iss.i_brand_id    brand_id
                    , iss.i_class_id    class_id
                    , iss.i_category_id category_id
                from store_sales
                , item iss
                , date_dim d1
                where ss_item_sk = iss.i_item_sk
                and ss_sold_date_sk = d1.d_date_sk
                and d1.d_year between 1999 AND 1999 + 2
                intersect
                select ics.i_brand_id
                    , ics.i_class_id
                    , ics.i_category_id
                from catalog_sales
                , item ics
                , date_dim d2
                where cs_item_sk = ics.i_item_sk
                and cs_sold_date_sk = d2.d_date_sk
                and d2.d_year between 1999 AND 1999 + 2
                intersect
                select iws.i_brand_id
                    , iws.i_class_id
                    , iws.i_category_id
                from web_sales
                , item iws
                , date_dim d3
                where ws_item_sk = iws.i_item_sk
                and ws_sold_date_sk = d3.d_date_sk
                and d3.d_year between 1999 AND 1999 + 2) x
        where i_brand_id = brand_id
            and i_class_id = class_id
            and i_category_id = category_id),
    avg_sales as
        (select avg(quantity * list_price) average_sales
        from (select ss_quantity   quantity
                    , ss_list_price list_price
                from store_sales
                , date_dim
                where ss_sold_date_sk = d_date_sk
                and d_year between 1999 and 1999 + 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 1999 and 1999 + 2
                union all
                select ws_quantity   quantity
                    , ws_list_price list_price
                from web_sales
                , date_dim
                where ws_sold_date_sk = d_date_sk
                and d_year between 1999 and 1999 + 2) x)
select this_year.channel       ty_channel
    , this_year.i_brand_id    ty_brand
    , this_year.i_class_id    ty_class
    , this_year.i_category_id ty_category
    , this_year.sales         ty_sales
    , this_year.number_sales  ty_number_sales
    , last_year.channel       ly_channel
    , last_year.i_brand_id    ly_brand
    , last_year.i_class_id    ly_class
    , last_year.i_category_id ly_category
    , last_year.sales         ly_sales
    , last_year.number_sales  ly_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_week_seq = (select d_week_seq
                        from date_dim
                        where d_year = 1999 + 1
                            and d_moy = 12
                            and d_dom = 14)
    group by i_brand_id, i_class_id, i_category_id
    having sum(ss_quantity * ss_list_price) > (select average_sales from avg_sales)) this_year,
    (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_week_seq = (select d_week_seq
                        from date_dim
                        where d_year = 1999
                            and d_moy = 12
                            and d_dom = 14)
    group by i_brand_id, i_class_id, i_category_id
    having sum(ss_quantity * ss_list_price) > (select average_sales from avg_sales)) last_year
where this_year.i_brand_id = last_year.i_brand_id
and this_year.i_class_id = last_year.i_class_id
and this_year.i_category_id = last_year.i_category_id
order by this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id LIMIT 100;

-- end query 69 in stream 0 using template query14.tpl
-- start query 70 in stream 0 using template query57.tpl
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, psum LIMIT 100;

-- end query 70 in stream 0 using template query57.tpl
-- start query 71 in stream 0 using template query65.tpl
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 1186 and 1186 + 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 1186 and 1186 + 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;

-- end query 71 in stream 0 using template query65.tpl
-- start query 72 in stream 0 using template query71.tpl
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 = 11
        and d_year = 2001
    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 = 11
        and d_year = 2001
    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 = 11
        and d_year = 2001) 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
;

-- end query 72 in stream 0 using template query71.tpl
-- start query 73 in stream 0 using template query34.tpl
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 = '501-1000' or
            household_demographics.hd_buy_potential = 'Unknown')
        and household_demographics.hd_vehicle_count > 0
        and (case
                when household_demographics.hd_vehicle_count > 0
                    then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count
                else null
        end) > 1.2
        and date_dim.d_year in (2000, 2000 + 1, 2000 + 2)
        and store.s_county in ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County',
                            'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
    group by ss_ticket_number, ss_customer_sk) dn,
    customer
where ss_customer_sk = c_customer_sk
and cnt between 15 and 20
order by c_last_name, c_first_name, c_salutation, c_preferred_cust_flag desc, ss_ticket_number;

-- end query 73 in stream 0 using template query34.tpl
-- start query 74 in stream 0 using template query48.tpl
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 = 2001
and (
        (
                    cd_demo_sk = ss_cdemo_sk
                and
                    cd_marital_status = 'W'
                and
                    cd_education_status = '2 yr Degree'
                and
                    ss_sales_price between 100.00 and 150.00
            )
        or
        (
                    cd_demo_sk = ss_cdemo_sk
                and
                    cd_marital_status = 'S'
                and
                    cd_education_status = 'Advanced Degree'
                and
                    ss_sales_price between 50.00 and 100.00
            )
        or
        (
                    cd_demo_sk = ss_cdemo_sk
                and
                    cd_marital_status = 'D'
                and
                    cd_education_status = 'Primary'
                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 ('IL', 'KY', 'OR')
                and ss_net_profit between 0 and 2000
            )
        or
        (ss_addr_sk = ca_address_sk
            and
        ca_country = 'United States'
            and
        ca_state in ('VA', 'FL', 'AL')
            and ss_net_profit between 150 and 3000
            )
        or
        (ss_addr_sk = ca_address_sk
            and
        ca_country = 'United States'
            and
        ca_state in ('OK', 'IA', 'TX')
            and ss_net_profit between 50 and 25000
            )
    )
;

-- end query 74 in stream 0 using template query48.tpl
-- start query 75 in stream 0 using template query30.tpl
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 = 2000
            and wr_returning_addr_sk = ca_address_sk
        group by wr_returning_customer_sk
                , ca_state)
select c_customer_id
    , c_salutation
    , c_first_name
    , c_last_name
    , c_preferred_cust_flag
    , c_birth_day
    , c_birth_month
    , c_birth_year
    , c_birth_country
    , c_login
    , c_email_address
    , c_last_review_date_sk
    , ctr_total_return
from customer_total_return ctr1
, customer_address
, customer
where ctr1.ctr_total_return > (select avg(ctr_total_return) * 1.2
                            from customer_total_return ctr2
                            where ctr1.ctr_state = ctr2.ctr_state)
and ca_address_sk = c_current_addr_sk
and ca_state = 'KS'
and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id, c_salutation, c_first_name, c_last_name, c_preferred_cust_flag
    , c_birth_day, c_birth_month, c_birth_year, c_birth_country, c_login, c_email_address
    , c_last_review_date_sk, ctr_total_return LIMIT 100;

-- end query 75 in stream 0 using template query30.tpl
-- start query 76 in stream 0 using template query74.tpl
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
, stddev_samp(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
    ,stddev_samp(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 3, 2, 1 LIMIT 100;

-- end query 76 in stream 0 using template query74.tpl
-- start query 77 in stream 0 using template query87.tpl
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 1189 and 1189 + 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 1189 and 1189 + 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 1189 and 1189 + 11)) cool_cust
;

-- end query 77 in stream 0 using template query87.tpl
-- start query 78 in stream 0 using template query77.tpl
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('2001-08-11' as date)
            and (cast('2001-08-11' as date) + 30 days)
            and ss_store_sk = s_store_sk
        group by s_store_sk)
        ,
    sr as
        (select s_store_sk,
                sum(sr_return_amt) as returns,
                sum(sr_net_loss)   as profit_loss
        from store_returns,
            date_dim,
            store
        where sr_returned_date_sk = d_date_sk
            and d_date between cast('2001-08-11' as date)
            and (cast('2001-08-11' as date) + 30 days)
            and sr_store_sk = s_store_sk
        group by s_store_sk),
    cs as
        (select cs_call_center_sk,
                sum(cs_ext_sales_price) as sales,
                sum(cs_net_profit)      as profit
        from catalog_sales,
            date_dim
        where cs_sold_date_sk = d_date_sk
            and d_date between cast('2001-08-11' as date)
            and (cast('2001-08-11' as date) + 30 days)
        group by cs_call_center_sk),
    cr as
        (select cr_call_center_sk,
                sum(cr_return_amount) as returns,
                sum(cr_net_loss)      as profit_loss
        from catalog_returns,
            date_dim
        where cr_returned_date_sk = d_date_sk
            and d_date between cast('2001-08-11' as date)
            and (cast('2001-08-11' as date) + 30 days)
        group by cr_call_center_sk),
    ws as
        (select wp_web_page_sk,
                sum(ws_ext_sales_price) as sales,
                sum(ws_net_profit)      as profit
        from web_sales,
            date_dim,
            web_page
        where ws_sold_date_sk = d_date_sk
            and d_date between cast('2001-08-11' as date)
            and (cast('2001-08-11' as date) + 30 days)
            and ws_web_page_sk = wp_web_page_sk
        group by wp_web_page_sk),
    wr as
        (select wp_web_page_sk,
                sum(wr_return_amt) as returns,
                sum(wr_net_loss)   as profit_loss
        from web_returns,
            date_dim,
            web_page
        where wr_returned_date_sk = d_date_sk
            and d_date between cast('2001-08-11' as date)
            and (cast('2001-08-11' as date) + 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;

-- end query 78 in stream 0 using template query77.tpl
-- start query 79 in stream 0 using template query73.tpl
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 = '1001-5000' 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 ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
    group by ss_ticket_number, ss_customer_sk) dj,
    customer
where ss_customer_sk = c_customer_sk
and cnt between 1 and 5
order by cnt desc, c_last_name asc;

-- end query 79 in stream 0 using template query73.tpl
-- start query 80 in stream 0 using template query84.tpl
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 = 'White Oak'
and c_current_addr_sk = ca_address_sk
and ib_lower_bound >= 45626
and ib_upper_bound <= 45626 + 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;

-- end query 80 in stream 0 using template query84.tpl
-- start query 81 in stream 0 using template query54.tpl
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 = 'Men'
                        and i_class = 'shirts'
                        and c_customer_sk = cs_or_ws_sales.customer_sk
                        and d_moy = 4
                        and d_year = 1998)
, 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 = 1998
                                                and d_moy = 4)
                        and (select distinct d_month_seq + 3
                            from date_dim
                            where d_year = 1998
                            and d_moy = 4)
                    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;

-- end query 81 in stream 0 using template query54.tpl
-- start query 82 in stream 0 using template query55.tpl
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 = 20
and d_moy = 12
and d_year = 1998
group by i_brand, i_brand_id
order by ext_price desc, i_brand_id LIMIT 100;

-- end query 82 in stream 0 using template query55.tpl
-- start query 83 in stream 0 using template query56.tpl
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 ('powder', 'goldenrod', 'bisque'))
            and ss_item_sk = i_item_sk
            and ss_sold_date_sk = d_date_sk
            and d_year = 1998
            and d_moy = 5
            and ss_addr_sk = ca_address_sk
            and ca_gmt_offset = -5
            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 ('powder', 'goldenrod', 'bisque'))
            and cs_item_sk = i_item_sk
            and cs_sold_date_sk = d_date_sk
            and d_year = 1998
            and d_moy = 5
            and cs_bill_addr_sk = ca_address_sk
            and ca_gmt_offset = -5
            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 ('powder', 'goldenrod', 'bisque'))
            and ws_item_sk = i_item_sk
            and ws_sold_date_sk = d_date_sk
            and d_year = 1998
            and d_moy = 5
            and ws_bill_addr_sk = ca_address_sk
            and ca_gmt_offset = -5
            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;

-- end query 83 in stream 0 using template query56.tpl
-- start query 84 in stream 0 using template query2.tpl
with wscs as
        (select sold_date_sk
            , sales_price
        from (select ws_sold_date_sk    sold_date_sk
                    , ws_ext_sales_price sales_price
                from web_sales
                union all
                select cs_sold_date_sk    sold_date_sk
                    , cs_ext_sales_price sales_price
                from catalog_sales)),
    wswscs as
        (select d_week_seq,
                sum(case when (d_day_name = 'Sunday') then sales_price else null end)    sun_sales,
                sum(case when (d_day_name = 'Monday') then sales_price else null end)    mon_sales,
                sum(case when (d_day_name = 'Tuesday') then sales_price else null end)   tue_sales,
                sum(case when (d_day_name = 'Wednesday') then sales_price else null end) wed_sales,
                sum(case when (d_day_name = 'Thursday') then sales_price else null end)  thu_sales,
                sum(case when (d_day_name = 'Friday') then sales_price else null end)    fri_sales,
                sum(case when (d_day_name = 'Saturday') then sales_price else null end)  sat_sales
        from wscs
            , date_dim
        where d_date_sk = sold_date_sk
        group by d_week_seq)
select d_week_seq1
    , round(sun_sales1 / sun_sales2, 2)
    , round(mon_sales1 / mon_sales2, 2)
    , round(tue_sales1 / tue_sales2, 2)
    , round(wed_sales1 / wed_sales2, 2)
    , round(thu_sales1 / thu_sales2, 2)
    , round(fri_sales1 / fri_sales2, 2)
    , round(sat_sales1 / sat_sales2, 2)
from (select wswscs.d_week_seq d_week_seq1
        , sun_sales         sun_sales1
        , mon_sales         mon_sales1
        , tue_sales         tue_sales1
        , wed_sales         wed_sales1
        , thu_sales         thu_sales1
        , fri_sales         fri_sales1
        , sat_sales         sat_sales1
    from wswscs,
        date_dim
    where date_dim.d_week_seq = wswscs.d_week_seq
        and d_year = 2000) 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 = 2000 + 1) z
where d_week_seq1 = d_week_seq2 - 53
order by d_week_seq1;

-- end query 84 in stream 0 using template query2.tpl
-- start query 85 in stream 0 using template query26.tpl
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 = 'M'
and cd_education_status = '4 yr Degree'
and (p_channel_email = 'N' or p_channel_event = 'N')
and d_year = 2000
group by i_item_id
order by i_item_id LIMIT 100;

-- end query 85 in stream 0 using template query26.tpl
-- start query 86 in stream 0 using template query40.tpl
select w_state
    , i_item_id
    , sum(case
            when (cast(d_date as date) < cast('2002-05-18' 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('2002-05-18' 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('2002-05-18' as date) - 30 days)
    and (cast('2002-05-18' as date) + 30 days)
group by w_state, i_item_id
order by w_state, i_item_id LIMIT 100;

-- end query 86 in stream 0 using template query40.tpl
-- start query 87 in stream 0 using template query72.tpl
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 = '501-1000'
and d1.d_year = 1999
and cd_marital_status = 'S'
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;

-- end query 87 in stream 0 using template query72.tpl
-- start query 88 in stream 0 using template query53.tpl
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
            (1197, 1197 + 1, 1197 + 2, 1197 + 3, 1197 + 4, 1197 + 5, 1197 + 6, 1197 + 7, 1197 + 8, 1197 + 9, 1197 + 10,
            1197 + 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;

-- end query 88 in stream 0 using template query53.tpl
-- start query 89 in stream 0 using template query79.tpl
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 = 0 or household_demographics.hd_vehicle_count > 4)
        and date_dim.d_dow = 1
        and date_dim.d_year in (1999, 1999 + 1, 1999 + 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;

-- end query 89 in stream 0 using template query79.tpl
-- start query 90 in stream 0 using template query18.tpl
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 = 'Primary'
and c_current_cdemo_sk = cd2.cd_demo_sk
and c_current_addr_sk = ca_address_sk
and c_birth_month in (1, 2, 9, 5, 11, 3)
and d_year = 1998
and ca_state in ('MS', 'NE', 'IA', 'MI', 'GA', 'NY', 'CO')
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;

-- end query 90 in stream 0 using template query18.tpl
-- start query 91 in stream 0 using template query13.tpl
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 = 'U'
    and cd_education_status = '4 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 = 'Unknown'
        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 = 'D'
        and cd_education_status = '2 yr 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', 'MI', '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 ('NC', 'NY', 'TX')
        and ss_net_profit between 150 and 300
        ) or
    (ss_addr_sk = ca_address_sk
        and ca_country = 'United States'
        and ca_state in ('CA', 'NE', 'TN')
        and ss_net_profit between 50 and 250
        ))
;

-- end query 91 in stream 0 using template query13.tpl
-- start query 92 in stream 0 using template query24.tpl
with ssales as
        (select c_last_name
            , c_first_name
            , s_store_name
            , ca_state
            , s_state
            , i_color
            , i_current_price
            , i_manager_id
            , i_units
            , i_size
            , sum(ss_net_profit) 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 = 10
        group by c_last_name
                , c_first_name
                , s_store_name
                , ca_state
                , s_state
                , i_color
                , i_current_price
                , i_manager_id
                , i_units
                , i_size)
select c_last_name
    , c_first_name
    , s_store_name
    , sum(netpaid) paid
from ssales
where i_color = '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
;
with ssales as
        (select c_last_name
            , c_first_name
            , s_store_name
            , ca_state
            , s_state
            , i_color
            , i_current_price
            , i_manager_id
            , i_units
            , i_size
            , sum(ss_net_profit) 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 = 10
        group by c_last_name
                , c_first_name
                , s_store_name
                , ca_state
                , s_state
                , i_color
                , i_current_price
                , i_manager_id
                , i_units
                , i_size)
select c_last_name
    , c_first_name
    , s_store_name
    , sum(netpaid) paid
from ssales
where i_color = 'green'
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
;

-- end query 92 in stream 0 using template query24.tpl
-- start query 93 in stream 0 using template query4.tpl
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;

-- end query 93 in stream 0 using template query4.tpl
-- start query 94 in stream 0 using template query99.tpl
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 1188 and 1188 + 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;

-- end query 94 in stream 0 using template query99.tpl
-- start query 95 in stream 0 using template query68.tpl
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 = 8 or
            household_demographics.hd_vehicle_count = 3)
        and date_dim.d_year in (2000, 2000 + 1, 2000 + 2)
        and store.s_city in ('Midway', 'Fairview')
    group by ss_ticket_number
            , ss_customer_sk
            , ss_addr_sk, ca_city) dn
, customer
, customer_address current_addr
where ss_customer_sk = c_customer_sk
and customer.c_current_addr_sk = current_addr.ca_address_sk
and current_addr.ca_city <> bought_city
order by c_last_name
    , ss_ticket_number LIMIT 100;

-- end query 95 in stream 0 using template query68.tpl
-- start query 96 in stream 0 using template query83.tpl
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 ('2000-04-29', '2000-09-09', '2000-11-02')))
            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 ('2000-04-29', '2000-09-09', '2000-11-02')))
            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 ('2000-04-29', '2000-09-09', '2000-11-02')))
            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;

-- end query 96 in stream 0 using template query83.tpl
-- start query 97 in stream 0 using template query61.tpl
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 = -6
        and i_category = 'Sports'
        and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
        and s_gmt_offset = -6
        and d_year = 2002
        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 = -6
        and i_category = 'Sports'
        and s_gmt_offset = -6
        and d_year = 2002
        and d_moy = 11) all_sales
order by promotions, total LIMIT 100;

-- end query 97 in stream 0 using template query61.tpl
-- start query 98 in stream 0 using template query5.tpl
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('2001-08-04' as date)
            and (cast('2001-08-04' as date) + 14 days)
            and store_sk = s_store_sk
        group by s_store_id)
        ,
    csr as
        (select cp_catalog_page_id,
                sum(sales_price) as sales,
                sum(profit)      as profit,
                sum(return_amt)  as returns,
                sum(net_loss)    as profit_loss
        from (select cs_catalog_page_sk       as page_sk,
                    cs_sold_date_sk          as date_sk,
                    cs_ext_sales_price       as sales_price,
                    cs_net_profit            as profit,
                    cast(0 as decimal(7, 2)) as return_amt,
                    cast(0 as decimal(7, 2)) as net_loss
                from catalog_sales
                union all
                select cr_catalog_page_sk       as page_sk,
                    cr_returned_date_sk      as date_sk,
                    cast(0 as decimal(7, 2)) as sales_price,
                    cast(0 as decimal(7, 2)) as profit,
                    cr_return_amount         as return_amt,
                    cr_net_loss              as net_loss
                from catalog_returns) salesreturns,
            date_dim,
            catalog_page
        where date_sk = d_date_sk
            and d_date between cast('2001-08-04' as date)
            and (cast('2001-08-04' as date) + 14 days)
            and page_sk = cp_catalog_page_sk
        group by cp_catalog_page_id)
        ,
    wsr as
        (select web_site_id,
                sum(sales_price) as sales,
                sum(profit)      as profit,
                sum(return_amt)  as returns,
                sum(net_loss)    as profit_loss
        from (select ws_web_site_sk           as wsr_web_site_sk,
                    ws_sold_date_sk          as date_sk,
                    ws_ext_sales_price       as sales_price,
                    ws_net_profit            as profit,
                    cast(0 as decimal(7, 2)) as return_amt,
                    cast(0 as decimal(7, 2)) as net_loss
                from web_sales
                union all
                select ws_web_site_sk           as wsr_web_site_sk,
                    wr_returned_date_sk      as date_sk,
                    cast(0 as decimal(7, 2)) as sales_price,
                    cast(0 as decimal(7, 2)) as profit,
                    wr_return_amt            as return_amt,
                    wr_net_loss              as net_loss
                from web_returns
                        left outer join web_sales on
                    (wr_item_sk = ws_item_sk
                        and wr_order_number = ws_order_number)) salesreturns,
            date_dim,
            web_site
        where date_sk = d_date_sk
            and d_date between cast('2001-08-04' as date)
            and (cast('2001-08-04' as date) + 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;

-- end query 98 in stream 0 using template query5.tpl
-- start query 99 in stream 0 using template query76.tpl
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_customer_sk'   col_name,
            d_year,
            d_qoy,
            i_category,
            ss_ext_sales_price ext_sales_price
    FROM store_sales,
        item,
        date_dim
    WHERE ss_customer_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_ship_hdemo_sk' col_name,
            d_year,
            d_qoy,
            i_category,
            ws_ext_sales_price ext_sales_price
    FROM web_sales,
        item,
        date_dim
    WHERE ws_ship_hdemo_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_bill_customer_sk' col_name,
            d_year,
            d_qoy,
            i_category,
            cs_ext_sales_price    ext_sales_price
    FROM catalog_sales,
        item,
        date_dim
    WHERE cs_bill_customer_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;

-- end query 99 in stream 0 using template query76.tpl

\timing off

2 安装数据库#

3 安装benchmarksql#

3 使用benchmarksql测试tpcc#

测试模型#

2 安装数据库

3 安装benchmarksql

3 使用benchmarksql测试tpcc

测试模型