-- start query 6 using
template query55.tpl
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 = 49
and d_moy = 11
and d_year = 2001
group
by i_brand, i_brand_id
order
by ext_price desc, i_brand_id limit 38100;
-- end query 6
-- start query 9 using
template query7.tpl
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
= 'F' and
cd_marital_status
= 'M' and
cd_education_status
= 'College' and
(p_channel_email
= 'N' or p_channel_event = 'N') and
d_year
= 2001
group by
i_item_id;
-- end query 9
-- start query 12 using
template query48.tpl
select sum
(ss_quantity)
from store_sales, store, customer_demographics,
customer_address, date_dim
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk and d_year = 2002
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)
or
(cd_demo_sk = ss_cdemo_sk
and
cd_marital_status
= 'M'
and
cd_education_status
= 'Advanced Degree'
and
ss_sales_price between 50.00 and 100.00)
or
(cd_demo_sk = ss_cdemo_sk
and
cd_marital_status
= 'M'
and
cd_education_status
= 'Advanced Degree'
and
ss_sales_price between 150.00 and 200.00)
)
and
(
(ss_addr_sk
= ca_address_sk
and
ca_country
= '
and
ca_state in ('MS', 'MS', 'WV')
and ss_net_profit between 0 and 2000
)
or
(ss_addr_sk
= ca_address_sk
and
ca_country
= '
and
ca_state in ('TX', 'TN', 'MI')
and
ss_net_profit between 150 and 3000)
or
(ss_addr_sk
= ca_address_sk
and
ca_country
= '
and
ca_state in ('PA', 'AR', 'SC')
and
ss_net_profit between 50 and 25000)
);
-- end query 12
-- start query 16 using
template query15.tpl
select ca_zip,
sum(cs_sales_price) as sum_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 = 1998
group by
ca_zip;
-- end query 16
-- start query 17 using
template query37.tpl
select i_item_id,
i_item_desc, i_current_price
from
item, inventory, date_dim, catalog_sales
where i_current_price between 62 and 62 + 30
and inv_item_sk = i_item_sk
and d_date_sk=inv_date_sk
and d_date between cast('2001-02-12' as date)
and (cast('2001-02-12' as date) + 60
)
and i_manufact_id in (824,711,989,704)
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;
-- end query 17
-- start query 19 using
template query26.tpl
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
= 'F' and
cd_marital_status
= 'M' and
cd_education_status
= 'Unknown' and
(p_channel_email
= 'N' or p_channel_event = 'N') and
d_year
= 2000
group by
i_item_id;
-- end query 19
-- start query 23 using
template query52.tpl
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 dt,store_sales,item
where dt.d_date_sk = store_sales.ss_sold_date_sk
and store_sales.ss_item_sk = item.i_item_sk
and item.i_manager_id = 1
and dt.d_moy=12
and dt.d_year=2000
group by
dt.d_year, item.i_brand, item.i_brand_id
order by
dt.d_year, ext_price desc, brand_id;
-- end query 23
-- start query 24 using
template query45.tpl
select ca_zip,
sum(ws_sales_price) as sum_sales_price
from web_sales, customer, customer_address,
date_dim, item
where ws_bill_customer_sk = c_customer_sk
and c_current_addr_sk = ca_address_sk
and ws_item_sk = i_item_sk
and ( substr(ca_zip,1,5) in
('85669','86197','88274','83405',
'86475', '85392', '85460', '80348',
'81792')
or
i_item_id
in
(select i_item_id
from item
where i_item_sk in (2, 3, 5, 7,
11, 13, 17, 19, 23, 29)
)
)
and ws_sold_date_sk = d_date_sk
and d_qoy = 2 and d_year = 2000
group by
ca_zip;
-- end query 24
-- start query 25 using
template query91.tpl
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 = 2001
and d_moy = 12
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 '1001-5000%'
and ca_gmt_offset =
-6
group by cc_call_center_id, cc_name, cc_manager, cd_marital_status,
cd_education_status
order by sum(cr_net_loss) desc;
-- end query 25
-- start query 28 using
template query96.tpl
select count(*) as count_sales
from store_sales,
household_demographics, time_dim,
store
where ss_sold_time_sk
= time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 8
and time_dim.t_minute >= 30
and household_demographics.hd_dep_count = 3
and store.s_store_name = 'ese';
-- end query 28
-- start query 30 using
template query40.tpl
select w_state, i_item_id,
sum (case when (cast(d_date as date) < cast
('1999-06-18' as date))
then cs_sales_price - coalesce(cr_refunded_cash,0)
else 0
end) as sales_before,
sum(case when (cast(d_date as date) >= cast
('1999-06-18' as date))
then cs_sales_price - coalesce(cr_refunded_cash,0)
else 0
end) as sales_after
from catalog_sales left outer join catalog_returns
on
(cs_order_number
= cr_order_number
and
cs_item_sk = cr_item_sk), warehouse,
item, date_dim
where i_current_price between 0.99 and 1.49
and i_item_sk = cs_item_sk
and cs_warehouse_sk = w_warehouse_sk
and cs_sold_date_sk = d_date_sk
and d_date between (cast ('1999-06-18' as date) -
30 )
and
(cast ('1999-06-18' as date) + 30 )
group by
w_state,i_item_id
order by
w_state,i_item_id
limit
38100;
-- end query 30
-- start query 33 using
template query75.tpl
select i_item_id,
i_item_desc, i_current_price
from
item, inventory, date_dim
where i_current_price between 63 and 63+30
and inv_item_sk = i_item_sk
and d_date_sk=inv_date_sk
and d_date between cast('2000-06-27' as date)
and (cast('2000-06-27' as date) +
60)
and i_manufact_id in
(987,5,553,426)
and inv_quantity_on_hand between
100 and 500
group by
i_item_id,i_item_desc,i_current_price
order by
i_item_id;
-- end query 33
-- start query 36 using
template query13.tpl
select avg(ss_quantity) as avg_quantity,
avg(ss_ext_sales_price) as avg_sales_price,
avg(ss_ext_wholesale_cost) as avg_wholesale_cost,
sum(ss_ext_wholesale_cost) as
sum_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
= 'College'
and
ss_sales_price between 100.00 and 150.00
and
hd_dep_count
= 3)
or
(ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status
= 'S'
and
cd_education_status
= 'Secondary'
and
ss_sales_price between 50.00 and 100.00
and
hd_dep_count
= 1)
or
(ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status
= 'D'
and
cd_education_status
= '4 yr Degree'
and
ss_sales_price between 150.00 and 200.00
and
hd_dep_count
= 1)
)
and
(
(ss_addr_sk
= ca_address_sk
and
ca_country
= '
and
ca_state in ('NE', 'MO', 'NV')
and ss_net_profit between 100 and 200)
or
(ss_addr_sk
= ca_address_sk
and
ca_country
= '
and
ca_state in ('NY', 'NC', 'KS')
and ss_net_profit between 150 and 300)
or
(ss_addr_sk
= ca_address_sk
and
ca_country
= '
and
ca_state in ('GA', 'AL', 'NJ')
and ss_net_profit between 50 and 250)
);
-- end query 36
-- start query 37 using
template query25.tpl
select i_item_id,
i_item_desc, s_store_id, s_store_name,
sum(ss_net_profit) as
store_sales_profit,
sum(sr_net_loss) as store_returns_loss,
sum(cs_net_profit) as catalog_sales_profit
from store_sales, store_returns, catalog_sales, date_dim d1,
date_dim d2, date_dim d3, store, item
where d1.d_moy
= 4
and d1.d_year = 2002
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and ss_customer_sk = sr_customer_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and sr_returned_date_sk =
d2.d_date_sk
and d2.d_moy between 4 and
4 +6
and d2.d_year = 2002
and sr_customer_sk = cs_bill_customer_sk
and sr_item_sk = cs_item_sk
and cs_sold_date_sk = d3.d_date_sk
and d3.d_moy between 4 and
4 +6
and d3.d_year = 2002
group by
i_item_id, i_item_desc, s_store_id, s_store_name
order by
i_item_id, i_item_desc, s_store_id, s_store_name;
-- end query 37
-- start query 38 using
template query43.tpl
select s_store_name,
s_store_id,
sum
(case when (d_day_name='Sunday') then ss_sales_price
else null end) 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
= 1999
group by
s_store_name, s_store_id;
-- end query 38
-- start query 43 using
template query67.tpl
select s_state,
i_item_id,
sum(case when (cast(d_date as date) < cast
('1999-10-27' as date))
then ss_net_paid - coalesce(sr_net_loss,0)
else 0 end)
as sales_before,
sum(case when (cast(d_date as
date) >= cast ('1999-10-27' as date))
then ss_net_paid - coalesce(sr_net_loss,0)
else 0 end)
as sales_after
from store_sales left outer join store_returns
on
(ss_ticket_number
= sr_ticket_number
and
ss_item_sk = sr_item_sk),
store, item, date_dim
where i_rec_start_date = cast ('1999-10-27' as date)
and i_item_sk = ss_item_sk
and ss_store_sk = s_store_sk
and ss_sold_date_sk = d_date_sk
and d_date between (cast
('1999-10-27' as date) - 90)
and
(cast ('1999-10-27' as date) + 90)
group by
s_state, i_item_id;
-- end query 43
-- start query 47 using
template query3.tpl
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 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 = 356
and dt.d_moy=12
group by
dt.d_year, item.i_brand, item.i_brand_id
order by
dt.d_year, ext_price desc, brand_id;
-- end query 47
-- start query 49 using
template query29.tpl
Select i_item_id, i_item_desc,
s_store_id, s_store_name,
sum(ss_quantity)
as store_sales_quantity
sum(sr_return_quantity)
as store_returns_quantity
sum(cs_quantity)
as catalog_sales_quantity
from store_sales, store_returns,
catalog_sales, date_dim d1,
date_dim d2, date_dim d3, store, item
where d1.d_moy = 4
and d1.d_year = 1998
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and ss_customer_sk = sr_customer_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and sr_returned_date_sk =
d2.d_date_sk
and d2.d_moy between 4 and 4 + 3
and d2.d_year = 1998
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 (1998,1998+1,1998+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;
-- end query 49
-- start query 50 using
template query76.tpl
select i_item_id,
avg(ws_quantity)
as agg1,
avg(ws_list_price)
as agg2,
avg(ws_coupon_amt)
as agg3,
avg(ws_sales_price)
as agg4
from web_sales, customer_demographics,
date_dim, item, promotion
where ws_sold_date_sk = d_date_sk and
ws_item_sk
= i_item_sk and
ws_bill_cdemo_sk
= cd_demo_sk and
ws_promo_sk
= p_promo_sk and
cd_gender
= 'F' and
cd_marital_status
= 'M' and
cd_education_status
= 'Advanced Degree' and
(p_channel_email
= 'N' or p_channel_event = 'N') and
d_year
= 1999
group by
i_item_id;
-- end query 50
-- start query 54 using
template query91.tpl
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 = 1999
and d_moy = 12
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 '1001-5000%'
and ca_gmt_offset = -6
group by cc_call_center_id, cc_name, cc_manager, cd_marital_status,
cd_education_status
order by sum(cr_net_loss) desc;
-- end query 54
-- start query 57 using
template query96.tpl
select count(*) as count_sales
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 = 2
and store.s_store_name = 'ese';
-- end query 57
-- start query 59 using
template query17.tpl
Select i_item_id, i_item_desc,
s_state,
count(ss_quantity) as
store_sales_quantitycount
avg(ss_quantity) as store_sales_quantityave
stddev(ss_quantity) as store_sales_quantitystdev
stddev(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(sr_return_quantity) as store_returns_quantitystdev
stddev(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(cs_quantity)/avg(cs_quantity)
as catalog_sales_quantitystdev
stddev(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
from store_sales, store_returns, catalog_sales, date_dim d1,
date_dim d2, date_dim d3, store, item
where
d1.d_quarter_name = '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;
-- end query 59
-- start query 61 using
template query19.tpl
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=33
and d_moy=12
and d_year=2001
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;
-- end query 61
-- start query 69 using
template query42.tpl
select dt.d_year,
item.i_category_id, item.i_category,
avg(ss_ext_sales_price) as avg_ext_sales_price
from date_dim dt, store_sales,
item
where dt.d_date_sk = store_sales.ss_sold_date_sk
and store_sales.ss_item_sk = item.i_item_sk
and item.i_manager_id = 1
and dt.d_moy=11
and dt.d_year=1998
group by
dt.d_year, item.i_category_id,
item.i_category
order by
dt.d_year, item.i_category_id,
item.i_category;
-- end query 69
-- start query 75 using
template query99.tpl
select substring(w_warehouse_name,1,20)
as substr_warehouse,
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 extract (year from d_date) = 2001
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
substring(w_warehouse_name,1,20), sm_type, cc_name;
-- end query 75
-- start query 87 using
template query99.tpl
Select substring(w_warehouse_name,1,20) as substr_warehouse,
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 extract (year from d_date) = 2002
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
substring(w_warehouse_name,1,20), sm_type, cc_name;
-- end query 87