TPC-DS (2012)
Similar to the Star Schema Benchmark (SSB), TPC-DS is based on TPC-H, but it took the opposite route, i.e. it expanded the number of joins needed by storing the data in a complex snowflake schema (24 instead of 8 tables). The data distribution is skewed (e.g. normal and Poisson distributions). It includes 99 reporting and ad-hoc queries with random substitutions.
References
- The Making of TPC-DS (Nambiar), 2006
Data Generation and Import
First, checkout the TPC-DS repository and compile the data generator:
git clone https://github.com/gregrahn/tpcds-kit.git
cd tpcds-kit/tools
make
Then, generate the data. Parameter -scale specifies the scale factor.
./dsdgen -scale 1
Now create tables in ClickHouse.
We stick as closely as possible to the rules of the TPC-DS specification. The abstract datatypes from the specification are mapped to ClickHouse's native datatypes as follows:
| TPC-DS Type | ClickHouse Type |
|---|---|
identifier | Int64, except *_date_sk and *_time_sk columns which use UInt32 |
integer | Int64 |
decimal(P,S) | Decimal(P,S) |
char(N) | FixedString(N) |
varchar(N) | String |
date | Date |
CREATE TABLE call_center(
cc_call_center_sk Int64 NOT NULL,
cc_call_center_id FixedString(16) NOT NULL,
cc_rec_start_date Date,
cc_rec_end_date Date,
cc_closed_date_sk UInt32,
cc_open_date_sk UInt32,
cc_name String,
cc_class String,
cc_employees Int64,
cc_sq_ft Int64,
cc_hours FixedString(20),
cc_manager String,
cc_mkt_id Int64,
cc_mkt_class FixedString(50),
cc_mkt_desc String,
cc_market_manager String,
cc_division Int64,
cc_division_name String,
cc_company Int64,
cc_company_name FixedString(50),
cc_street_number FixedString(10),
cc_street_name String,
cc_street_type FixedString(15),
cc_suite_number FixedString(10),
cc_city String,
cc_county String,
cc_state FixedString(2),
cc_zip FixedString(10),
cc_country String,
cc_gmt_offset Decimal(5,2),
cc_tax_percentage Decimal(5,2),
PRIMARY KEY (cc_call_center_sk)
);
CREATE TABLE catalog_page(
cp_catalog_page_sk Int64 NOT NULL,
cp_catalog_page_id FixedString(16) NOT NULL,
cp_start_date_sk UInt32,
cp_end_date_sk UInt32,
cp_department String,
cp_catalog_number Int64,
cp_catalog_page_number Int64,
cp_description String,
cp_type String,
PRIMARY KEY (cp_catalog_page_sk)
);
CREATE TABLE catalog_returns(
cr_returned_date_sk UInt32,
cr_returned_time_sk UInt32,
cr_item_sk Int64 NOT NULL,
cr_refunded_customer_sk Int64,
cr_refunded_cdemo_sk Int64,
cr_refunded_hdemo_sk Int64,
cr_refunded_addr_sk Int64,
cr_returning_customer_sk Int64,
cr_returning_cdemo_sk Int64,
cr_returning_hdemo_sk Int64,
cr_returning_addr_sk Int64,
cr_call_center_sk Int64,
cr_catalog_page_sk Int64,
cr_ship_mode_sk Int64,
cr_warehouse_sk Int64,
cr_reason_sk Int64,
cr_order_number Int64 NOT NULL,
cr_return_quantity Int64,
cr_return_amount Decimal(7,2),
cr_return_tax Decimal(7,2),
cr_return_amt_inc_tax Decimal(7,2),
cr_fee Decimal(7,2),
cr_return_ship_cost Decimal(7,2),
cr_refunded_cash Decimal(7,2),
cr_reversed_charge Decimal(7,2),
cr_store_credit Decimal(7,2),
cr_net_loss Decimal(7,2),
PRIMARY KEY (cr_item_sk, cr_order_number)
);
CREATE TABLE catalog_sales (
cs_sold_date_sk UInt32,
cs_sold_time_sk UInt32,
cs_ship_date_sk UInt32,
cs_bill_customer_sk Int64,
cs_bill_cdemo_sk Int64,
cs_bill_hdemo_sk Int64,
cs_bill_addr_sk Int64,
cs_ship_customer_sk Int64,
cs_ship_cdemo_sk Int64,
cs_ship_hdemo_sk Int64,
cs_ship_addr_sk Int64,
cs_call_center_sk Int64,
cs_catalog_page_sk Int64,
cs_ship_mode_sk Int64,
cs_warehouse_sk Int64,
cs_item_sk Int64 NOT NULL,
cs_promo_sk Int64,
cs_order_number Int64 NOT NULL,
cs_quantity Int64,
cs_wholesale_cost Decimal(7,2),
cs_list_price Decimal(7,2),
cs_sales_price Decimal(7,2),
cs_ext_discount_amt Decimal(7,2),
cs_ext_sales_price Decimal(7,2),
cs_ext_wholesale_cost Decimal(7,2),
cs_ext_list_price Decimal(7,2),
cs_ext_tax Decimal(7,2),
cs_coupon_amt Decimal(7,2),
cs_ext_ship_cost Decimal(7,2),
cs_net_paid Decimal(7,2),
cs_net_paid_inc_tax Decimal(7,2),
cs_net_paid_inc_ship Decimal(7,2),
cs_net_paid_inc_ship_tax Decimal(7,2),
cs_net_profit Decimal(7,2),
PRIMARY KEY (cs_item_sk, cs_order_number)
);
CREATE TABLE customer_address (
ca_address_sk Int64 NOT NULL,
ca_address_id FixedString(16) NOT NULL,
ca_street_number FixedString(10),
ca_street_name String,
ca_street_type FixedString(15),
ca_suite_number FixedString(10),
ca_city String,
ca_county String,
ca_state FixedString(2),
ca_zip FixedString(10),
ca_country String,
ca_gmt_offset Decimal(5,2),
ca_location_type FixedString(20),
PRIMARY KEY (ca_address_sk)
);
CREATE TABLE customer_demographics (
cd_demo_sk Int64 NOT NULL,
cd_gender FixedString(1),
cd_marital_status FixedString(1),
cd_education_status FixedString(20),
cd_purchase_estimate Int64,
cd_credit_rating FixedString(10),
cd_dep_count Int64,
cd_dep_employed_count Int64,
cd_dep_college_count Int64,
PRIMARY KEY (cd_demo_sk)
);
CREATE TABLE customer (
c_customer_sk Int64 NOT NULL,
c_customer_id FixedString(16) NOT NULL,
c_current_cdemo_sk Int64,
c_current_hdemo_sk Int64,
c_current_addr_sk Int64,
c_first_shipto_date_sk UInt32,
c_first_sales_date_sk UInt32,
c_salutation FixedString(10),
c_first_name FixedString(20),
c_last_name FixedString(30),
c_preferred_cust_flag FixedString(1),
c_birth_day Int64,
c_birth_month Int64,
c_birth_year Int64,
c_birth_country String,
c_login FixedString(13),
c_email_address FixedString(50),
c_last_review_date_sk UInt32,
PRIMARY KEY (c_customer_sk)
);
CREATE TABLE date_dim (
d_date_sk UInt32 NOT NULL,
d_date_id FixedString(16) NOT NULL,
d_date Date NOT NULL,
d_month_seq Int64,
d_week_seq Int64,
d_quarter_seq Int64,
d_year Int64,
d_dow Int64,
d_moy Int64,
d_dom Int64,
d_qoy Int64,
d_fy_year Int64,
d_fy_quarter_seq Int64,
d_fy_week_seq Int64,
d_day_name FixedString(9),
d_quarter_name FixedString(6),
d_holiday FixedString(1),
d_weekend FixedString(1),
d_following_holiday FixedString(1),
d_first_dom Int64,
d_last_dom Int64,
d_same_day_ly Int64,
d_same_day_lq Int64,
d_current_day FixedString(1),
d_current_week FixedString(1),
d_current_month FixedString(1),
d_current_quarter FixedString(1),
d_current_year FixedString(1),
PRIMARY KEY (d_date_sk)
);
CREATE TABLE household_demographics (
hd_demo_sk Int64 NOT NULL,
hd_income_band_sk Int64,
hd_buy_potential FixedString(15),
hd_dep_count Int64,
hd_vehicle_count Int64,
PRIMARY KEY (hd_demo_sk)
);
CREATE TABLE income_band(
ib_income_band_sk Int64 NOT NULL,
ib_lower_bound Int64,
ib_upper_bound Int64,
PRIMARY KEY (ib_income_band_sk),
);
CREATE TABLE inventory (
inv_date_sk UInt32 NOT NULL,
inv_item_sk Int64 NOT NULL,
inv_warehouse_sk Int64 NOT NULL,
inv_quantity_on_hand Int64,
PRIMARY KEY (inv_date_sk, inv_item_sk, inv_warehouse_sk),
);
CREATE TABLE item (
i_item_sk Int64 NOT NULL,
i_item_id FixedString(16) NOT NULL,
i_rec_start_date Date,
i_rec_end_date Date,
i_item_desc String,
i_current_price Decimal(7,2),
i_wholesale_cost Decimal(7,2),
i_brand_id Int64,
i_brand FixedString(50),
i_class_id Int64,
i_class FixedString(50),
i_category_id Int64,
i_category FixedString(50),
i_manufact_id Int64,
i_manufact FixedString(50),
i_size FixedString(20),
i_formulation FixedString(20),
i_color FixedString(20),
i_units FixedString(10),
i_container FixedString(10),
i_manager_id Int64,
i_product_name FixedString(50),
PRIMARY KEY (i_item_sk)
);
CREATE TABLE promotion (
p_promo_sk Int64 NOT NULL,
p_promo_id FixedString(16) NOT NULL,
p_start_date_sk UInt32,
p_end_date_sk UInt32,
p_item_sk Int64,
p_cost Decimal(15,2),
p_response_target Int64,
p_promo_name FixedString(50),
p_channel_dmail FixedString(1),
p_channel_email FixedString(1),
p_channel_catalog FixedString(1),
p_channel_tv FixedString(1),
p_channel_radio FixedString(1),
p_channel_press FixedString(1),
p_channel_event FixedString(1),
p_channel_demo FixedString(1),
p_channel_details String,
p_purpose FixedString(15),
p_discount_active FixedString(1),
PRIMARY KEY (p_promo_sk)
);
CREATE TABLE reason(
r_reason_sk Int64 NOT NULL,
r_reason_id FixedString(16) NOT NULL,
r_reason_desc FixedString(100),
PRIMARY KEY (r_reason_sk)
);
CREATE TABLE ship_mode(
sm_ship_mode_sk Int64 NOT NULL,
sm_ship_mode_id FixedString(16) NOT NULL,
sm_type FixedString(30),
sm_code FixedString(10),
sm_carrier FixedString(20),
sm_contract FixedString(20),
PRIMARY KEY (sm_ship_mode_sk)
);
CREATE TABLE store_returns (
sr_returned_date_sk UInt32,
sr_return_time_sk UInt32,
sr_item_sk Int64 NOT NULL,
sr_customer_sk Int64,
sr_cdemo_sk Int64,
sr_hdemo_sk Int64,
sr_addr_sk Int64,
sr_store_sk Int64,
sr_reason_sk Int64,
sr_ticket_number Int64 NOT NULL,
sr_return_quantity Int64,
sr_return_amt Decimal(7,2),
sr_return_tax Decimal(7,2),
sr_return_amt_inc_tax Decimal(7,2),
sr_fee Decimal(7,2),
sr_return_ship_cost Decimal(7,2),
sr_refunded_cash Decimal(7,2),
sr_reversed_charge Decimal(7,2),
sr_store_credit Decimal(7,2),
sr_net_loss Decimal(7,2),
PRIMARY KEY (sr_item_sk, sr_ticket_number)
);
CREATE TABLE store_sales (
ss_sold_date_sk UInt32,
ss_sold_time_sk UInt32,
ss_item_sk Int64 NOT NULL,
ss_customer_sk Int64,
ss_cdemo_sk Int64,
ss_hdemo_sk Int64,
ss_addr_sk Int64,
ss_store_sk Int64,
ss_promo_sk Int64,
ss_ticket_number Int64 NOT NULL,
ss_quantity Int64,
ss_wholesale_cost Decimal(7,2),
ss_list_price Decimal(7,2),
ss_sales_price Decimal(7,2),
ss_ext_discount_amt Decimal(7,2),
ss_ext_sales_price Decimal(7,2),
ss_ext_wholesale_cost Decimal(7,2),
ss_ext_list_price Decimal(7,2),
ss_ext_tax Decimal(7,2),
ss_coupon_amt Decimal(7,2),
ss_net_paid Decimal(7,2),
ss_net_paid_inc_tax Decimal(7,2),
ss_net_profit Decimal(7,2),
PRIMARY KEY (ss_item_sk, ss_ticket_number)
);
CREATE TABLE store (
s_store_sk Int64 NOT NULL,
s_store_id FixedString(16) NOT NULL,
s_rec_start_date Date,
s_rec_end_date Date,
s_closed_date_sk UInt32,
s_store_name String,
s_number_employees Int64,
s_floor_space Int64,
s_hours FixedString(20),
s_manager String,
s_market_id Int64,
s_geography_class String,
s_market_desc String,
s_market_manager String,
s_division_id Int64,
s_division_name String,
s_company_id Int64,
s_company_name String,
s_street_number String,
s_street_name String,
s_street_type FixedString(15),
s_suite_number FixedString(10),
s_city String,
s_county String,
s_state FixedString(2),
s_zip FixedString(10),
s_country String,
s_gmt_offset Decimal(5,2),
s_tax_percentage Decimal(5,2),
PRIMARY KEY (s_store_sk)
);
CREATE TABLE time_dim (
t_time_sk UInt32 NOT NULL,
t_time_id FixedString(16) NOT NULL,
t_time Int64 NOT NULL,
t_hour Int64,
t_minute Int64,
t_second Int64,
t_am_pm FixedString(2),
t_shift FixedString(20),
t_sub_shift FixedString(20),
t_meal_time FixedString(20),
PRIMARY KEY (t_time_sk)
);
CREATE TABLE warehouse(
w_warehouse_sk Int64 NOT NULL,
w_warehouse_id FixedString(16) NOT NULL,
w_warehouse_name String,
w_warehouse_sq_ft Int64,
w_street_number FixedString(10),
w_street_name String,
w_street_type FixedString(15),
w_suite_number FixedString(10),
w_city String,
w_county String,
w_state FixedString(2),
w_zip FixedString(10),
w_country String,
w_gmt_offset Decimal(5,2),
PRIMARY KEY (w_warehouse_sk)
);
CREATE TABLE web_page(
wp_web_page_sk Int64 NOT NULL,
wp_web_page_id FixedString(16) NOT NULL,
wp_rec_start_date Date,
wp_rec_end_date Date,
wp_creation_date_sk UInt32,
wp_access_date_sk UInt32,
wp_autogen_flag FixedString(1),
wp_customer_sk Int64,
wp_url String,
wp_type FixedString(50),
wp_char_count Int64,
wp_link_count Int64,
wp_image_count Int64,
wp_max_ad_count Int64,
PRIMARY KEY (wp_web_page_sk)
);
CREATE TABLE web_returns (
wr_returned_date_sk UInt32,
wr_returned_time_sk UInt32,
wr_item_sk Int64 NOT NULL,
wr_refunded_customer_sk Int64,
wr_refunded_cdemo_sk Int64,
wr_refunded_hdemo_sk Int64,
wr_refunded_addr_sk Int64,
wr_returning_customer_sk Int64,
wr_returning_cdemo_sk Int64,
wr_returning_hdemo_sk Int64,
wr_returning_addr_sk Int64,
wr_web_page_sk Int64,
wr_reason_sk Int64,
wr_order_number Int64 NOT NULL,
wr_return_quantity Int64,
wr_return_amt Decimal(7,2),
wr_return_tax Decimal(7,2),
wr_return_amt_inc_tax Decimal(7,2),
wr_fee Decimal(7,2),
wr_return_ship_cost Decimal(7,2),
wr_refunded_cash Decimal(7,2),
wr_reversed_charge Decimal(7,2),
wr_account_credit Decimal(7,2),
wr_net_loss Decimal(7,2),
PRIMARY KEY (wr_item_sk, wr_order_number)
);
CREATE TABLE web_sales (
ws_sold_date_sk UInt32,
ws_sold_time_sk UInt32,
ws_ship_date_sk UInt32,
ws_item_sk Int64 NOT NULL,
ws_bill_customer_sk Int64,
ws_bill_cdemo_sk Int64,
ws_bill_hdemo_sk Int64,
ws_bill_addr_sk Int64,
ws_ship_customer_sk Int64,
ws_ship_cdemo_sk Int64,
ws_ship_hdemo_sk Int64,
ws_ship_addr_sk Int64,
ws_web_page_sk Int64,
ws_web_site_sk Int64,
ws_ship_mode_sk Int64,
ws_warehouse_sk Int64,
ws_promo_sk Int64,
ws_order_number Int64 NOT NULL,
ws_quantity Int64,
ws_wholesale_cost Decimal(7,2),
ws_list_price Decimal(7,2),
ws_sales_price Decimal(7,2),
ws_ext_discount_amt Decimal(7,2),
ws_ext_sales_price Decimal(7,2),
ws_ext_wholesale_cost Decimal(7,2),
ws_ext_list_price Decimal(7,2),
ws_ext_tax Decimal(7,2),
ws_coupon_amt Decimal(7,2),
ws_ext_ship_cost Decimal(7,2),
ws_net_paid Decimal(7,2),
ws_net_paid_inc_tax Decimal(7,2),
ws_net_paid_inc_ship Decimal(7,2),
ws_net_paid_inc_ship_tax Decimal(7,2),
ws_net_profit Decimal(7,2),
PRIMARY KEY (ws_item_sk, ws_order_number)
);
CREATE TABLE web_site (
web_site_sk Int64 NOT NULL,
web_site_id FixedString(16) NOT NULL,
web_rec_start_date Date,
web_rec_end_date Date,
web_name String,
web_open_date_sk UInt32,
web_close_date_sk UInt32,
web_class String,
web_manager String,
web_mkt_id Int64,
web_mkt_class String,
web_mkt_desc String,
web_market_manager String,
web_company_id Int64,
web_company_name FixedString(50),
web_street_number FixedString(10),
web_street_name String,
web_street_type FixedString(15),
web_suite_number FixedString(10),
web_city String,
web_county String,
web_state FixedString(2),
web_zip FixedString(10),
web_country String,
web_gmt_offset Decimal(5,2),
web_tax_percentage Decimal(5,2),
PRIMARY KEY (web_site_sk)
);
The data can be imported as follows:
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO call_center FORMAT CSV" < call_center.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO catalog_page FORMAT CSV" < catalog_page.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO catalog_returns FORMAT CSV" < catalog_returns.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO catalog_sales FORMAT CSV" < catalog_sales.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO customer FORMAT CSV" < customer.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO customer_address FORMAT CSV" < customer_address.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO customer_demographics FORMAT CSV" < customer_demographics.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO date_dim FORMAT CSV" < date_dim.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO household_demographics FORMAT CSV" < household_demographics.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO income_band FORMAT CSV" < income_band.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO inventory FORMAT CSV" < inventory.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO item FORMAT CSV" < item.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO promotion FORMAT CSV" < promotion.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO reason FORMAT CSV" < reason.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO ship_mode FORMAT CSV" < ship_mode.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO store FORMAT CSV" < store.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO store_returns FORMAT CSV" < store_returns.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO store_sales FORMAT CSV" < store_sales.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO time_dim FORMAT CSV" < time_dim.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO warehouse FORMAT CSV" < warehouse.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO web_page FORMAT CSV" < web_page.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO web_returns FORMAT CSV" < web_returns.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO web_sales FORMAT CSV" < web_sales.dat
clickhouse-client --format_csv_delimiter '|' --query "INSERT INTO web_site FORMAT CSV" < web_site.dat
Then run the generated queries.
Queries
Note
The following settings should be enabled to produce correct results according to SQL standard:
SET data_type_default_nullable = 1;
SET group_by_use_nulls = 1;
SET intersect_default_mode = 'DISTINCT';
SET joined_subquery_requires_alias = 0;
SET union_default_mode = 'DISTINCT';
SET join_use_nulls = 1;
Correctness
The result of the queries agrees with the official results unless mentioned otherwise. There may be minor precision differences, which are permitted by the TPC-DS specification.
Q1
WITH customer_total_return AS
(
SELECT
sr_customer_sk AS ctr_customer_sk,
sr_store_sk AS ctr_store_sk,
sum(sr_return_amt) AS ctr_total_return
FROM store_returns, date_dim
WHERE (sr_returned_date_sk = d_date_sk) AND (d_year = 2000)
GROUP BY
sr_customer_sk,
sr_store_sk
)
SELECT c_customer_id
FROM customer_total_return AS ctr1, store, customer
WHERE (ctr1.ctr_total_return > (
SELECT avg(ctr_total_return) * 1.2
FROM customer_total_return AS 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;
Q2
WITH
wscs AS
(
SELECT
sold_date_sk,
sales_price
FROM
(
SELECT
ws_sold_date_sk AS sold_date_sk,
ws_ext_sales_price AS sales_price
FROM web_sales
UNION ALL
SELECT
cs_sold_date_sk AS sold_date_sk,
cs_ext_sales_price AS sales_price
FROM catalog_sales
)
),
wswscs AS
(
SELECT
d_week_seq,
sum(multiIf(d_day_name = 'Sunday', sales_price, NULL)) AS sun_sales,
sum(multiIf(d_day_name = 'Monday', sales_price, NULL)) AS mon_sales,
sum(multiIf(d_day_name = 'Tuesday', sales_price, NULL)) AS tue_sales,
sum(multiIf(d_day_name = 'Wednesday', sales_price, NULL)) AS wed_sales,
sum(multiIf(d_day_name = 'Thursday', sales_price, NULL)) AS thu_sales,
sum(multiIf(d_day_name = 'Friday', sales_price, NULL)) AS fri_sales,
sum(multiIf(d_day_name = 'Saturday', sales_price, NULL)) AS 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 AS d_week_seq1,
sun_sales AS sun_sales1,
mon_sales AS mon_sales1,
tue_sales AS tue_sales1,
wed_sales AS wed_sales1,
thu_sales AS thu_sales1,
fri_sales AS fri_sales1,
sat_sales AS sat_sales1
FROM wswscs, date_dim
WHERE (date_dim.d_week_seq = wswscs.d_week_seq) AND (d_year = 2001)
) AS y,
(
SELECT
wswscs.d_week_seq AS d_week_seq2,
sun_sales AS sun_sales2,
mon_sales AS mon_sales2,
tue_sales AS tue_sales2,
wed_sales AS wed_sales2,
thu_sales AS thu_sales2,
fri_sales AS fri_sales2,
sat_sales AS sat_sales2
FROM wswscs, date_dim
WHERE (date_dim.d_week_seq = wswscs.d_week_seq) AND (d_year = (2001 + 1))
) AS z
WHERE d_week_seq1 = (d_week_seq2 - 53)
ORDER BY d_week_seq1;
Q3
SELECT
dt.d_year,
item.i_brand_id AS brand_id,
item.i_brand AS brand,
sum(ss_ext_sales_price) AS sum_agg
FROM date_dim AS 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 = 128) AND (dt.d_moy = 11)
GROUP BY
dt.d_year,
item.i_brand,
item.i_brand_id
ORDER BY
dt.d_year,
sum_agg DESC,
brand_id
LIMIT 100;
Q4
WITH year_total AS
(
SELECT
c_customer_id AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
c_preferred_cust_flag AS customer_preferred_cust_flag,
c_birth_country AS customer_birth_country,
c_login AS customer_login,
c_email_address AS customer_email_address,
d_year AS dyear,
sum((((ss_ext_list_price - ss_ext_wholesale_cost) - ss_ext_discount_amt) + ss_ext_sales_price) / 2) AS year_total,
's' AS 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 AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
c_preferred_cust_flag AS customer_preferred_cust_flag,
c_birth_country AS customer_birth_country,
c_login AS customer_login,
c_email_address AS customer_email_address,
d_year AS dyear,
sum((((cs_ext_list_price - cs_ext_wholesale_cost) - cs_ext_discount_amt) + cs_ext_sales_price) / 2) AS year_total,
'c' AS 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 AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
c_preferred_cust_flag AS customer_preferred_cust_flag,
c_birth_country AS customer_birth_country,
c_login AS customer_login,
c_email_address AS customer_email_address,
d_year AS dyear,
sum((((ws_ext_list_price - ws_ext_wholesale_cost) - ws_ext_discount_amt) + ws_ext_sales_price) / 2) AS year_total,
'w' AS sale_type
FROM customer, web_sales, date_dim
WHERE (c_customer_sk = ws_bill_customer_sk) AND (ws_sold_date_sk = d_date_sk)
GROUP BY
c_customer_id,
c_first_name,
c_last_name,
c_preferred_cust_flag,
c_birth_country,
c_login,
c_email_address,
d_year
)
SELECT
t_s_secyear.customer_id,
t_s_secyear.customer_first_name,
t_s_secyear.customer_last_name,
t_s_secyear.customer_preferred_cust_flag
FROM year_total AS t_s_firstyear, year_total AS t_s_secyear, year_total AS t_c_firstyear, year_total AS t_c_secyear, year_total AS t_w_firstyear, year_total AS 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 (multiIf(t_c_firstyear.year_total > 0, t_c_secyear.year_total / t_c_firstyear.year_total, NULL) > multiIf(t_s_firstyear.year_total > 0, t_s_secyear.year_total / t_s_firstyear.year_total, NULL)) AND (multiIf(t_c_firstyear.year_total > 0, t_c_secyear.year_total / t_c_firstyear.year_total, NULL) > multiIf(t_w_firstyear.year_total > 0, t_w_secyear.year_total / t_w_firstyear.year_total, NULL))
ORDER BY
t_s_secyear.customer_id,
t_s_secyear.customer_first_name,
t_s_secyear.customer_last_name,
t_s_secyear.customer_preferred_cust_flag
LIMIT 100;
Q5
WITH
ssr AS
(
SELECT
s_store_id,
sum(sales_price) AS sales,
sum(profit) AS profit,
sum(return_amt) AS returns,
sum(net_loss) AS profit_loss
FROM
(
SELECT
ss_store_sk AS store_sk,
ss_sold_date_sk AS date_sk,
ss_ext_sales_price AS sales_price,
ss_net_profit AS profit,
CAST(0, 'decimal(7, 2)') AS return_amt,
CAST(0, '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, 'decimal(7, 2)') AS sales_price,
CAST(0, 'decimal(7, 2)') AS profit,
sr_return_amt AS return_amt,
sr_net_loss AS net_loss
FROM store_returns
) AS salesreturns, date_dim, store
WHERE (date_sk = d_date_sk) AND ((d_date >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 14 day))) 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, 'decimal(7, 2)') AS return_amt,
CAST(0, '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, 'decimal(7, 2)') AS sales_price,
CAST(0, 'decimal(7, 2)') AS profit,
cr_return_amount AS return_amt,
cr_net_loss AS net_loss
FROM catalog_returns
) AS salesreturns, date_dim, catalog_page
WHERE (date_sk = d_date_sk) AND (d_date BETWEEN CAST('2000-08-23', 'date') AND CAST('2000-08-23', 'date') + INTERVAL 14 day) 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, 'decimal(7, 2)') AS return_amt,
CAST(0, '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, 'decimal(7, 2)') AS sales_price,
CAST(0, 'decimal(7, 2)') AS profit,
wr_return_amt AS return_amt,
wr_net_loss AS net_loss
FROM web_returns
LEFT JOIN web_sales ON (wr_item_sk = ws_item_sk) AND (wr_order_number = ws_order_number)
) AS salesreturns, date_dim, web_site
WHERE (date_sk = d_date_sk) AND ((d_date >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 14 day))) 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,
concat('store', s_store_id) AS id,
sales,
returns,
profit - profit_loss AS profit
FROM ssr
UNION ALL
SELECT
'catalog channel' AS channel,
concat('catalog_page', cp_catalog_page_id) AS id,
sales,
returns,
profit - profit_loss AS profit
FROM csr
UNION ALL
SELECT
'web channel' AS channel,
concat('web_site', web_site_id) AS id,
sales,
returns,
profit - profit_loss AS profit
FROM wsr
) AS x
GROUP BY
channel,
id
WITH ROLLUP
ORDER BY
channel,
id
LIMIT 100;
Q6
SELECT
a.ca_state AS state,
count(*) AS cnt
FROM customer_address AS a, customer AS c, store_sales AS s, date_dim AS d, item AS 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 = 2001) AND (d_moy = 1)
))
AND (i.i_current_price > 1.2 * (
SELECT avg(j.i_current_price)
FROM item AS j
WHERE (j.i_category = i.i_category)
))
GROUP BY a.ca_state
HAVING count(*) >= 10
ORDER BY cnt, a.ca_state
LIMIT 100;
Q7
SELECT
i_item_id,
avg(ss_quantity) AS agg1,
avg(ss_list_price) AS agg2,
avg(ss_coupon_amt) AS agg3,
avg(ss_sales_price) AS 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 = 'S')
AND (cd_education_status = 'College')
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;
Q8
SELECT
s_store_name,
sum(ss_net_profit)
FROM store_sales, date_dim, store,
(
SELECT substr(ca_zip, 1, 2) AS ca_zip
FROM
(
SELECT substr(ca_zip, 1, 5) AS ca_zip
FROM customer_address
WHERE substr(ca_zip, 1, 5) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548', '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920', '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601', '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741', '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550', '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312', '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666', '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448', '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162', '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341', '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470', '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359', '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206', '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420', '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325', '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146', '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976', '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371', '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858', '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451', '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305', '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882', '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328', '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076', '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211', '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791', '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786', '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368', '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246', '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867', '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535', '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721', '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253', '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393', '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968', '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016', '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721', '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123', '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949', '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103', '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337', '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279', '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136', '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810', '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057', '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013', '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908', '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144', '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385', '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458', '35576')
INTERSECT
SELECT ca_zip
FROM
(
SELECT
substr(ca_zip, 1, 5) AS ca_zip,
count(*) AS 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
) AS A1
) AS A2
) AS V1
WHERE (ss_store_sk = s_store_sk) AND (ss_sold_date_sk = d_date_sk) AND (d_qoy = 2) AND (d_year = 1998) AND (substr(s_zip, 1, 2) = substr(V1.ca_zip, 1, 2))
GROUP BY s_store_name
ORDER BY s_store_name
LIMIT 100;
Q9
SELECT
multiIf((
SELECT count(*)
FROM store_sales
WHERE (ss_quantity >= 1) AND (ss_quantity <= 20)
) > 74129, (
SELECT avg(ss_ext_discount_amt)
FROM store_sales
WHERE (ss_quantity >= 1) AND (ss_quantity <= 20)
), (
SELECT avg(ss_net_paid)
FROM store_sales
WHERE (ss_quantity >= 1) AND (ss_quantity <= 20)
)) AS bucket1,
multiIf((
SELECT count(*)
FROM store_sales
WHERE (ss_quantity >= 21) AND (ss_quantity <= 40)
) > 122840, (
SELECT avg(ss_ext_discount_amt)
FROM store_sales
WHERE (ss_quantity >= 21) AND (ss_quantity <= 40)
), (
SELECT avg(ss_net_paid)
FROM store_sales
WHERE (ss_quantity >= 21) AND (ss_quantity <= 40)
)) AS bucket2,
multiIf((
SELECT count(*)
FROM store_sales
WHERE (ss_quantity >= 41) AND (ss_quantity <= 60)
) > 56580, (
SELECT avg(ss_ext_discount_amt)
FROM store_sales
WHERE (ss_quantity >= 41) AND (ss_quantity <= 60)
), (
SELECT avg(ss_net_paid)
FROM store_sales
WHERE (ss_quantity >= 41) AND (ss_quantity <= 60)
)) AS bucket3,
multiIf((
SELECT count(*)
FROM store_sales
WHERE (ss_quantity >= 61) AND (ss_quantity <= 80)
) > 10097, (
SELECT avg(ss_ext_discount_amt)
FROM store_sales
WHERE (ss_quantity >= 61) AND (ss_quantity <= 80)
), (
SELECT avg(ss_net_paid)
FROM store_sales
WHERE (ss_quantity >= 61) AND (ss_quantity <= 80)
)) AS bucket4,
multiIf((
SELECT count(*)
FROM store_sales
WHERE (ss_quantity >= 81) AND (ss_quantity <= 100)
) > 165306, (
SELECT avg(ss_ext_discount_amt)
FROM store_sales
WHERE (ss_quantity >= 81) AND (ss_quantity <= 100)
), (
SELECT avg(ss_net_paid)
FROM store_sales
WHERE (ss_quantity >= 81) AND (ss_quantity <= 100)
)) AS bucket5
FROM reason
WHERE r_reason_sk = 1;
Q10
SELECT
cd_gender,
cd_marital_status,
cd_education_status,
count(*) AS cnt1,
cd_purchase_estimate,
count(*) AS cnt2,
cd_credit_rating,
count(*) AS cnt3,
cd_dep_count,
count(*) AS cnt4,
cd_dep_employed_count,
count(*) AS cnt5,
cd_dep_college_count,
count(*) AS cnt6
FROM customer AS c, customer_address AS ca, customer_demographics
WHERE (c.c_current_addr_sk = ca.ca_address_sk) AND (ca_county IN ('Rush County', 'Toole County', 'Jefferson County', 'Dona Ana County', 'La Porte 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 >= 1) AND (d_moy <= (1 + 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 >= 1) AND (d_moy <= (1 + 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 >= 1) AND (d_moy <= (1 + 3)))
)))
GROUP BY
cd_gender,
cd_marital_status,
cd_education_status,
cd_purchase_estimate,
cd_credit_rating,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
ORDER BY
cd_gender,
cd_marital_status,
cd_education_status,
cd_purchase_estimate,
cd_credit_rating,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
LIMIT 100;
Q11
WITH year_total AS
(
SELECT
c_customer_id AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
c_preferred_cust_flag AS customer_preferred_cust_flag,
c_birth_country AS customer_birth_country,
c_login AS customer_login,
c_email_address AS customer_email_address,
d_year AS dyear,
sum(ss_ext_list_price - ss_ext_discount_amt) AS year_total,
's' AS 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 AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
c_preferred_cust_flag AS customer_preferred_cust_flag,
c_birth_country AS customer_birth_country,
c_login AS customer_login,
c_email_address AS customer_email_address,
d_year AS dyear,
sum(ws_ext_list_price - ws_ext_discount_amt) AS year_total,
'w' AS sale_type
FROM customer, web_sales, date_dim
WHERE (c_customer_sk = ws_bill_customer_sk) AND (ws_sold_date_sk = d_date_sk)
GROUP BY
c_customer_id,
c_first_name,
c_last_name,
c_preferred_cust_flag,
c_birth_country,
c_login,
c_email_address,
d_year
)
SELECT
t_s_secyear.customer_id,
t_s_secyear.customer_first_name,
t_s_secyear.customer_last_name,
t_s_secyear.customer_preferred_cust_flag
FROM year_total AS t_s_firstyear, year_total AS t_s_secyear, year_total AS t_w_firstyear, year_total AS t_w_secyear
WHERE (t_s_secyear.customer_id = t_s_firstyear.customer_id) AND (t_s_firstyear.customer_id = t_w_secyear.customer_id) AND (t_s_firstyear.customer_id = t_w_firstyear.customer_id) AND (t_s_firstyear.sale_type = 's') AND (t_w_firstyear.sale_type = 'w') AND (t_s_secyear.sale_type = 's') AND (t_w_secyear.sale_type = 'w') AND (t_s_firstyear.dyear = 2001) AND (t_s_secyear.dyear = (2001 + 1)) AND (t_w_firstyear.dyear = 2001) AND (t_w_secyear.dyear = (2001 + 1)) AND (t_s_firstyear.year_total > 0) AND (t_w_firstyear.year_total > 0) AND (multiIf(t_w_firstyear.year_total > 0, CAST(t_w_secyear.year_total AS Float32) / t_w_firstyear.year_total, 0.) > multiIf(t_s_firstyear.year_total > 0, CAST(t_s_secyear.year_total AS Float32) / t_s_firstyear.year_total, 0.))
ORDER BY
t_s_secyear.customer_id,
t_s_secyear.customer_first_name,
t_s_secyear.customer_last_name,
t_s_secyear.customer_preferred_cust_flag
LIMIT 100;
Q12
SELECT
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price,
sum(ws_ext_sales_price) AS itemrevenue,
(sum(ws_ext_sales_price) * 100) / sum(sum(ws_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio
FROM web_sales, item, date_dim
WHERE (ws_item_sk = i_item_sk) AND (i_category IN ('Sports', 'Books', 'Home')) AND (ws_sold_date_sk = d_date_sk) AND ((d_date >= CAST('1999-02-22', 'date')) AND (d_date <= (CAST('1999-02-22', 'date') + INTERVAL 30 day)))
GROUP BY
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price
ORDER BY
i_category,
i_class,
i_item_id,
i_item_desc,
revenueratio
LIMIT 100;
Q13
SELECT
avg(ss_quantity),
avg(ss_ext_sales_price),
avg(ss_ext_wholesale_cost),
sum(ss_ext_wholesale_cost)
FROM store_sales, store, customer_demographics, household_demographics, customer_address, date_dim
WHERE (s_store_sk = ss_store_sk)
AND (ss_sold_date_sk = d_date_sk)
AND (d_year = 2001)
AND (
(
(ss_hdemo_sk = hd_demo_sk)
AND (cd_demo_sk = ss_cdemo_sk)
AND (cd_marital_status = 'M')
AND (cd_education_status = 'Advanced 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 = 'College')
AND (ss_sales_price BETWEEN 50.00 AND 100.00)
AND (hd_dep_count = 1)
)
OR (
(ss_hdemo_sk = hd_demo_sk)
AND (cd_demo_sk = ss_cdemo_sk)
AND (cd_marital_status = 'W')
AND (cd_education_status = '2 yr Degree')
AND (ss_sales_price BETWEEN 150.00 AND 200.)
AND (hd_dep_count = 1)
)
)
AND (
(
(ss_addr_sk = ca_address_sk)
AND (ca_country = 'United States')
AND (ca_state IN ('TX', 'OH', 'TX'))
AND (ss_net_profit BETWEEN 100 AND 200)
)
OR (
(ss_addr_sk = ca_address_sk)
AND (ca_country = 'United States')
AND (ca_state IN ('OR', 'NM', 'KY'))
AND (ss_net_profit BETWEEN 150 AND 300)
)
OR (
(ss_addr_sk = ca_address_sk)
AND (ca_country = 'United States')
AND (ca_state IN ('VA', 'TX', 'MS'))
AND (ss_net_profit BETWEEN 50 AND 250)
)
);
Q14
WITH
cross_items AS
(
SELECT i_item_sk AS ss_item_sk
FROM item,
(
SELECT
iss.i_brand_id AS brand_id,
iss.i_class_id AS class_id,
iss.i_category_id AS category_id
FROM store_sales, item AS iss, date_dim AS 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 AS ics, date_dim AS 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 AS iws, date_dim AS 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) AS average_sales
FROM
(
SELECT
ss_quantity AS quantity,
ss_list_price AS 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 AS quantity,
cs_list_price AS 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 AS quantity,
ws_list_price AS list_price
FROM web_sales, date_dim
WHERE (ws_sold_date_sk = d_date_sk) AND (d_year BETWEEN 1999 AND 1999 + 2)
) AS x
)
SELECT
channel,
i_brand_id,
i_class_id,
i_category_id,
sum(sales),
sum(number_sales)
FROM
(
SELECT
'store' AS channel,
i_brand_id,
i_class_id,
i_category_id,
sum(ss_quantity * ss_list_price) AS sales,
count(*) AS 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' AS channel,
i_brand_id,
i_class_id,
i_category_id,
sum(cs_quantity * cs_list_price) AS sales,
count(*) AS 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' AS channel,
i_brand_id,
i_class_id,
i_category_id,
sum(ws_quantity * ws_list_price) AS sales,
count(*) AS 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
)
) AS y
GROUP BY
channel,
i_brand_id,
i_class_id,
i_category_id
WITH ROLLUP
ORDER BY
channel,
i_brand_id,
i_class_id,
i_category_id
LIMIT 100;
WITH
cross_items AS
(
SELECT i_item_sk AS ss_item_sk
FROM item,
(
SELECT
iss.i_brand_id AS brand_id,
iss.i_class_id AS class_id,
iss.i_category_id AS category_id
FROM store_sales, item AS iss, date_dim AS 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 AS ics, date_dim AS 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 AS iws, date_dim AS 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) AS average_sales
FROM
(
SELECT
ss_quantity AS quantity,
ss_list_price AS 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 AS quantity,
cs_list_price AS 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 AS quantity,
ws_list_price AS list_price
FROM web_sales, date_dim
WHERE (ws_sold_date_sk = d_date_sk) AND (d_year BETWEEN 1999 AND 1999 + 2)
) AS x
)
SELECT
this_year.channel AS ty_channel,
this_year.i_brand_id AS ty_brand,
this_year.i_class_id AS ty_class,
this_year.i_category_id AS ty_category,
this_year.sales AS ty_sales,
this_year.number_sales AS ty_number_sales,
last_year.channel AS ly_channel,
last_year.i_brand_id AS ly_brand,
last_year.i_class_id AS ly_class,
last_year.i_category_id AS ly_category,
last_year.sales AS ly_sales,
last_year.number_sales AS ly_number_sales
FROM
(
SELECT
'store' AS channel,
i_brand_id,
i_class_id,
i_category_id,
sum(ss_quantity * ss_list_price) AS sales,
count(*) AS 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 = 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
)
) AS this_year,
(
SELECT
'store' AS channel,
i_brand_id,
i_class_id,
i_category_id,
sum(ss_quantity * ss_list_price) AS sales,
count(*) AS 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 = 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
)
) AS 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;
Q15
SELECT
ca_zip,
sum(cs_sales_price)
FROM catalog_sales, customer, customer_address, date_dim
WHERE (cs_bill_customer_sk = c_customer_sk)
AND (c_current_addr_sk = ca_address_sk)
AND ((substr(ca_zip, 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792'))
OR (ca_state IN ('CA', 'WA', 'GA'))
OR (cs_sales_price > 500))
AND (cs_sold_date_sk = d_date_sk)
AND (d_qoy = 2)
AND (d_year = 2001)
GROUP BY ca_zip
ORDER BY ca_zip
LIMIT 100;
Q16
SELECT
count(DISTINCT cs_order_number) AS "order count",
sum(cs_ext_ship_cost) AS "total shipping cost",
sum(cs_net_profit) AS "total net profit"
FROM catalog_sales AS cs1, date_dim, customer_address, call_center
WHERE (d_date BETWEEN '2002-2-01' AND (CAST('2002-2-01' AS date) + INTERVAL 60 DAY))
AND (cs1.cs_ship_date_sk = d_date_sk)
AND (cs1.cs_ship_addr_sk = ca_address_sk)
AND (ca_state = 'GA')
AND (cs1.cs_call_center_sk = cc_call_center_sk)
AND (cc_county IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County'))
AND EXISTS (
SELECT *
FROM catalog_sales AS 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 AS cr1
WHERE (cs1.cs_order_number = cr1.cr_order_number)
)
ORDER BY count(DISTINCT cs_order_number)
LIMIT 100;
Q17
Note
The query returns nan instead of NULL when stddev_samp is called on a single value. Corresponding issue: https://github.com/ClickHouse/ClickHouse/issues/94683. Otherwise, the result is correct.
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 AS d1, date_dim AS d2, date_dim AS d3, store, item
WHERE (d1.d_quarter_name = '2001Q1')
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 ('2001Q1', '2001Q2', '2001Q3'))
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 ('2001Q1', '2001Q2', '2001Q3'))
GROUP BY
i_item_id,
i_item_desc,
s_state
ORDER BY
i_item_id,
i_item_desc,
s_state
LIMIT 100;
Q18
SELECT
i_item_id,
ca_country,
ca_state,
ca_county,
avg(CAST(cs_quantity AS Nullable(decimal(12, 2)))) AS agg1,
avg(CAST(cs_list_price AS Nullable(decimal(12, 2)))) AS agg2,
avg(CAST(cs_coupon_amt AS Nullable(decimal(12, 2)))) AS agg3,
avg(CAST(cs_sales_price AS Nullable(decimal(12, 2)))) AS agg4,
avg(CAST(cs_net_profit AS Nullable(decimal(12, 2)))) AS agg5,
avg(CAST(c_birth_year AS Nullable(decimal(12, 2)))) AS agg6,
avg(CAST(cd1.cd_dep_count AS Nullable(decimal(12, 2)))) AS agg7
FROM catalog_sales, customer_demographics AS cd1, customer_demographics AS 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 = 'F')
AND (cd1.cd_education_status = 'Unknown')
AND (c_current_cdemo_sk = cd2.cd_demo_sk)
AND (c_current_addr_sk = ca_address_sk)
AND (c_birth_month IN (1, 6, 8, 9, 12, 2))
AND (d_year = 1998)
AND (ca_state IN ('MS', 'IN', 'ND', 'OK', 'NM', 'VA', 'MS'))
GROUP BY ROLLUP (i_item_id, ca_country, ca_state, ca_county)
ORDER BY
ca_country,
ca_state,
ca_county,
i_item_id
LIMIT 100;
Q19
SELECT
i_brand_id AS brand_id,
i_brand AS brand,
i_manufact_id,
i_manufact,
sum(ss_ext_sales_price) AS 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 = 1998)
AND (ss_customer_sk = c_customer_sk)
AND (c_current_addr_sk = ca_address_sk)
AND (substr(ca_zip, 1, 5) <> substr(s_zip, 1, 5))
AND (ss_store_sk = s_store_sk)
GROUP BY
i_brand,
i_brand_id,
i_manufact_id,
i_manufact
ORDER BY
ext_price DESC,
i_brand,
i_brand_id,
i_manufact_id,
i_manufact
LIMIT 100;
Q20
SELECT
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price,
sum(cs_ext_sales_price) AS itemrevenue,
sum(cs_ext_sales_price) * 100 / sum(sum(cs_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio
FROM catalog_sales, item, date_dim
WHERE (cs_item_sk = i_item_sk)
AND (i_category IN ('Sports', 'Books', 'Home'))
AND (cs_sold_date_sk = d_date_sk)
AND (d_date BETWEEN CAST('1999-02-22' AS date) AND (CAST('1999-02-22' AS date) + INTERVAL 30 DAY))
GROUP BY
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price
ORDER BY
i_category,
i_class,
i_item_id,
i_item_desc,
revenueratio
LIMIT 100;
Q21
SELECT *
FROM
(
SELECT
w_warehouse_name,
i_item_id,
sum(CASE WHEN (CAST(d_date AS date) < CAST('2000-03-11' AS date)) THEN inv_quantity_on_hand ELSE 0 END) AS inv_before,
sum(CASE WHEN (CAST(d_date AS date) >= CAST('2000-03-11' 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-03-11' AS date) - INTERVAL 30 DAY) AND (CAST('2000-03-11' AS date) + INTERVAL 30 DAY))
GROUP BY w_warehouse_name, i_item_id
) AS x
WHERE (CASE WHEN inv_before > 0 THEN inv_after / inv_before ELSE NULL END) BETWEEN 2.0 / 3.0 AND 3.0 / 2.0
ORDER BY
w_warehouse_name,
i_item_id
LIMIT 100;
Q22
SELECT
i_product_name,
i_brand,
i_class,
i_category,
avg(inv_quantity_on_hand) AS 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 1200 AND 1200 + 11)
GROUP BY ROLLUP(i_product_name, i_brand, i_class, i_category)
ORDER BY qoh, i_product_name, i_brand, i_class, i_category
LIMIT 100;
Q23
WITH
frequent_ss_items AS
(
SELECT
substr(i_item_desc, 1, 30) AS itemdesc,
i_item_sk AS item_sk,
d_date AS solddate,
count(*) AS 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) AS tpcds_cmax
FROM
(
SELECT
c_customer_sk,
sum(ss_quantity * ss_sales_price) AS 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) AS ssales
FROM store_sales, customer
WHERE (ss_customer_sk = c_customer_sk)
GROUP BY c_customer_sk
HAVING sum(ss_quantity * ss_sales_price) > (50 / 100.0) * (
SELECT *
FROM max_store_sales
)
)
SELECT sum(sales)
FROM
(
SELECT cs_quantity * cs_list_price AS sales
FROM catalog_sales, date_dim
WHERE (d_year = 2000)
AND (d_moy = 2)
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 AS sales
FROM web_sales, date_dim
WHERE (d_year = 2000)
AND (d_moy = 2)
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) AS itemdesc,
i_item_sk AS item_sk,
d_date AS solddate,
count(*) AS 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) AS tpcds_cmax
FROM
(
SELECT
c_customer_sk,
sum(ss_quantity * ss_sales_price) AS 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) AS ssales
FROM store_sales, customer
WHERE (ss_customer_sk = c_customer_sk)
GROUP BY c_customer_sk
HAVING sum(ss_quantity * ss_sales_price) > (50 / 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) AS sales
FROM catalog_sales, customer, date_dim
WHERE (d_year = 2000)
AND (d_moy = 2)
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) AS sales
FROM web_sales, customer, date_dim
WHERE (d_year = 2000)
AND (d_moy = 2)
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;
Q24
WITH
ssales AS
(
SELECT
c_last_name,
c_first_name,
s_store_name,
ca_state,
s_state,
i_color,
i_current_price,
i_manager_id,
i_units,
i_size,
sum(ss_net_paid) AS 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 = 8)
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) AS paid
FROM ssales
WHERE (i_color = 'peach')
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_paid) AS 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 = 8)
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) AS paid
FROM ssales
WHERE (i_color = 'saddle')
GROUP BY
c_last_name,
c_first_name,
s_store_name
HAVING sum(netpaid) > (
SELECT 0.05 * avg(netpaid)
FROM ssales
)
ORDER BY
c_last_name,
c_first_name,
s_store_name;
Q25
SELECT
i_item_id,
i_item_desc,
s_store_id,
s_store_name,
sum(ss_net_profit) AS store_sales_profit,
sum(sr_net_loss) AS store_returns_loss,
sum(cs_net_profit) AS catalog_sales_profit
FROM store_sales, store_returns, catalog_sales, date_dim AS d1, date_dim AS d2, date_dim AS d3, store, item
WHERE (d1.d_moy = 4)
AND (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 (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 = 2001)
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 = 2001)
GROUP BY
i_item_id,
i_item_desc,
s_store_id,
s_store_name
ORDER BY
i_item_id,
i_item_desc,
s_store_id,
s_store_name
LIMIT 100;
Q26
SELECT
i_item_id,
avg(cs_quantity) AS agg1,
avg(cs_list_price) AS agg2,
avg(cs_coupon_amt) AS agg3,
avg(cs_sales_price) AS 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 = 'M')
AND (cd_marital_status = 'S')
AND (cd_education_status = 'College')
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;
Q27
SELECT
i_item_id,
s_state,
grouping(s_state) AS g_state,
avg(ss_quantity) AS agg1,
avg(ss_list_price) AS agg2,
avg(ss_coupon_amt) AS agg3,
avg(ss_sales_price) AS 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 = 'S')
AND (cd_education_status = 'College')
AND (d_year = 2002)
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;
Q28
SELECT *
FROM
(
SELECT
avg(ss_list_price) AS B1_LP,
count(ss_list_price) AS B1_CNT,
count(DISTINCT ss_list_price) AS B1_CNTD
FROM store_sales
WHERE (ss_quantity BETWEEN 0 AND 5)
AND ((ss_list_price BETWEEN 8 AND 8 + 10)
OR (ss_coupon_amt BETWEEN 459 AND 459 + 1000)
OR (ss_wholesale_cost BETWEEN 57 AND 57 + 20))
) AS B1,
(
SELECT
avg(ss_list_price) AS B2_LP,
count(ss_list_price) AS B2_CNT,
count(DISTINCT ss_list_price) AS B2_CNTD
FROM store_sales
WHERE (ss_quantity BETWEEN 6 AND 10)
AND ((ss_list_price BETWEEN 90 AND 90 + 10)
OR (ss_coupon_amt BETWEEN 2323 AND 2323 + 1000)
OR (ss_wholesale_cost BETWEEN 31 AND 31 + 20))
) AS B2,
(
SELECT
avg(ss_list_price) AS B3_LP,
count(ss_list_price) AS B3_CNT,
count(DISTINCT ss_list_price) AS B3_CNTD
FROM store_sales
WHERE (ss_quantity BETWEEN 11 AND 15)
AND ((ss_list_price BETWEEN 142 AND 142 + 10)
OR (ss_coupon_amt BETWEEN 12214 AND 12214 + 1000)
OR (ss_wholesale_cost BETWEEN 79 AND 79 + 20))
) AS B3,
(
SELECT
avg(ss_list_price) AS B4_LP,
count(ss_list_price) AS B4_CNT,
count(DISTINCT ss_list_price) AS B4_CNTD
FROM store_sales
WHERE (ss_quantity BETWEEN 16 AND 20)
AND ((ss_list_price BETWEEN 135 AND 135 + 10)
OR (ss_coupon_amt BETWEEN 6071 AND 6071 + 1000)
OR (ss_wholesale_cost BETWEEN 38 AND 38 + 20))
) AS B4,
(
SELECT
avg(ss_list_price) AS B5_LP,
count(ss_list_price) AS B5_CNT,
count(DISTINCT ss_list_price) AS B5_CNTD
FROM store_sales
WHERE (ss_quantity BETWEEN 21 AND 25)
AND ((ss_list_price BETWEEN 122 AND 122 + 10)
OR (ss_coupon_amt BETWEEN 836 AND 836 + 1000)
OR (ss_wholesale_cost BETWEEN 17 AND 17 + 20))
) AS B5,
(
SELECT
avg(ss_list_price) AS B6_LP,
count(ss_list_price) AS B6_CNT,
count(DISTINCT ss_list_price) AS B6_CNTD
FROM store_sales
WHERE (ss_quantity BETWEEN 26 AND 30)
AND ((ss_list_price BETWEEN 154 AND 154 + 10)
OR (ss_coupon_amt BETWEEN 7326 AND 7326 + 1000)
OR (ss_wholesale_cost BETWEEN 7 AND 7 + 20))
) AS B6
LIMIT 100;
Q29
SELECT
i_item_id,
i_item_desc,
s_store_id,
s_store_name,
sum(ss_quantity) AS store_sales_quantity,
sum(sr_return_quantity) AS store_returns_quantity,
sum(cs_quantity) AS catalog_sales_quantity
FROM store_sales, store_returns, catalog_sales, date_dim AS d1, date_dim AS d2, date_dim AS d3, store, item
WHERE (d1.d_moy = 9)
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 9 AND 9 + 3)
AND (d2.d_year = 1999)
AND (sr_customer_sk = cs_bill_customer_sk)
AND (sr_item_sk = cs_item_sk)
AND (cs_sold_date_sk = d3.d_date_sk)
AND (d3.d_year IN (1999, 1999 + 1, 1999 + 2))
GROUP BY
i_item_id,
i_item_desc,
s_store_id,
s_store_name
ORDER BY
i_item_id,
i_item_desc,
s_store_id,
s_store_name
LIMIT 100;
Q30
WITH
customer_total_return AS
(
SELECT
wr_returning_customer_sk AS ctr_customer_sk,
ca_state AS ctr_state,
sum(wr_return_amt) AS ctr_total_return
FROM web_returns, date_dim, customer_address
WHERE (wr_returned_date_sk = d_date_sk)
AND (d_year = 2002)
AND (wr_returning_addr_sk = ca_address_sk)
GROUP BY
wr_returning_customer_sk,
ca_state
)
SELECT
c_customer_id,
c_salutation,
c_first_name,
c_last_name,
c_preferred_cust_flag,
c_birth_day,
c_birth_month,
c_birth_year,
c_birth_country,
c_login,
c_email_address,
c_last_review_date_sk,
ctr_total_return
FROM customer_total_return AS ctr1, customer_address, customer
WHERE (ctr1.ctr_total_return > (
SELECT avg(ctr_total_return) * 1.2
FROM customer_total_return AS ctr2
WHERE (ctr1.ctr_state = ctr2.ctr_state)
))
AND (ca_address_sk = c_current_addr_sk)
AND (ca_state = 'GA')
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;
Q31
WITH
ss AS
(
SELECT
ca_county,
d_qoy,
d_year,
sum(ss_ext_sales_price) AS store_sales
FROM store_sales, date_dim, customer_address
WHERE (ss_sold_date_sk = d_date_sk)
AND (ss_addr_sk = ca_address_sk)
GROUP BY ca_county, d_qoy, d_year
),
ws AS
(
SELECT
ca_county,
d_qoy,
d_year,
sum(ws_ext_sales_price) AS web_sales
FROM web_sales, date_dim, customer_address
WHERE (ws_sold_date_sk = d_date_sk)
AND (ws_bill_addr_sk = ca_address_sk)
GROUP BY ca_county, d_qoy, d_year
)
SELECT
ss1.ca_county,
ss1.d_year,
CAST(ws2.web_sales AS Float64) / ws1.web_sales AS web_q1_q2_increase,
CAST(ss2.store_sales AS Float64) / ss1.store_sales AS store_q1_q2_increase,
CAST(ws3.web_sales AS Float64) / ws2.web_sales AS web_q2_q3_increase,
CAST(ss3.store_sales AS Float64) / ss2.store_sales AS store_q2_q3_increase
FROM ss AS ss1, ss AS ss2, ss AS ss3, ws AS ws1, ws AS ws2, ws AS ws3
WHERE (ss1.d_qoy = 1)
AND (ss1.d_year = 2000)
AND (ss1.ca_county = ss2.ca_county)
AND (ss2.d_qoy = 2)
AND (ss2.d_year = 2000)
AND (ss2.ca_county = ss3.ca_county)
AND (ss3.d_qoy = 3)
AND (ss3.d_year = 2000)
AND (ss1.ca_county = ws1.ca_county)
AND (ws1.d_qoy = 1)
AND (ws1.d_year = 2000)
AND (ws1.ca_county = ws2.ca_county)
AND (ws2.d_qoy = 2)
AND (ws2.d_year = 2000)
AND (ws1.ca_county = ws3.ca_county)
AND (ws3.d_qoy = 3)
AND (ws3.d_year = 2000)
AND (CASE WHEN ws1.web_sales > 0 THEN CAST(ws2.web_sales AS Float64) / ws1.web_sales ELSE NULL END
> CASE WHEN ss1.store_sales > 0 THEN CAST(ss2.store_sales AS Float64) / ss1.store_sales ELSE NULL END)
AND (CASE WHEN ws2.web_sales > 0 THEN CAST(ws3.web_sales AS Float64) / ws2.web_sales ELSE NULL END
> CASE WHEN ss2.store_sales > 0 THEN CAST(ss3.store_sales AS Float64) / ss2.store_sales ELSE NULL END)
ORDER BY ss1.ca_county;
Q32
SELECT sum(cs_ext_discount_amt) AS "excess discount amount"
FROM catalog_sales, item, date_dim
WHERE (i_manufact_id = 977)
AND (i_item_sk = cs_item_sk)
AND (d_date BETWEEN '2000-01-27' AND (CAST('2000-01-27' AS date) + INTERVAL 90 DAY))
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 '2000-01-27' AND (CAST('2000-01-27' AS date) + INTERVAL 90 DAY))
AND (d_date_sk = cs_sold_date_sk)
))
LIMIT 100;
Q33
WITH
ss AS
(
SELECT
i_manufact_id,
sum(ss_ext_sales_price) AS 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 ('Electronics'))
))
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_manufact_id
),
cs AS
(
SELECT
i_manufact_id,
sum(cs_ext_sales_price) AS 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 ('Electronics'))
))
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_manufact_id
),
ws AS
(
SELECT
i_manufact_id,
sum(ws_ext_sales_price) AS 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 ('Electronics'))
))
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_manufact_id
)
SELECT
i_manufact_id,
sum(total_sales) AS total_sales
FROM
(
SELECT * FROM ss
UNION ALL
SELECT * FROM cs
UNION ALL
SELECT * FROM ws
) AS tmp1
GROUP BY i_manufact_id
ORDER BY total_sales
LIMIT 100;
Q34
SELECT
c_last_name,
c_first_name,
c_salutation,
c_preferred_cust_flag,
ss_ticket_number,
cnt
FROM
(
SELECT
ss_ticket_number,
ss_customer_sk,
count(*) AS cnt
FROM store_sales, date_dim, store, household_demographics
WHERE (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
AND (store_sales.ss_store_sk = store.s_store_sk)
AND (store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk)
AND ((date_dim.d_dom BETWEEN 1 AND 3) OR (date_dim.d_dom BETWEEN 25 AND 28))
AND ((household_demographics.hd_buy_potential = '>10000') OR (household_demographics.hd_buy_potential = 'Unknown'))
AND (household_demographics.hd_vehicle_count > 0)
AND ((CASE WHEN household_demographics.hd_vehicle_count > 0 THEN (household_demographics.hd_dep_count) / household_demographics.hd_vehicle_count ELSE NULL END) > 1.2)
AND (date_dim.d_year IN (1999, 1999 + 1, 1999 + 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
) AS dn, customer
WHERE (ss_customer_sk = c_customer_sk)
AND (cnt BETWEEN 15 AND 20)
ORDER BY c_last_name, c_first_name, c_salutation, c_preferred_cust_flag DESC, ss_ticket_number;
Q35
SELECT
ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
count(*) AS cnt1,
avg(cd_dep_count),
min(cd_dep_count),
min(cd_dep_count),
cd_dep_employed_count,
count(*) AS cnt2,
avg(cd_dep_employed_count),
min(cd_dep_employed_count),
min(cd_dep_employed_count),
cd_dep_college_count,
count(*) AS cnt3,
avg(cd_dep_college_count),
min(cd_dep_college_count),
min(cd_dep_college_count)
FROM customer AS c, customer_address AS 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 = 2002)
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 = 2002)
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 = 2002)
AND (d_qoy < 4)
)
)
GROUP BY
ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
ORDER BY
ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
LIMIT 100;
Q36
SELECT
sum(ss_net_profit) / sum(ss_ext_sales_price) AS gross_margin,
i_category,
i_class,
grouping(i_category) + grouping(i_class) AS lochierarchy,
rank() OVER (
PARTITION BY grouping(i_category) + grouping(i_class),
CASE WHEN grouping(i_class) = 0 THEN i_category END
ORDER BY (sum(ss_net_profit) * 1.) / sum(ss_ext_sales_price) ASC
) AS rank_within_parent
FROM store_sales, date_dim AS 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;
Q37
SELECT
i_item_id,
i_item_desc,
i_current_price
FROM item, inventory, date_dim, catalog_sales
WHERE (i_current_price BETWEEN 68 AND 68 + 30)
AND (inv_item_sk = i_item_sk)
AND (d_date_sk = inv_date_sk)
AND (d_date BETWEEN CAST('2000-02-01' AS date) AND (CAST('2000-02-01' AS date) + INTERVAL 60 DAY))
AND (i_manufact_id IN (677, 940, 694, 808))
AND (inv_quantity_on_hand BETWEEN 100 AND 500)
AND (cs_item_sk = i_item_sk)
GROUP BY i_item_id, i_item_desc, i_current_price
ORDER BY i_item_id
LIMIT 100;
Q38
SELECT count(*)
FROM
(
SELECT DISTINCT c_last_name, c_first_name, d_date
FROM store_sales, date_dim, customer
WHERE (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
AND (store_sales.ss_customer_sk = customer.c_customer_sk)
AND (d_month_seq BETWEEN 1200 AND 1200 + 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 1200 AND 1200 + 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 1200 AND 1200 + 11)
) AS hot_cust
LIMIT 100;
Q39
WITH
inv AS
(
SELECT
w_warehouse_name,
w_warehouse_sk,
i_item_sk,
d_moy,
stdev,
mean,
CASE mean WHEN 0 THEN NULL ELSE stdev / mean END AS cov
FROM
(
SELECT
w_warehouse_name,
w_warehouse_sk,
i_item_sk,
d_moy,
stddev_samp(inv_quantity_on_hand) AS stdev,
avg(inv_quantity_on_hand) AS 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
) AS 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 AS inv1, inv AS 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 AS cov
FROM
(
SELECT
w_warehouse_name,
w_warehouse_sk,
i_item_sk,
d_moy,
stddev_samp(inv_quantity_on_hand) AS stdev,
avg(inv_quantity_on_hand) AS 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
) AS 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 AS inv1, inv AS 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;
Q40
SELECT
w_state,
i_item_id,
sum(CASE WHEN (CAST(d_date AS date) < CAST('2000-03-11' 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('2000-03-11' 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('2000-03-11' AS date) - INTERVAL 30 DAY) AND (CAST('2000-03-11' AS date) + INTERVAL 30 DAY))
GROUP BY w_state, i_item_id
ORDER BY w_state, i_item_id
LIMIT 100;
Q41
SELECT DISTINCT(i_product_name)
FROM item AS i1
WHERE (i_manufact_id BETWEEN 738 AND 738 + 40)
AND (
SELECT count(*) AS item_cnt
FROM item
WHERE (
(i_manufact = i1.i_manufact)
AND (
(
(i_category = 'Women')
AND (i_color = 'powder' OR i_color = 'khaki')
AND (i_units = 'Ounce' OR i_units = 'Oz')
AND (i_size = 'medium' OR i_size = 'extra large')
)
OR (
(i_category = 'Women')
AND (i_color = 'brown' OR i_color = 'honeydew')
AND (i_units = 'Bunch' OR i_units = 'Ton')
AND (i_size = 'N/A' OR i_size = 'small')
)
OR (
(i_category = 'Men')
AND (i_color = 'floral' OR i_color = 'deep')
AND (i_units = 'N/A' OR i_units = 'Dozen')
AND (i_size = 'petite' OR i_size = 'large')
)
OR (
(i_category = 'Men')
AND (i_color = 'light' OR i_color = 'cornflower')
AND (i_units = 'Box' OR i_units = 'Pound')
AND (i_size = 'medium' OR i_size = 'extra large')
)
)
)
OR (
(i_manufact = i1.i_manufact)
AND (
(
(i_category = 'Women')
AND (i_color = 'midnight' OR i_color = 'snow')
AND (i_units = 'Pallet' OR i_units = 'Gross')
AND (i_size = 'medium' OR i_size = 'extra large')
)
OR (
(i_category = 'Women')
AND (i_color = 'cyan' OR i_color = 'papaya')
AND (i_units = 'Cup' OR i_units = 'Dram')
AND (i_size = 'N/A' OR i_size = 'small')
)
OR (
(i_category = 'Men')
AND (i_color = 'orange' OR i_color = 'frosted')
AND (i_units = 'Each' OR i_units = 'Tbl')
AND (i_size = 'petite' OR i_size = 'large')
)
OR (
(i_category = 'Men')
AND (i_color = 'forest' OR i_color = 'ghost')
AND (i_units = 'Lb' OR i_units = 'Bundle')
AND (i_size = 'medium' OR i_size = 'extra large')
)
)
)
) > 0
ORDER BY i_product_name
LIMIT 100;
Q42
SELECT
dt.d_year,
item.i_category_id,
item.i_category,
sum(ss_ext_sales_price)
FROM date_dim AS 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_category_id,
item.i_category
ORDER BY
sum(ss_ext_sales_price) DESC,
dt.d_year,
item.i_category_id,
item.i_category
LIMIT 100;
Q43
SELECT
s_store_name,
s_store_id,
sum(CASE WHEN (d_day_name = 'Sunday') THEN ss_sales_price ELSE NULL END) AS sun_sales,
sum(CASE WHEN (d_day_name = 'Monday') THEN ss_sales_price ELSE NULL END) AS mon_sales,
sum(CASE WHEN (d_day_name = 'Tuesday') THEN ss_sales_price ELSE NULL END) AS tue_sales,
sum(CASE WHEN (d_day_name = 'Wednesday') THEN ss_sales_price ELSE NULL END) AS wed_sales,
sum(CASE WHEN (d_day_name = 'Thursday') THEN ss_sales_price ELSE NULL END) AS thu_sales,
sum(CASE WHEN (d_day_name = 'Friday') THEN ss_sales_price ELSE NULL END) AS fri_sales,
sum(CASE WHEN (d_day_name = 'Saturday') THEN ss_sales_price ELSE NULL END) AS 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;
Q44
SELECT
asceding.rnk,
i1.i_product_name AS best_performing,
i2.i_product_name AS worst_performing
FROM
(
SELECT *
FROM
(
SELECT
item_sk,
rank() OVER (ORDER BY rank_col ASC) AS rnk
FROM
(
SELECT
ss_item_sk AS item_sk,
avg(ss_net_profit) AS rank_col
FROM store_sales AS ss1
WHERE (ss_store_sk = 4)
GROUP BY ss_item_sk
HAVING avg(ss_net_profit) > 0.9 * (
SELECT avg(ss_net_profit) AS rank_col
FROM store_sales
WHERE (ss_store_sk = 4) AND (ss_addr_sk IS NULL)
GROUP BY ss_store_sk
)
) AS V1
) AS V11
WHERE (rnk < 11)
) AS asceding,
(
SELECT *
FROM
(
SELECT
item_sk,
rank() OVER (ORDER BY rank_col DESC) AS rnk
FROM
(
SELECT
ss_item_sk AS item_sk,
avg(ss_net_profit) AS rank_col
FROM store_sales AS ss1
WHERE (ss_store_sk = 4)
GROUP BY ss_item_sk
HAVING avg(ss_net_profit) > 0.9 * (
SELECT avg(ss_net_profit) AS rank_col
FROM store_sales
WHERE (ss_store_sk = 4) AND (ss_addr_sk IS NULL)
GROUP BY ss_store_sk
)
) AS V2
) AS V21
WHERE (rnk < 11)
) AS descending,
item AS i1,
item AS i2
WHERE (asceding.rnk = descending.rnk)
AND (i1.i_item_sk = asceding.item_sk)
AND (i2.i_item_sk = descending.item_sk)
ORDER BY asceding.rnk
LIMIT 100;
Q45
SELECT
ca_zip,
ca_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 = 2001)
GROUP BY ca_zip, ca_city
ORDER BY ca_zip, ca_city
LIMIT 100;
Q46
SELECT
c_last_name,
c_first_name,
ca_city,
bought_city,
ss_ticket_number,
amt,
profit
FROM
(
SELECT
ss_ticket_number,
ss_customer_sk,
ca_city AS bought_city,
sum(ss_coupon_amt) AS amt,
sum(ss_net_profit) AS 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 = 4) OR (household_demographics.hd_vehicle_count = 3))
AND (date_dim.d_dow IN (6, 0))
AND (date_dim.d_year IN (1999, 1999 + 1, 1999 + 2))
AND (store.s_city IN ('Fairview', 'Midway', 'Fairview', 'Fairview', 'Fairview'))
GROUP BY ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city
) AS dn, customer, customer_address AS current_addr
WHERE (ss_customer_sk = c_customer_sk)
AND (customer.c_current_addr_sk = current_addr.ca_address_sk)
AND (current_addr.ca_city <> bought_city)
ORDER BY
c_last_name,
c_first_name,
ca_city,
bought_city,
ss_ticket_number
LIMIT 100;
Q47
WITH
v1 AS
(
SELECT
i_category,
i_brand,
s_store_name,
s_company_name,
d_year,
d_moy,
sum(ss_sales_price) AS sum_sales,
avg(sum(ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name, d_year) AS avg_monthly_sales,
rank() OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name ORDER BY d_year, d_moy) AS 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 = 1999)
OR ((d_year = 1999 - 1) AND (d_moy = 12))
OR ((d_year = 1999 + 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.d_moy,
v1.avg_monthly_sales,
v1.sum_sales,
v1_lag.sum_sales AS psum,
v1_lead.sum_sales AS nsum
FROM v1, v1 AS v1_lag, v1 AS 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 = 1999)
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, s_store_name
LIMIT 100;
Note
The query does not work out-of-the-box due to https://github.com/ClickHouse/ClickHouse/issues/94858. This alternative formulation with a minor fix works:
WITH
v1 AS
(
SELECT
i_category,
i_brand,
s_store_name,
s_company_name,
d_year,
d_moy,
sum(ss_sales_price) AS sum_sales,
avg(sum(ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name, d_year) AS avg_monthly_sales,
rank() OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name ORDER BY d_year, d_moy) AS 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 = 1999)
OR ((d_year = 1999 - 1) AND (d_moy = 12))
OR ((d_year = 1999 + 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.d_moy,
v1.avg_monthly_sales,
v1.sum_sales,
v1_lag.sum_sales AS psum,
v1_lead.sum_sales AS nsum
FROM v1, v1 AS v1_lag, v1 AS 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 (v1.d_year = 1999)
AND (v1.avg_monthly_sales > 0)
AND (CASE WHEN v1.avg_monthly_sales > 0 THEN abs(v1.sum_sales - v1.avg_monthly_sales) / v1.avg_monthly_sales ELSE NULL END > 0.1)
ORDER BY v1.sum_sales - v1.avg_monthly_sales, v1.s_store_name
LIMIT 100;
Q48
SELECT sum(ss_quantity)
FROM store_sales, store, customer_demographics, customer_address, date_dim
WHERE (s_store_sk = ss_store_sk)
AND (ss_sold_date_sk = d_date_sk)
AND (d_year = 2000)
AND (
(
(cd_demo_sk = ss_cdemo_sk)
AND (cd_marital_status = 'M')
AND (cd_education_status = '4 yr Degree')
AND (ss_sales_price BETWEEN 100.00 AND 150.00)
)
OR (
(cd_demo_sk = ss_cdemo_sk)
AND (cd_marital_status = 'D')
AND (cd_education_status = '2 yr Degree')
AND (ss_sales_price BETWEEN 50.00 AND 100.00)
)
OR (
(cd_demo_sk = ss_cdemo_sk)
AND (cd_marital_status = 'S')
AND (cd_education_status = 'College')
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 ('CO', 'OH', 'TX'))
AND (ss_net_profit BETWEEN 0 AND 2000)
)
OR (
(ss_addr_sk = ca_address_sk)
AND (ca_country = 'United States')
AND (ca_state IN ('OR', 'MN', 'KY'))
AND (ss_net_profit BETWEEN 150 AND 3000)
)
OR (
(ss_addr_sk = ca_address_sk)
AND (ca_country = 'United States')
AND (ca_state IN ('VA', 'CA', 'MS'))
AND (ss_net_profit BETWEEN 50 AND 25000)
)
);
Q49
SELECT
channel,
item,
return_ratio,
return_rank,
currency_rank
FROM
(
SELECT
'web' AS channel,
web.item,
web.return_ratio,
web.return_rank,
web.currency_rank
FROM
(
SELECT
item,
return_ratio,
currency_ratio,
rank() OVER (ORDER BY return_ratio) AS return_rank,
rank() OVER (ORDER BY currency_ratio) AS currency_rank
FROM
(
SELECT
ws.ws_item_sk AS item,
(CAST(sum(coalesce(wr.wr_return_quantity, 0)) AS decimal(15, 4)) / CAST(sum(coalesce(ws.ws_quantity, 0)) AS decimal(15, 4))) AS return_ratio,
(CAST(sum(coalesce(wr.wr_return_amt, 0)) AS decimal(15, 4)) / CAST(sum(coalesce(ws.ws_net_paid, 0)) AS decimal(15, 4))) AS currency_ratio
FROM web_sales AS ws
LEFT OUTER JOIN web_returns AS 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 = 2001)
AND (d_moy = 12)
GROUP BY ws.ws_item_sk
) AS in_web
) AS 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 AS cs
LEFT OUTER JOIN catalog_returns AS 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 = 2001)
AND (d_moy = 12)
GROUP BY cs.cs_item_sk
) AS in_cat
) AS 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 AS sts
LEFT OUTER JOIN store_returns AS 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 = 2001)
AND (d_moy = 12)
GROUP BY sts.ss_item_sk
) AS in_store
) AS store
WHERE (store.return_rank <= 10) OR (store.currency_rank <= 10)
)
ORDER BY 1, 4, 5, 2
LIMIT 100;
Q50
SELECT
s_store_name,
s_company_id,
s_street_number,
s_street_name,
s_street_type,
s_suite_number,
s_city,
s_county,
s_state,
s_zip,
sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1 ELSE 0 END) AS "30 days",
sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1 ELSE 0 END) AS "31-60 days",
sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1 ELSE 0 END) AS "61-90 days",
sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1 ELSE 0 END) AS "91-120 days",
sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1 ELSE 0 END) AS ">120 days"
FROM store_sales, store_returns, store, date_dim AS d1, date_dim AS d2
WHERE (d2.d_year = 2001)
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;
Q51
WITH
web_v1 AS
(
SELECT
ws_item_sk AS 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) AS cume_sales
FROM web_sales, date_dim
WHERE (ws_sold_date_sk = d_date_sk)
AND (d_month_seq BETWEEN 1200 AND 1200 + 11)
AND (ws_item_sk IS NOT NULL)
GROUP BY ws_item_sk, d_date
),
store_v1 AS
(
SELECT
ss_item_sk AS 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) AS cume_sales
FROM store_sales, date_dim
WHERE (ss_sold_date_sk = d_date_sk)
AND (d_month_seq BETWEEN 1200 AND 1200 + 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) AS web_cumulative,
max(store_sales) OVER (PARTITION BY item_sk ORDER BY d_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS store_cumulative
FROM
(
SELECT
CASE WHEN web.item_sk IS NOT NULL THEN web.item_sk ELSE store.item_sk END AS item_sk,
CASE WHEN web.d_date IS NOT NULL THEN web.d_date ELSE store.d_date END AS d_date,
web.cume_sales AS web_sales,
store.cume_sales AS store_sales
FROM web_v1 AS web
FULL OUTER JOIN store_v1 AS store ON (web.item_sk = store.item_sk) AND (web.d_date = store.d_date)
) AS x
) AS y
WHERE (web_cumulative > store_cumulative)
ORDER BY item_sk, d_date
LIMIT 100;
Q52
SELECT
dt.d_year,
item.i_brand_id AS brand_id,
item.i_brand AS brand,
sum(ss_ext_sales_price) AS ext_price
FROM date_dim AS 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;
Q53
SELECT *
FROM
(
SELECT
i_manufact_id,
sum(ss_sales_price) AS sum_sales,
avg(sum(ss_sales_price)) OVER (PARTITION BY i_manufact_id) AS 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 (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 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
) AS tmp1
WHERE (CASE WHEN avg_quarterly_sales > 0 THEN abs(sum_sales - avg_quarterly_sales) / avg_quarterly_sales ELSE NULL END > 0.1)
ORDER BY
avg_quarterly_sales,
sum_sales,
i_manufact_id
LIMIT 100;
Q54
WITH
my_customers AS
(
SELECT DISTINCT
c_customer_sk,
c_current_addr_sk
FROM
(
SELECT
cs_sold_date_sk AS sold_date_sk,
cs_bill_customer_sk AS customer_sk,
cs_item_sk AS item_sk
FROM catalog_sales
UNION ALL
SELECT
ws_sold_date_sk AS sold_date_sk,
ws_bill_customer_sk AS customer_sk,
ws_item_sk AS item_sk
FROM web_sales
) AS cs_or_ws_sales, item, date_dim, customer
WHERE (sold_date_sk = d_date_sk)
AND (item_sk = i_item_sk)
AND (i_category = 'Women')
AND (i_class = 'maternity')
AND (c_customer_sk = cs_or_ws_sales.customer_sk)
AND (d_moy = 12)
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 = 12)
) AND (
SELECT DISTINCT d_month_seq + 3
FROM date_dim
WHERE (d_year = 1998) AND (d_moy = 12)
))
GROUP BY c_customer_sk
),
segments AS
(
SELECT CAST((revenue / 50) AS int) AS segment
FROM my_revenue
)
SELECT
segment,
count(*) AS num_customers,
segment * 50 AS segment_base
FROM segments
GROUP BY segment
ORDER BY segment, num_customers
LIMIT 100;
Q55
SELECT
i_brand_id AS brand_id,
i_brand AS brand,
sum(ss_ext_sales_price) AS 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 = 28)
AND (d_moy = 11)
AND (d_year = 1999)
GROUP BY i_brand, i_brand_id
ORDER BY ext_price DESC, i_brand_id
LIMIT 100;
Q56
WITH
ss AS
(
SELECT
i_item_id,
sum(ss_ext_sales_price) AS 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 ('slate', 'blanched', 'burnished'))
))
AND (ss_item_sk = i_item_sk)
AND (ss_sold_date_sk = d_date_sk)
AND (d_year = 2001)
AND (d_moy = 2)
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) AS 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 ('slate', 'blanched', 'burnished'))
))
AND (cs_item_sk = i_item_sk)
AND (cs_sold_date_sk = d_date_sk)
AND (d_year = 2001)
AND (d_moy = 2)
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) AS 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 ('slate', 'blanched', 'burnished'))
))
AND (ws_item_sk = i_item_sk)
AND (ws_sold_date_sk = d_date_sk)
AND (d_year = 2001)
AND (d_moy = 2)
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) AS total_sales
FROM
(
SELECT * FROM ss
UNION ALL
SELECT * FROM cs
UNION ALL
SELECT * FROM ws
) AS tmp1
GROUP BY i_item_id
ORDER BY
total_sales,
i_item_id
LIMIT 100;
Q57
WITH
v1 AS
(
SELECT
i_category,
i_brand,
cc_name,
d_year,
d_moy,
sum(cs_sales_price) AS sum_sales,
avg(sum(cs_sales_price)) OVER (PARTITION BY i_category, i_brand, cc_name, d_year) AS avg_monthly_sales,
rank() OVER (PARTITION BY i_category, i_brand, cc_name ORDER BY d_year, d_moy) AS 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 = 1999)
OR ((d_year = 1999 - 1) AND (d_moy = 12))
OR ((d_year = 1999 + 1) AND (d_moy = 1))
)
GROUP BY i_category, i_brand, cc_name, d_year, d_moy
),
v2 AS
(
SELECT
v1.i_category,
v1.i_brand,
v1.cc_name,
v1.d_year,
v1.d_moy,
v1.avg_monthly_sales,
v1.sum_sales,
v1_lag.sum_sales AS psum,
v1_lead.sum_sales AS nsum
FROM v1, v1 AS v1_lag, v1 AS 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 = 1999)
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, cc_name
LIMIT 100;
Note
The query does not work out-of-the-box due to https://github.com/ClickHouse/ClickHouse/issues/94858. This alternative formulation with a minor fix works:
WITH
v1 AS
(
SELECT
i_category,
i_brand,
cc_name,
d_year,
d_moy,
sum(cs_sales_price) AS sum_sales,
avg(sum(cs_sales_price)) OVER (PARTITION BY i_category, i_brand, cc_name, d_year) AS avg_monthly_sales,
rank() OVER (PARTITION BY i_category, i_brand, cc_name ORDER BY d_year, d_moy) AS 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 = 1999)
OR ((d_year = 1999 - 1) AND (d_moy = 12))
OR ((d_year = 1999 + 1) AND (d_moy = 1))
)
GROUP BY i_category, i_brand, cc_name, d_year, d_moy
),
v2 AS
(
SELECT
v1.i_category,
v1.i_brand,
v1.cc_name,
v1.d_year,
v1.d_moy,
v1.avg_monthly_sales,
v1.sum_sales,
v1_lag.sum_sales AS psum,
v1_lead.sum_sales AS nsum
FROM v1, v1 AS v1_lag, v1 AS 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 (v1.d_year = 1999)
AND (v1.avg_monthly_sales > 0)
AND (CASE WHEN v1.avg_monthly_sales > 0 THEN abs(v1.sum_sales - v1.avg_monthly_sales) / v1.avg_monthly_sales ELSE NULL END > 0.1)
ORDER BY v1.sum_sales - v1.avg_monthly_sales, v1.cc_name
LIMIT 100;
Q58
WITH
ss_items AS
(
SELECT
i_item_id AS item_id,
sum(ss_ext_sales_price) AS 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-01-03')
))
))
AND (ss_sold_date_sk = d_date_sk)
GROUP BY i_item_id
),
cs_items AS
(
SELECT
i_item_id AS item_id,
sum(cs_ext_sales_price) AS 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-01-03')
))
))
AND (cs_sold_date_sk = d_date_sk)
GROUP BY i_item_id
),
ws_items AS
(
SELECT
i_item_id AS item_id,
sum(ws_ext_sales_price) AS 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-01-03')
))
))
AND (ws_sold_date_sk = d_date_sk)
GROUP BY i_item_id
)
SELECT
ss_items.item_id,
ss_item_rev,
ss_item_rev / ((CAST(ss_item_rev AS Float64) + cs_item_rev + ws_item_rev) / 3) * 100 AS ss_dev,
cs_item_rev,
cs_item_rev / ((CAST(ss_item_rev AS Float64) + cs_item_rev + ws_item_rev) / 3) * 100 AS cs_dev,
ws_item_rev,
ws_item_rev / ((CAST(ss_item_rev AS Float64) + cs_item_rev + ws_item_rev) / 3) * 100 AS ws_dev,
(ss_item_rev + cs_item_rev + ws_item_rev) / 3 AS 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;
Note
The query does not work out-of-the-box due to https://github.com/ClickHouse/ClickHouse/issues/94976. This alternative formulation with a minor fix works:
WITH
ss_items AS
(
SELECT
i_item_id AS item_id,
sum(ss_ext_sales_price) AS 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-01-03')
))
))
AND (ss_sold_date_sk = d_date_sk)
GROUP BY i_item_id
),
cs_items AS
(
SELECT
i_item_id AS item_id,
sum(cs_ext_sales_price) AS 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-01-03')
))
))
AND (cs_sold_date_sk = d_date_sk)
GROUP BY i_item_id
),
ws_items AS
(
SELECT
i_item_id AS item_id,
sum(ws_ext_sales_price) AS 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-01-03')
))
))
AND (ws_sold_date_sk = d_date_sk)
GROUP BY i_item_id
)
SELECT
ss_items.item_id,
ss_item_rev,
ss_item_rev / ((CAST(ss_item_rev AS Float64) + cs_item_rev + ws_item_rev) / 3) * 100 AS ss_dev,
cs_item_rev,
cs_item_rev / ((CAST(ss_item_rev AS Float64) + cs_item_rev + ws_item_rev) / 3) * 100 AS cs_dev,
ws_item_rev,
ws_item_rev / ((CAST(ss_item_rev AS Float64) + cs_item_rev + ws_item_rev) / 3) * 100 AS ws_dev,
(ss_item_rev + cs_item_rev + ws_item_rev) / 3 AS 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
ss_items.item_id,
ss_item_rev
LIMIT 100;
Q59
WITH
wss AS
(
SELECT
d_week_seq,
ss_store_sk,
sum(CASE WHEN (d_day_name = 'Sunday') THEN ss_sales_price ELSE NULL END) AS sun_sales,
sum(CASE WHEN (d_day_name = 'Monday') THEN ss_sales_price ELSE NULL END) AS mon_sales,
sum(CASE WHEN (d_day_name = 'Tuesday') THEN ss_sales_price ELSE NULL END) AS tue_sales,
sum(CASE WHEN (d_day_name = 'Wednesday') THEN ss_sales_price ELSE NULL END) AS wed_sales,
sum(CASE WHEN (d_day_name = 'Thursday') THEN ss_sales_price ELSE NULL END) AS thu_sales,
sum(CASE WHEN (d_day_name = 'Friday') THEN ss_sales_price ELSE NULL END) AS fri_sales,
sum(CASE WHEN (d_day_name = 'Saturday') THEN ss_sales_price ELSE NULL END) AS 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 AS s_store_name1,
wss.d_week_seq AS d_week_seq1,
s_store_id AS s_store_id1,
sun_sales AS sun_sales1,
mon_sales AS mon_sales1,
tue_sales AS tue_sales1,
wed_sales AS wed_sales1,
thu_sales AS thu_sales1,
fri_sales AS fri_sales1,
sat_sales AS sat_sales1
FROM wss, store, date_dim AS d
WHERE (d.d_week_seq = wss.d_week_seq)
AND (ss_store_sk = s_store_sk)
AND (d_month_seq BETWEEN 1212 AND 1212 + 11)
) AS y,
(
SELECT
s_store_name AS s_store_name2,
wss.d_week_seq AS d_week_seq2,
s_store_id AS s_store_id2,
sun_sales AS sun_sales2,
mon_sales AS mon_sales2,
tue_sales AS tue_sales2,
wed_sales AS wed_sales2,
thu_sales AS thu_sales2,
fri_sales AS fri_sales2,
sat_sales AS sat_sales2
FROM wss, store, date_dim AS d
WHERE (d.d_week_seq = wss.d_week_seq)
AND (ss_store_sk = s_store_sk)
AND (d_month_seq BETWEEN 1212 + 12 AND 1212 + 23)
) AS x
WHERE (s_store_id1 = s_store_id2)
AND (d_week_seq1 = d_week_seq2 - 52)
ORDER BY s_store_name1, s_store_id1, d_week_seq1
LIMIT 100;
Q60
WITH
ss AS
(
SELECT
i_item_id,
sum(ss_ext_sales_price) AS 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 ('Music'))
))
AND (ss_item_sk = i_item_sk)
AND (ss_sold_date_sk = d_date_sk)
AND (d_year = 1998)
AND (d_moy = 9)
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) AS 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 ('Music'))
))
AND (cs_item_sk = i_item_sk)
AND (cs_sold_date_sk = d_date_sk)
AND (d_year = 1998)
AND (d_moy = 9)
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) AS 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 ('Music'))
))
AND (ws_item_sk = i_item_sk)
AND (ws_sold_date_sk = d_date_sk)
AND (d_year = 1998)
AND (d_moy = 9)
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) AS total_sales
FROM
(
SELECT * FROM ss
UNION ALL
SELECT * FROM cs
UNION ALL
SELECT * FROM ws
) AS tmp1
GROUP BY i_item_id
ORDER BY
i_item_id,
total_sales
LIMIT 100;
Q61
SELECT
promotions,
total,
CAST(promotions AS decimal(15, 4)) / CAST(total AS decimal(15, 4)) * 100
FROM
(
SELECT sum(ss_ext_sales_price) AS 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 = -5)
AND (i_category = 'Jewelry')
AND ((p_channel_dmail = 'Y') OR (p_channel_email = 'Y') OR (p_channel_tv = 'Y'))
AND (s_gmt_offset = -5)
AND (d_year = 1998)
AND (d_moy = 11)
) AS promotional_sales,
(
SELECT sum(ss_ext_sales_price) AS 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 = -5)
AND (i_category = 'Jewelry')
AND (s_gmt_offset = -5)
AND (d_year = 1998)
AND (d_moy = 11)
) AS all_sales
ORDER BY promotions, total
LIMIT 100;
Q62
SELECT
substr(w_warehouse_name, 1, 20),
sm_type,
web_name,
sum(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk <= 30) THEN 1 ELSE 0 END) AS "30 days",
sum(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk > 30) AND (ws_ship_date_sk - ws_sold_date_sk <= 60) THEN 1 ELSE 0 END) AS "31-60 days",
sum(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk > 60) AND (ws_ship_date_sk - ws_sold_date_sk <= 90) THEN 1 ELSE 0 END) AS "61-90 days",
sum(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk > 90) AND (ws_ship_date_sk - ws_sold_date_sk <= 120) THEN 1 ELSE 0 END) AS "91-120 days",
sum(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk > 120) THEN 1 ELSE 0 END) AS ">120 days"
FROM web_sales, warehouse, ship_mode, web_site, date_dim
WHERE (d_month_seq BETWEEN 1200 AND 1200 + 11)
AND (ws_ship_date_sk = d_date_sk)
AND (ws_warehouse_sk = w_warehouse_sk)
AND (ws_ship_mode_sk = sm_ship_mode_sk)
AND (ws_web_site_sk = web_site_sk)
GROUP BY
substr(w_warehouse_name, 1, 20),
sm_type,
web_name
ORDER BY
substr(w_warehouse_name, 1, 20),
sm_type,
web_name
LIMIT 100;
Q63
SELECT *
FROM
(
SELECT
i_manager_id,
sum(ss_sales_price) AS sum_sales,
avg(sum(ss_sales_price)) OVER (PARTITION BY i_manager_id) AS 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 (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 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
) AS tmp1
WHERE (CASE WHEN avg_monthly_sales > 0 THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales ELSE NULL END > 0.1)
ORDER BY
i_manager_id,
avg_monthly_sales,
sum_sales
LIMIT 100;
Q64
WITH
cs_ui AS
(
SELECT
cs_item_sk,
sum(cs_ext_list_price) AS sale,
sum((cr_refunded_cash + cr_reversed_charge) + cr_store_credit) AS refund
FROM catalog_sales, catalog_returns
WHERE (cs_item_sk = cr_item_sk) AND (cs_order_number = cr_order_number)
GROUP BY cs_item_sk
HAVING sum(cs_ext_list_price) > (2 * sum((cr_refunded_cash + cr_reversed_charge) + cr_store_credit))
),
cross_sales AS
(
SELECT
i_product_name AS product_name,
i_item_sk AS item_sk,
s_store_name AS store_name,
s_zip AS store_zip,
ad1.ca_street_number AS b_street_number,
ad1.ca_street_name AS b_street_name,
ad1.ca_city AS b_city,
ad1.ca_zip AS b_zip,
ad2.ca_street_number AS c_street_number,
ad2.ca_street_name AS c_street_name,
ad2.ca_city AS c_city,
ad2.ca_zip AS c_zip,
d1.d_year AS syear,
d2.d_year AS fsyear,
d3.d_year AS s2year,
count(*) AS cnt,
sum(ss_wholesale_cost) AS s1,
sum(ss_list_price) AS s2,
sum(ss_coupon_amt) AS s3
FROM store_sales, store_returns, cs_ui, date_dim AS d1, date_dim AS d2, date_dim AS d3, store, customer, customer_demographics AS cd1, customer_demographics AS cd2, promotion, household_demographics AS hd1, household_demographics AS hd2, customer_address AS ad1, customer_address AS ad2, income_band AS ib1, income_band AS 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 ('purple', 'burlywood', 'indian', 'spring', 'floral', 'medium')) AND ((i_current_price >= 64) AND (i_current_price <= (64 + 10))) AND ((i_current_price >= (64 + 1)) AND (i_current_price <= (64 + 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 AS cs1, cross_sales AS cs2
WHERE (cs1.item_sk = cs2.item_sk) AND (cs1.syear = 1999) AND (cs2.syear = (1999 + 1)) AND (cs2.cnt <= cs1.cnt) AND (cs1.store_name = cs2.store_name) AND (cs1.store_zip = cs2.store_zip)
ORDER BY
cs1.product_name,
cs1.store_name,
cs2.cnt,
cs1.s1,
cs2.s1;
Q65
SELECT
s_store_name,
i_item_desc,
sc.revenue,
i_current_price,
i_wholesale_cost,
i_brand
FROM store, item,
(
SELECT
ss_store_sk,
avg(revenue) AS ave
FROM
(
SELECT
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) AS revenue
FROM store_sales, date_dim
WHERE ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1176 AND 1176+11
GROUP BY
ss_store_sk,
ss_item_sk
) AS sa
GROUP BY ss_store_sk
) AS 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 1176 AND 1176+11
GROUP BY
ss_store_sk,
ss_item_sk
) AS sc
WHERE (sb.ss_store_sk = sc.ss_store_sk) AND (sc.revenue <= (0.1 * sb.ave)) AND (s_store_sk = sc.ss_store_sk) AND (i_item_sk = sc.ss_item_sk)
ORDER BY
s_store_name,
i_item_desc
LIMIT 100;
Q66
SELECT
w_warehouse_name,
w_warehouse_sq_ft,
w_city,
w_county,
w_state,
w_country,
ship_carriers,
year,
sum(x.jan_sales) AS jan_sales,
sum(x.feb_sales) AS feb_sales,
sum(x.mar_sales) AS mar_sales,
sum(x.apr_sales) AS apr_sales,
sum(x.may_sales) AS may_sales,
sum(x.jun_sales) AS jun_sales,
sum(x.jul_sales) AS jul_sales,
sum(x.aug_sales) AS aug_sales,
sum(x.sep_sales) AS sep_sales,
sum(x.oct_sales) AS oct_sales,
sum(x.nov_sales) AS nov_sales,
sum(x.dec_sales) AS dec_sales,
sum(CAST(x.jan_sales AS Float64) / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
sum(CAST(x.feb_sales AS Float64) / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
sum(CAST(x.mar_sales AS Float64) / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
sum(CAST(x.apr_sales AS Float64) / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
sum(CAST(x.may_sales AS Float64) / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
sum(CAST(x.jun_sales AS Float64) / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
sum(CAST(x.jul_sales AS Float64) / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
sum(CAST(x.aug_sales AS Float64) / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
sum(CAST(x.sep_sales AS Float64) / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
sum(CAST(x.oct_sales AS Float64) / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
sum(CAST(x.nov_sales AS Float64) / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
sum(CAST(x.dec_sales AS Float64) / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
sum(x.jan_net) AS jan_net,
sum(x.feb_net) AS feb_net,
sum(x.mar_net) AS mar_net,
sum(x.apr_net) AS apr_net,
sum(x.may_net) AS may_net,
sum(x.jun_net) AS jun_net,
sum(x.jul_net) AS jul_net,
sum(x.aug_net) AS aug_net,
sum(x.sep_net) AS sep_net,
sum(x.oct_net) AS oct_net,
sum(x.nov_net) AS nov_net,
sum(x.dec_net) AS dec_net
FROM
(
SELECT
w_warehouse_name,
w_warehouse_sq_ft,
w_city,
w_county,
w_state,
w_country,
concat('DHL', ',', 'BARIAN') AS ship_carriers,
d_year AS year,
sum(multiIf(d_moy = 1, ws_ext_sales_price * ws_quantity, 0)) AS jan_sales,
sum(multiIf(d_moy = 2, ws_ext_sales_price * ws_quantity, 0)) AS feb_sales,
sum(multiIf(d_moy = 3, ws_ext_sales_price * ws_quantity, 0)) AS mar_sales,
sum(multiIf(d_moy = 4, ws_ext_sales_price * ws_quantity, 0)) AS apr_sales,
sum(multiIf(d_moy = 5, ws_ext_sales_price * ws_quantity, 0)) AS may_sales,
sum(multiIf(d_moy = 6, ws_ext_sales_price * ws_quantity, 0)) AS jun_sales,
sum(multiIf(d_moy = 7, ws_ext_sales_price * ws_quantity, 0)) AS jul_sales,
sum(multiIf(d_moy = 8, ws_ext_sales_price * ws_quantity, 0)) AS aug_sales,
sum(multiIf(d_moy = 9, ws_ext_sales_price * ws_quantity, 0)) AS sep_sales,
sum(multiIf(d_moy = 10, ws_ext_sales_price * ws_quantity, 0)) AS oct_sales,
sum(multiIf(d_moy = 11, ws_ext_sales_price * ws_quantity, 0)) AS nov_sales,
sum(multiIf(d_moy = 12, ws_ext_sales_price * ws_quantity, 0)) AS dec_sales,
sum(multiIf(d_moy = 1, ws_net_paid * ws_quantity, 0)) AS jan_net,
sum(multiIf(d_moy = 2, ws_net_paid * ws_quantity, 0)) AS feb_net,
sum(multiIf(d_moy = 3, ws_net_paid * ws_quantity, 0)) AS mar_net,
sum(multiIf(d_moy = 4, ws_net_paid * ws_quantity, 0)) AS apr_net,
sum(multiIf(d_moy = 5, ws_net_paid * ws_quantity, 0)) AS may_net,
sum(multiIf(d_moy = 6, ws_net_paid * ws_quantity, 0)) AS jun_net,
sum(multiIf(d_moy = 7, ws_net_paid * ws_quantity, 0)) AS jul_net,
sum(multiIf(d_moy = 8, ws_net_paid * ws_quantity, 0)) AS aug_net,
sum(multiIf(d_moy = 9, ws_net_paid * ws_quantity, 0)) AS sep_net,
sum(multiIf(d_moy = 10, ws_net_paid * ws_quantity, 0)) AS oct_net,
sum(multiIf(d_moy = 11, ws_net_paid * ws_quantity, 0)) AS nov_net,
sum(multiIf(d_moy = 12, ws_net_paid * ws_quantity, 0)) 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 30838 AND 30838 + 28800) AND (sm_carrier IN ('DHL', 'BARIAN'))
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,
concat('DHL', ',', 'BARIAN') AS ship_carriers,
d_year AS year,
sum(multiIf(d_moy = 1, cs_sales_price * cs_quantity, 0)) AS jan_sales,
sum(multiIf(d_moy = 2, cs_sales_price * cs_quantity, 0)) AS feb_sales,
sum(multiIf(d_moy = 3, cs_sales_price * cs_quantity, 0)) AS mar_sales,
sum(multiIf(d_moy = 4, cs_sales_price * cs_quantity, 0)) AS apr_sales,
sum(multiIf(d_moy = 5, cs_sales_price * cs_quantity, 0)) AS may_sales,
sum(multiIf(d_moy = 6, cs_sales_price * cs_quantity, 0)) AS jun_sales,
sum(multiIf(d_moy = 7, cs_sales_price * cs_quantity, 0)) AS jul_sales,
sum(multiIf(d_moy = 8, cs_sales_price * cs_quantity, 0)) AS aug_sales,
sum(multiIf(d_moy = 9, cs_sales_price * cs_quantity, 0)) AS sep_sales,
sum(multiIf(d_moy = 10, cs_sales_price * cs_quantity, 0)) AS oct_sales,
sum(multiIf(d_moy = 11, cs_sales_price * cs_quantity, 0)) AS nov_sales,
sum(multiIf(d_moy = 12, cs_sales_price * cs_quantity, 0)) AS dec_sales,
sum(multiIf(d_moy = 1, cs_net_paid_inc_tax * cs_quantity, 0)) AS jan_net,
sum(multiIf(d_moy = 2, cs_net_paid_inc_tax * cs_quantity, 0)) AS feb_net,
sum(multiIf(d_moy = 3, cs_net_paid_inc_tax * cs_quantity, 0)) AS mar_net,
sum(multiIf(d_moy = 4, cs_net_paid_inc_tax * cs_quantity, 0)) AS apr_net,
sum(multiIf(d_moy = 5, cs_net_paid_inc_tax * cs_quantity, 0)) AS may_net,
sum(multiIf(d_moy = 6, cs_net_paid_inc_tax * cs_quantity, 0)) AS jun_net,
sum(multiIf(d_moy = 7, cs_net_paid_inc_tax * cs_quantity, 0)) AS jul_net,
sum(multiIf(d_moy = 8, cs_net_paid_inc_tax * cs_quantity, 0)) AS aug_net,
sum(multiIf(d_moy = 9, cs_net_paid_inc_tax * cs_quantity, 0)) AS sep_net,
sum(multiIf(d_moy = 10, cs_net_paid_inc_tax * cs_quantity, 0)) AS oct_net,
sum(multiIf(d_moy = 11, cs_net_paid_inc_tax * cs_quantity, 0)) AS nov_net,
sum(multiIf(d_moy = 12, cs_net_paid_inc_tax * cs_quantity, 0)) 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 >= 30838) AND (t_time <= (30838 + 28800))) AND (sm_carrier IN ('DHL', 'BARIAN'))
GROUP BY
w_warehouse_name,
w_warehouse_sq_ft,
w_city,
w_county,
w_state,
w_country,
d_year
) AS x
GROUP BY
w_warehouse_name,
w_warehouse_sq_ft,
w_city,
w_county,
w_state,
w_country,
ship_carriers,
year
ORDER BY w_warehouse_name
LIMIT 100;
Q67
SELECT *
FROM
(
SELECT
i_category,
i_class,
i_brand,
i_product_name,
d_year,
d_qoy,
d_moy,
s_store_id,
sumsales,
rank() OVER (PARTITION BY i_category ORDER BY sumsales DESC) AS 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)) AS 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 1200 AND 1200 + 11)
GROUP BY
i_category,
i_class,
i_brand,
i_product_name,
d_year,
d_qoy,
d_moy,
s_store_id
WITH ROLLUP
) AS dw1
) AS dw2
WHERE (rk <= 100)
ORDER BY
i_category,
i_class,
i_brand,
i_product_name,
d_year,
d_qoy,
d_moy,
s_store_id,
sumsales,
rk
LIMIT 100;
Q68
SELECT
c_last_name,
c_first_name,
ca_city,
bought_city,
ss_ticket_number,
extended_price,
extended_tax,
list_price
FROM
(
SELECT
ss_ticket_number,
ss_customer_sk,
ca_city AS bought_city,
sum(ss_ext_sales_price) AS extended_price,
sum(ss_ext_list_price) AS list_price,
sum(ss_ext_tax) AS 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 >= 1) AND (date_dim.d_dom <= 2))
AND ((household_demographics.hd_dep_count = 4) OR (household_demographics.hd_vehicle_count = 3))
AND (date_dim.d_year IN (1999, 1999 + 1, 1999 + 2))
AND (store.s_city IN ('Midway', 'Fairview'))
GROUP BY
ss_ticket_number,
ss_customer_sk,
ss_addr_sk,
ca_city
) AS dn, customer, customer_address AS current_addr
WHERE (ss_customer_sk = c_customer_sk)
AND (customer.c_current_addr_sk = current_addr.ca_address_sk)
AND (current_addr.ca_city != bought_city)
ORDER BY
c_last_name,
ss_ticket_number
LIMIT 100;
Q69
SELECT
cd_gender,
cd_marital_status,
cd_education_status,
count(*) AS cnt1,
cd_purchase_estimate,
count(*) AS cnt2,
cd_credit_rating,
count(*) AS cnt3
FROM customer AS c, customer_address AS ca, customer_demographics
WHERE (c.c_current_addr_sk = ca.ca_address_sk)
AND (ca_state IN ('KY', 'GA', 'NM'))
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 = 2001)
AND (d_moy BETWEEN 4 AND 4 + 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 = 2001)
AND (d_moy BETWEEN 4 AND 4 + 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 = 2001)
AND (d_moy BETWEEN 4 AND 4 + 2)
)
)
GROUP BY
cd_gender,
cd_marital_status,
cd_education_status,
cd_purchase_estimate,
cd_credit_rating
ORDER BY
cd_gender,
cd_marital_status,
cd_education_status,
cd_purchase_estimate,
cd_credit_rating
LIMIT 100;
Q70
SELECT
sum(ss_net_profit) AS total_sum,
s_state,
s_county,
grouping(s_state) + grouping(s_county) AS lochierarchy,
rank() OVER (PARTITION BY grouping(s_state) + grouping(s_county), multiIf(grouping(s_county) = 0, s_state, NULL) ORDER BY sum(ss_net_profit) DESC) AS rank_within_parent
FROM store_sales, date_dim AS d1, store
WHERE ((d1.d_month_seq >= 1200) AND (d1.d_month_seq <= (1200 + 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 >= 1200) AND (d_month_seq <= (1200 + 11)))
AND (d_date_sk = ss_sold_date_sk)
AND (s_store_sk = ss_store_sk)
GROUP BY s_state
) AS tmp1
WHERE (ranking <= 5)
))
GROUP BY
s_state,
s_county
WITH ROLLUP
ORDER BY
lochierarchy DESC,
multiIf(lochierarchy = 0, s_state, NULL),
rank_within_parent
LIMIT 100;
Q71
SELECT
i_brand_id AS brand_id,
i_brand AS brand,
t_hour,
t_minute,
sum(ext_price) AS 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 = 1999)
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 = 1999)
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 = 1999)
) AS tmp, time_dim
WHERE (sold_item_sk = i_item_sk)
AND (i_manager_id = 1)
AND (time_sk = t_time_sk)
AND ((t_meal_time = 'breakfast') OR (t_meal_time = 'dinner'))
GROUP BY
i_brand,
i_brand_id,
t_hour,
t_minute
ORDER BY
ext_price DESC,
i_brand_id;
Q72
SELECT
i_item_desc,
w_warehouse_name,
d1.d_week_seq,
sum(multiIf(p_promo_sk IS NULL, 1, 0)) AS no_promo,
sum(multiIf(p_promo_sk IS NOT NULL, 1, 0)) AS promo,
count(*) AS total_cnt
FROM catalog_sales
INNER JOIN inventory ON cs_item_sk = inv_item_sk
INNER JOIN warehouse ON w_warehouse_sk = inv_warehouse_sk
INNER JOIN item ON i_item_sk = cs_item_sk
INNER JOIN customer_demographics ON cs_bill_cdemo_sk = cd_demo_sk
INNER JOIN household_demographics ON cs_bill_hdemo_sk = hd_demo_sk
INNER JOIN date_dim AS d1 ON cs_sold_date_sk = d1.d_date_sk
INNER JOIN date_dim AS d2 ON inv_date_sk = d2.d_date_sk
INNER JOIN date_dim AS d3 ON cs_ship_date_sk = d3.d_date_sk
LEFT JOIN promotion ON cs_promo_sk = p_promo_sk
LEFT 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 = '>10000')
AND (d1.d_year = 1999)
AND (cd_marital_status = 'D')
GROUP BY
i_item_desc,
w_warehouse_name,
d_week_seq
ORDER BY
total_cnt DESC,
i_item_desc,
w_warehouse_name,
d_week_seq
LIMIT 100;
Q73
SELECT
c_last_name,
c_first_name,
c_salutation,
c_preferred_cust_flag,
ss_ticket_number,
cnt
FROM
(
SELECT
ss_ticket_number,
ss_customer_sk,
count(*) AS 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 >= 1) AND (date_dim.d_dom <= 2))
AND ((household_demographics.hd_buy_potential = '>10000') OR (household_demographics.hd_buy_potential = 'Unknown'))
AND (household_demographics.hd_vehicle_count > 0)
AND (multiIf(household_demographics.hd_vehicle_count > 0, household_demographics.hd_dep_count / household_demographics.hd_vehicle_count, NULL) > 1)
AND (date_dim.d_year IN (1999, 1999 + 1, 1999 + 2))
AND (store.s_county IN ('Williamson County', 'Franklin Parish', 'Bronx County', 'Orange County'))
GROUP BY
ss_ticket_number,
ss_customer_sk
) AS dj, customer
WHERE (ss_customer_sk = c_customer_sk)
AND ((cnt >= 1) AND (cnt <= 5))
ORDER BY
cnt DESC,
c_last_name;
Q74
WITH
year_total AS
(
SELECT
c_customer_id AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
d_year AS year,
sum(ss_net_paid) AS year_total,
's' AS 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 AS customer_id,
c_first_name AS customer_first_name,
c_last_name AS customer_last_name,
d_year AS year,
sum(ws_net_paid) AS year_total,
'w' AS 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 AS t_s_firstyear, year_total AS t_s_secyear, year_total AS t_w_firstyear, year_total AS 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 (multiIf(t_w_firstyear.year_total > 0, t_w_secyear.year_total / t_w_firstyear.year_total, NULL) > multiIf(t_s_firstyear.year_total > 0, t_s_secyear.year_total / t_s_firstyear.year_total, NULL))
ORDER BY 1, 2, 3
LIMIT 100;
Q75
WITH
all_sales AS
(
SELECT
d_year,
i_brand_id,
i_class_id,
i_category_id,
i_manufact_id,
SUM(sales_cnt) AS sales_cnt,
SUM(sales_amt) AS sales_amt
FROM
(
SELECT
d_year,
i_brand_id,
i_class_id,
i_category_id,
i_manufact_id,
cs_quantity - COALESCE(cr_return_quantity, 0) AS sales_cnt,
cs_ext_sales_price - COALESCE(cr_return_amount, 0.) AS sales_amt
FROM catalog_sales
INNER JOIN item ON i_item_sk = cs_item_sk
INNER 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 = 'Books')
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.) AS sales_amt
FROM store_sales
INNER JOIN item ON i_item_sk = ss_item_sk
INNER 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 = 'Books')
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.) AS sales_amt
FROM web_sales
INNER JOIN item ON i_item_sk = ws_item_sk
INNER 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 = 'Books')
) AS 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 AS curr_yr, all_sales AS prev_yr
WHERE (curr_yr.i_brand_id = prev_yr.i_brand_id)
AND (curr_yr.i_class_id = prev_yr.i_class_id)
AND (curr_yr.i_category_id = prev_yr.i_category_id)
AND (curr_yr.i_manufact_id = prev_yr.i_manufact_id)
AND (curr_yr.d_year = 2002)
AND (prev_yr.d_year = (2002 - 1))
AND ((CAST(curr_yr.sales_cnt, 'DECIMAL(17, 2)') / CAST(prev_yr.sales_cnt, 'DECIMAL(17, 2)')) < 0.9)
ORDER BY
sales_cnt_diff,
sales_amt_diff
LIMIT 100;
Note
The query does not work out-of-the-box due to https://github.com/ClickHouse/ClickHouse/issues/94671. This alternative formulation with a minor fix works:
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, CAST('0.0', 'Decimal(7, 2)')) AS sales_amt
FROM catalog_sales
INNER JOIN item ON i_item_sk = cs_item_sk
INNER 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 = 'Books')
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, CAST('0.0', 'Decimal(7, 2)')) AS sales_amt
FROM store_sales
INNER JOIN item ON i_item_sk = ss_item_sk
INNER 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 = 'Books')
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, CAST('0.0', 'Decimal(7, 2)')) AS sales_amt
FROM web_sales
INNER JOIN item ON i_item_sk = ws_item_sk
INNER 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 = 'Books')
) AS 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 AS curr_yr, all_sales AS prev_yr
WHERE (curr_yr.i_brand_id = prev_yr.i_brand_id)
AND (curr_yr.i_class_id = prev_yr.i_class_id)
AND (curr_yr.i_category_id = prev_yr.i_category_id)
AND (curr_yr.i_manufact_id = prev_yr.i_manufact_id)
AND (curr_yr.d_year = 2002)
AND (prev_yr.d_year = (2002 - 1))
AND ((CAST(curr_yr.sales_cnt, 'DECIMAL(17, 2)') / CAST(prev_yr.sales_cnt, 'DECIMAL(17, 2)')) < 0.9)
ORDER BY
sales_cnt_diff,
sales_amt_diff
LIMIT 100;
Q76
SELECT
channel,
col_name,
d_year,
d_qoy,
i_category,
COUNT(*) AS sales_cnt,
SUM(ext_sales_price) AS sales_amt
FROM
(
SELECT
'store' AS channel,
'ss_store_sk' AS col_name,
d_year,
d_qoy,
i_category,
ss_ext_sales_price AS ext_sales_price
FROM store_sales, item, date_dim
WHERE (ss_store_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_customer_sk' AS col_name,
d_year,
d_qoy,
i_category,
ws_ext_sales_price AS ext_sales_price
FROM web_sales, item, date_dim
WHERE (ws_ship_customer_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_ship_addr_sk' AS col_name,
d_year,
d_qoy,
i_category,
cs_ext_sales_price AS ext_sales_price
FROM catalog_sales, item, date_dim
WHERE (cs_ship_addr_sk IS NULL)
AND (cs_sold_date_sk = d_date_sk)
AND (cs_item_sk = i_item_sk)
) AS foo
GROUP BY
channel,
col_name,
d_year,
d_qoy,
i_category
ORDER BY
channel,
col_name,
d_year,
d_qoy,
i_category
LIMIT 100;
Q77
WITH
ss AS
(
SELECT
s_store_sk,
sum(ss_ext_sales_price) AS sales,
sum(ss_net_profit) AS profit
FROM store_sales, date_dim, store
WHERE (ss_sold_date_sk = d_date_sk)
AND ((d_date >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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) AS returns,
profit - coalesce(profit_loss, 0) AS profit
FROM ws
LEFT JOIN wr ON ws.wp_web_page_sk = wr.wp_web_page_sk
) AS x
GROUP BY
channel,
id
WITH ROLLUP
ORDER BY
channel,
id
LIMIT 100;
Q78
WITH
ws AS
(
SELECT
d_year AS ws_sold_year,
ws_item_sk,
ws_bill_customer_sk AS ws_customer_sk,
sum(ws_quantity) AS ws_qty,
sum(ws_wholesale_cost) AS ws_wc,
sum(ws_sales_price) AS ws_sp
FROM web_sales
LEFT JOIN web_returns ON (wr_order_number = ws_order_number) AND (ws_item_sk = wr_item_sk)
INNER 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 AS cs_customer_sk,
sum(cs_quantity) AS cs_qty,
sum(cs_wholesale_cost) AS cs_wc,
sum(cs_sales_price) AS cs_sp
FROM catalog_sales
LEFT JOIN catalog_returns ON (cr_order_number = cs_order_number) AND (cs_item_sk = cr_item_sk)
INNER 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) AS ss_qty,
sum(ss_wholesale_cost) AS ss_wc,
sum(ss_sales_price) AS ss_sp
FROM store_sales
LEFT JOIN store_returns ON (sr_ticket_number = ss_ticket_number) AND (ss_item_sk = sr_item_sk)
INNER JOIN date_dim ON ss_sold_date_sk = d_date_sk
WHERE sr_ticket_number IS NULL
GROUP BY
d_year,
ss_item_sk,
ss_customer_sk
)
SELECT
ss_sold_year,
ss_item_sk,
ss_customer_sk,
round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) AS ratio,
ss_qty AS store_qty,
ss_wc AS store_wholesale_cost,
ss_sp AS store_sales_price,
coalesce(ws_qty, 0) + coalesce(cs_qty, 0) AS other_chan_qty,
coalesce(ws_wc, 0) + coalesce(cs_wc, 0) AS other_chan_wholesale_cost,
coalesce(ws_sp, 0) + coalesce(cs_sp, 0) AS other_chan_sales_price
FROM ss
LEFT JOIN ws ON (ws_sold_year = ss_sold_year) AND (ws_item_sk = ss_item_sk) AND (ws_customer_sk = ss_customer_sk)
LEFT JOIN cs ON (cs_sold_year = ss_sold_year) AND (cs_item_sk = ss_item_sk) AND (cs_customer_sk = ss_customer_sk)
WHERE ((coalesce(ws_qty, 0) > 0) OR (coalesce(cs_qty, 0) > 0)) AND (ss_sold_year = 2000)
ORDER BY
ss_sold_year,
ss_item_sk,
ss_customer_sk,
ss_qty DESC,
ss_wc DESC,
ss_sp DESC,
other_chan_qty,
other_chan_wholesale_cost,
other_chan_sales_price,
ratio
LIMIT 100;
Q79
SELECT
c_last_name,
c_first_name,
substr(s_city, 1, 30),
ss_ticket_number,
amt,
profit
FROM
(
SELECT
ss_ticket_number,
ss_customer_sk,
store.s_city,
sum(ss_coupon_amt) AS amt,
sum(ss_net_profit) AS 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 = 6) OR (household_demographics.hd_vehicle_count > 2))
AND (date_dim.d_dow = 1)
AND (date_dim.d_year IN (1999, 1999 + 1, 1999 + 2))
AND ((store.s_number_employees >= 200) AND (store.s_number_employees <= 295))
GROUP BY
ss_ticket_number,
ss_customer_sk,
ss_addr_sk,
store.s_city
) AS ms, customer
WHERE (ss_customer_sk = c_customer_sk)
ORDER BY
c_last_name,
c_first_name,
substr(s_city, 1, 30),
profit
LIMIT 100;
Q80
WITH
ssr AS
(
SELECT
s_store_id AS store_id,
sum(ss_ext_sales_price) AS sales,
sum(coalesce(sr_return_amt, 0)) AS returns,
sum(ss_net_profit - coalesce(sr_net_loss, 0)) AS profit
FROM store_sales
LEFT 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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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 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 >= CAST('2000-08-23', 'date')) AND (d_date <= (CAST('2000-08-23', 'date') + INTERVAL 30 DAY)))
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,
concat('store', store_id) AS id,
sales,
returns,
profit
FROM ssr
UNION ALL
SELECT
'catalog channel' AS channel,
concat('catalog_page', catalog_page_id) AS id,
sales,
returns,
profit
FROM csr
UNION ALL
SELECT
'web channel' AS channel,
concat('web_site', web_site_id) AS id,
sales,
returns,
profit
FROM wsr
) AS x
GROUP BY
channel,
id
WITH ROLLUP
ORDER BY
channel,
id
LIMIT 100;
Q81
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 = 2000)
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 AS ctr1, customer_address, customer
WHERE (ctr1.ctr_total_return > (
SELECT avg(ctr_total_return) * 1.2
FROM customer_total_return AS ctr2
WHERE (ctr1.ctr_state = ctr2.ctr_state)
))
AND (ca_address_sk = c_current_addr_sk)
AND (ca_state = 'GA')
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;
Q82
SELECT
i_item_id,
i_item_desc,
i_current_price
FROM item, inventory, date_dim, store_sales
WHERE (i_current_price BETWEEN 62 AND 62 + 30)
AND (inv_item_sk = i_item_sk)
AND (d_date_sk = inv_date_sk)
AND (d_date BETWEEN CAST('2000-05-25', 'date') AND (CAST('2000-05-25', 'date') + INTERVAL 60 DAY))
AND (i_manufact_id IN (129, 270, 821, 423))
AND (inv_quantity_on_hand BETWEEN 100 AND 500)
AND (ss_item_sk = i_item_sk)
GROUP BY
i_item_id,
i_item_desc,
i_current_price
ORDER BY i_item_id
LIMIT 100;
Q83
WITH
sr_items AS
(
SELECT
i_item_id AS item_id,
sum(sr_return_quantity) AS 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-06-30', '2000-09-27', '2000-11-17'))
))
))
AND (sr_returned_date_sk = d_date_sk)
GROUP BY i_item_id
),
cr_items AS
(
SELECT
i_item_id AS item_id,
sum(cr_return_quantity) AS 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-06-30', '2000-09-27', '2000-11-17'))
))
))
AND (cr_returned_date_sk = d_date_sk)
GROUP BY i_item_id
),
wr_items AS
(
SELECT
i_item_id AS item_id,
sum(wr_return_quantity) AS 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-06-30', '2000-09-27', '2000-11-17'))
))
))
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.) * 100 AS sr_dev,
cr_item_qty,
((cr_item_qty / ((sr_item_qty + cr_item_qty) + wr_item_qty)) / 3.) * 100 AS cr_dev,
wr_item_qty,
((wr_item_qty / ((sr_item_qty + cr_item_qty) + wr_item_qty)) / 3.) * 100 AS wr_dev,
((sr_item_qty + cr_item_qty) + wr_item_qty) / 3. AS average
FROM sr_items, cr_items, wr_items
WHERE (sr_items.item_id = cr_items.item_id)
AND (sr_items.item_id = wr_items.item_id)
ORDER BY
sr_items.item_id,
sr_item_qty
LIMIT 100;
Q84
SELECT
c_customer_id AS customer_id,
concat(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 = 'Edgewood')
AND (c_current_addr_sk = ca_address_sk)
AND (ib_lower_bound >= 38128)
AND (ib_upper_bound <= (38128 + 50000))
AND (ib_income_band_sk = hd_income_band_sk)
AND (cd_demo_sk = c_current_cdemo_sk)
AND (hd_demo_sk = c_current_hdemo_sk)
AND (sr_cdemo_sk = cd_demo_sk)
ORDER BY c_customer_id
LIMIT 100;
Q85
SELECT
substr(r_reason_desc, 1, 20),
avg(ws_quantity),
avg(wr_refunded_cash),
avg(wr_fee)
FROM web_sales, web_returns, web_page, customer_demographics AS cd1, customer_demographics AS 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 = 2000)
AND (cd1.cd_demo_sk = wr_refunded_cdemo_sk)
AND (cd2.cd_demo_sk = wr_returning_cdemo_sk)
AND (ca_address_sk = wr_refunded_addr_sk)
AND (r_reason_sk = wr_reason_sk)
AND (
(
(cd1.cd_marital_status = 'M')
AND (cd1.cd_marital_status = cd2.cd_marital_status)
AND (cd1.cd_education_status = 'Advanced Degree')
AND (cd1.cd_education_status = cd2.cd_education_status)
AND ((ws_sales_price >= 100.0) AND (ws_sales_price <= 150.0))
)
OR (
(cd1.cd_marital_status = 'S')
AND (cd1.cd_marital_status = cd2.cd_marital_status)
AND (cd1.cd_education_status = 'College')
AND (cd1.cd_education_status = cd2.cd_education_status)
AND ((ws_sales_price >= 50.0) AND (ws_sales_price <= 100.0))
)
OR (
(cd1.cd_marital_status = 'W')
AND (cd1.cd_marital_status = cd2.cd_marital_status)
AND (cd1.cd_education_status = '2 yr Degree')
AND (cd1.cd_education_status = cd2.cd_education_status)
AND ((ws_sales_price >= 150.0) AND (ws_sales_price <= 200.0))
)
)
AND (
(
(ca_country = 'United States')
AND (ca_state IN ('IN', 'OH', 'NJ'))
AND ((ws_net_profit >= 100) AND (ws_net_profit <= 200))
)
OR (
(ca_country = 'United States')
AND (ca_state IN ('WI', 'CT', 'KY'))
AND ((ws_net_profit >= 150) AND (ws_net_profit <= 300))
)
OR (
(ca_country = 'United States')
AND (ca_state IN ('LA', 'IA', 'AR'))
AND ((ws_net_profit >= 50) AND (ws_net_profit <= 250))
)
)
GROUP BY r_reason_desc
ORDER BY
substr(r_reason_desc, 1, 20),
avg(ws_quantity),
avg(wr_refunded_cash),
avg(wr_fee)
LIMIT 100;
Q86
SELECT
sum(ws_net_paid) AS total_sum,
i_category,
i_class,
grouping(i_category) + grouping(i_class) AS lochierarchy,
rank() OVER (PARTITION BY grouping(i_category) + grouping(i_class), multiIf(grouping(i_class) = 0, i_category, NULL) ORDER BY sum(ws_net_paid) DESC) AS rank_within_parent
FROM web_sales, date_dim AS d1, item
WHERE ((d1.d_month_seq >= 1200) AND (d1.d_month_seq <= (1200 + 11)))
AND (d1.d_date_sk = ws_sold_date_sk)
AND (i_item_sk = ws_item_sk)
GROUP BY
i_category,
i_class
WITH ROLLUP
ORDER BY
lochierarchy DESC,
multiIf(lochierarchy = 0, i_category, NULL),
rank_within_parent
LIMIT 100;
Q87
SELECT count(*)
FROM
(
SELECT DISTINCT
c_last_name,
c_first_name,
d_date
FROM store_sales, date_dim, customer
WHERE (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
AND (store_sales.ss_customer_sk = customer.c_customer_sk)
AND ((d_month_seq >= 1200) AND (d_month_seq <= (1200 + 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 >= 1200) AND (d_month_seq <= (1200 + 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 >= 1200) AND (d_month_seq <= (1200 + 11)))
) AS cool_cust;
Q88
SELECT *
FROM
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s1,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s2,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s3,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s4,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s5,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s6,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s7,
(
SELECT count(*) AS 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 = 4) AND (household_demographics.hd_vehicle_count <= (4 + 2)))
OR ((household_demographics.hd_dep_count = 2) AND (household_demographics.hd_vehicle_count <= (2 + 2)))
OR ((household_demographics.hd_dep_count = 0) AND (household_demographics.hd_vehicle_count <= (0 + 2)))
)
AND (store.s_store_name = 'ese')
) AS s8;
Q89
SELECT *
FROM
(
SELECT
i_category,
i_class,
i_brand,
s_store_name,
s_company_name,
d_moy,
sum(ss_sales_price) AS sum_sales,
avg(sum(ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name) AS 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 (1999))
AND (
((i_category IN ('Women', 'Jewelry', 'Men')) AND (i_class IN ('dresses', 'birdal', 'shirts')))
OR ((i_category IN ('Sports', 'Electronics', 'Books')) AND (i_class IN ('football', 'stereo', 'computers')))
)
GROUP BY
i_category,
i_class,
i_brand,
s_store_name,
s_company_name,
d_moy
) AS tmp1
WHERE (multiIf(avg_monthly_sales != 0, abs(sum_sales - avg_monthly_sales) / avg_monthly_sales, NULL) > 0.1)
ORDER BY
sum_sales - avg_monthly_sales,
s_store_name
LIMIT 100;
Q90
SELECT CAST(amc AS DECIMAL(15, 4)) / CAST(pmc AS DECIMAL(15, 4)) AS am_pm_ratio
FROM
(
SELECT count(*) AS 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 8 AND 8 + 1)
AND (household_demographics.hd_dep_count = 6)
AND (web_page.wp_char_count BETWEEN 5000 AND 5200)
) AS at,
(
SELECT count(*) AS 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 19 AND 19 + 1)
AND (household_demographics.hd_dep_count = 6)
AND (web_page.wp_char_count BETWEEN 5000 AND 5200)
) AS pt
ORDER BY am_pm_ratio
LIMIT 100;
Q91
SELECT
cc_call_center_id AS Call_Center,
cc_name AS Call_Center_Name,
cc_manager AS Manager,
sum(cr_net_loss) AS 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 = 1998)
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 = -7)
GROUP BY
cc_call_center_id,
cc_name,
cc_manager,
cd_marital_status,
cd_education_status
ORDER BY sum(cr_net_loss) DESC;
Q92
SELECT sum(ws_ext_discount_amt) AS `Excess Discount Amount`
FROM web_sales, item, date_dim
WHERE (i_manufact_id = 350)
AND (i_item_sk = ws_item_sk)
AND ((d_date >= '2000-01-27') AND (d_date <= (CAST('2000-01-27', 'date') + INTERVAL 90 DAY)))
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 >= '2000-01-27') AND (d_date <= (CAST('2000-01-27', 'date') + INTERVAL 90 DAY)))
AND (d_date_sk = ws_sold_date_sk)
))
ORDER BY sum(ws_ext_discount_amt)
LIMIT 100;
Q93
SELECT
ss_customer_sk,
sum(act_sales) AS sumsales
FROM
(
SELECT
ss_item_sk,
ss_ticket_number,
ss_customer_sk,
multiIf(sr_return_quantity IS NOT NULL, (ss_quantity - sr_return_quantity) * ss_sales_price, ss_quantity * ss_sales_price) AS act_sales
FROM store_sales
LEFT 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 = 'reason 28')
) AS t
GROUP BY ss_customer_sk
ORDER BY
sumsales,
ss_customer_sk
LIMIT 100;
Q94
SELECT
count(DISTINCT ws_order_number) AS "order count",
sum(ws_ext_ship_cost) AS "total shipping cost",
sum(ws_net_profit) AS "total net profit"
FROM web_sales AS ws1, date_dim, customer_address, web_site
WHERE (d_date BETWEEN '1999-2-01' AND (CAST('1999-2-01', 'date') + INTERVAL 60 DAY))
AND (ws1.ws_ship_date_sk = d_date_sk)
AND (ws1.ws_ship_addr_sk = ca_address_sk)
AND (ca_state = 'IL')
AND (ws1.ws_web_site_sk = web_site_sk)
AND (web_company_name = 'pri')
AND EXISTS (
SELECT *
FROM web_sales AS 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 AS wr1
WHERE (ws1.ws_order_number = wr1.wr_order_number)
)
ORDER BY count(DISTINCT ws_order_number)
LIMIT 100;
Q95
WITH
ws_wh AS
(
SELECT
ws1.ws_order_number,
ws1.ws_warehouse_sk AS wh1,
ws2.ws_warehouse_sk AS wh2
FROM web_sales AS ws1, web_sales AS ws2
WHERE (ws1.ws_order_number = ws2.ws_order_number)
AND (ws1.ws_warehouse_sk != ws2.ws_warehouse_sk)
)
SELECT
countDistinct(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 AS ws1, date_dim, customer_address, web_site
WHERE ((d_date >= '1999-2-01') AND (d_date <= (CAST('1999-2-01', 'date') + INTERVAL 60 DAY)))
AND (ws1.ws_ship_date_sk = d_date_sk)
AND (ws1.ws_ship_addr_sk = ca_address_sk)
AND (ca_state = 'IL')
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 countDistinct(ws_order_number)
LIMIT 100;
Q96
SELECT count(*)
FROM store_sales, household_demographics, time_dim, store
WHERE (ss_sold_time_sk = time_dim.t_time_sk)
AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
AND (ss_store_sk = s_store_sk)
AND (time_dim.t_hour = 20)
AND (time_dim.t_minute >= 30)
AND (household_demographics.hd_dep_count = 7)
AND (store.s_store_name = 'ese')
ORDER BY count(*)
LIMIT 100;
Q97
WITH
ssci AS
(
SELECT
ss_customer_sk AS customer_sk,
ss_item_sk AS item_sk
FROM store_sales, date_dim
WHERE (ss_sold_date_sk = d_date_sk)
AND (d_month_seq BETWEEN 1200 AND 1200 + 11)
GROUP BY
ss_customer_sk,
ss_item_sk
),
csci AS
(
SELECT
cs_bill_customer_sk AS customer_sk,
cs_item_sk AS item_sk
FROM catalog_sales, date_dim
WHERE (cs_sold_date_sk = d_date_sk)
AND (d_month_seq BETWEEN 1200 AND 1200 + 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) AS store_only,
sum(CASE WHEN (ssci.customer_sk IS NULL) AND (csci.customer_sk IS NOT NULL) THEN 1 ELSE 0 END) AS catalog_only,
sum(CASE WHEN (ssci.customer_sk IS NOT NULL) AND (csci.customer_sk IS NOT NULL) THEN 1 ELSE 0 END) AS store_and_catalog
FROM ssci
FULL OUTER JOIN csci ON (ssci.customer_sk = csci.customer_sk) AND (ssci.item_sk = csci.item_sk)
LIMIT 100;
Q98
SELECT
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price,
sum(ss_ext_sales_price) AS itemrevenue,
sum(ss_ext_sales_price) * 100 / sum(sum(ss_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio
FROM store_sales, item, date_dim
WHERE (ss_item_sk = i_item_sk)
AND (i_category IN ('Sports', 'Books', 'Home'))
AND (ss_sold_date_sk = d_date_sk)
AND (d_date BETWEEN CAST('1999-02-22', 'date') AND (CAST('1999-02-22', 'date') + INTERVAL 30 DAY))
GROUP BY
i_item_id,
i_item_desc,
i_category,
i_class,
i_current_price
ORDER BY
i_category,
i_class,
i_item_id,
i_item_desc,
revenueratio;
Q99
SELECT
substr(w_warehouse_name, 1, 20),
sm_type,
cc_name,
sum(CASE WHEN (cs_ship_date_sk - cs_sold_date_sk <= 30) THEN 1 ELSE 0 END) AS "30 days",
sum(CASE WHEN (cs_ship_date_sk - cs_sold_date_sk > 30) AND (cs_ship_date_sk - cs_sold_date_sk <= 60) THEN 1 ELSE 0 END) AS "31-60 days",
sum(CASE WHEN (cs_ship_date_sk - cs_sold_date_sk > 60) AND (cs_ship_date_sk - cs_sold_date_sk <= 90) THEN 1 ELSE 0 END) AS "61-90 days",
sum(CASE WHEN (cs_ship_date_sk - cs_sold_date_sk > 90) AND (cs_ship_date_sk - cs_sold_date_sk <= 120) THEN 1 ELSE 0 END) AS "91-120 days",
sum(CASE WHEN (cs_ship_date_sk - cs_sold_date_sk > 120) THEN 1 ELSE 0 END) AS ">120 days"
FROM catalog_sales, warehouse, ship_mode, call_center, date_dim
WHERE (d_month_seq BETWEEN 1200 AND 1200 + 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;
