-- start query 16 in stream 0 using template query16.tpl
select top 100
count(distinct cs_order_number) as "order count"
,sum(cs_ext_ship_cost) as "total shipping cost"
,sum(cs_net_profit) as "total net profit"
from
catalog_sales cs1
,date_dim
,customer_address
,call_center
where
d_date between '2002-4-01' and
(date_add(cast('2002-4-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 = 'PA'
and cs1.cs_call_center_sk = cc_call_center_sk
and cc_county in ('Williamson County','Williamson County','Williamson County','Williamson County',
'Williamson County'
)
and exists (select *
from catalog_sales cs2
where cs1.cs_order_number = cs2.cs_order_number
and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
and not exists(select *
from catalog_returns cr1
where cs1.cs_order_number = cr1.cr_order_number)
order by count(distinct cs_order_number)
;
-- start query 17 in stream 0 using template query17.tpl
select top 100 i_item_id
,i_item_desc
,s_state
,count(ss_quantity) as store_sales_quantitycount
,avg(ss_quantity) as store_sales_quantityave
,stddev_samp(ss_quantity) as store_sales_quantitystdev
,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
,count(sr_return_quantity) as store_returns_quantitycount
,avg(sr_return_quantity) as store_returns_quantityave
,stddev_samp(sr_return_quantity) as store_returns_quantitystdev
,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
,stddev_samp(cs_quantity) as catalog_sales_quantitystdev
,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
from store_sales
,store_returns
,catalog_sales
,date_dim d1
,date_dim d2
,date_dim d3
,store
,item
where d1.d_quarter_name = '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
;
-- start query 18 in stream 0 using template query18.tpl
select top 100 i_item_id,
ca_country,
ca_state,
ca_county,
avg( cast(cs_quantity as decimal(12,2))) agg1,
avg( cast(cs_list_price as decimal(12,2))) agg2,
avg( cast(cs_coupon_amt as decimal(12,2))) agg3,
avg( cast(cs_sales_price as decimal(12,2))) agg4,
avg( cast(cs_net_profit as decimal(12,2))) agg5,
avg( cast(c_birth_year as decimal(12,2))) agg6,
avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7
from catalog_sales, customer_demographics cd1,
customer_demographics cd2, customer, customer_address, date_dim, item
where cs_sold_date_sk = d_date_sk and
cs_item_sk = i_item_sk and
cs_bill_cdemo_sk = cd1.cd_demo_sk and
cs_bill_customer_sk = c_customer_sk and
cd1.cd_gender = 'F' and
cd1.cd_education_status = 'Primary' and
c_current_cdemo_sk = cd2.cd_demo_sk and
c_current_addr_sk = ca_address_sk and
c_birth_month in (1,3,7,11,10,4) and
d_year = 2001 and
ca_state in ('AL','MO','TN'
,'GA','MT','IN','CA')
group by rollup (i_item_id, ca_country, ca_state, ca_county)
order by ca_country,
ca_state,
ca_county,
i_item_id
;
-- start query 19 in stream 0 using template query19.tpl
select top 100 i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
sum(ss_ext_sales_price) ext_price
from date_dim, store_sales, item,customer,customer_address,store
where d_date_sk = ss_sold_date_sk
and ss_item_sk = i_item_sk
and i_manager_id=14
and d_moy=11
and d_year=2002
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
;
-- start query 20 in stream 0 using template query20.tpl
select top 100 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 ('Books', 'Music', 'Sports')
and cs_sold_date_sk = d_date_sk
and d_date between cast('2002-06-18' as date)
and (date_add(cast('2002-06-18' 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
;
-- start query 21 in stream 0 using template query21.tpl
select top 100 *
from(select w_warehouse_name
,i_item_id
,sum(case when (cast(d_date as date) < cast ('1999-06-22' as date))
then inv_quantity_on_hand
else 0 end) as inv_before
,sum(case when (cast(d_date as date) >= cast ('1999-06-22' 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 (date_sub(cast ('1999-06-22' as date),interval 30 day))
and (date_add(cast('1999-06-22' as date),interval 30 day))
group by w_warehouse_name, i_item_id) x
where (case when inv_before > 0
then inv_after / inv_before
else null
end) between 2.0/3.0 and 3.0/2.0
order by w_warehouse_name
,i_item_id
;
-- start query 22 in stream 0 using template query22.tpl
select top 100 i_product_name
,i_brand
,i_class
,i_category
,avg(inv_quantity_on_hand) qoh
from inventory
,date_dim
,item
where inv_date_sk=d_date_sk
and inv_item_sk=i_item_sk
and d_month_seq between 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
;
-- start query 23 in stream 0 using template query23.tpl
with frequent_ss_items as
(select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
from store_sales
,date_dim
,item
where ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and d_year in (2000,2000+1,2000+2,2000+3)
group by substr(i_item_desc,1,30),i_item_sk,d_date
having count(*) >4),
max_store_sales as
(select max(csales) tpcds_cmax
from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
from store_sales
,customer
,date_dim
where ss_customer_sk = c_customer_sk
and ss_sold_date_sk = d_date_sk
and d_year in (2000,2000+1,2000+2,2000+3)
group by c_customer_sk)),
best_ss_customer as
(select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
from store_sales
,customer
where ss_customer_sk = c_customer_sk
group by c_customer_sk
having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select
*
from
max_store_sales))
select top 100 sum(sales)
from (select cs_quantity*cs_list_price sales
from catalog_sales
,date_dim
where d_year = 2000
and d_moy = 7
and cs_sold_date_sk = d_date_sk
and cs_item_sk in (select item_sk from frequent_ss_items)
and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
union all
select ws_quantity*ws_list_price sales
from web_sales
,date_dim
where d_year = 2000
and d_moy = 7
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))
;
-- start query 24 in stream 0 using template query24.tpl
with ssales as
(select c_last_name
,c_first_name
,s_store_name
,ca_state
,s_state
,i_color
,i_current_price
,i_manager_id
,i_units
,i_size
,sum(ss_net_paid) 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=5
group by c_last_name
,c_first_name
,s_store_name
,ca_state
,s_state
,i_color
,i_current_price
,i_manager_id
,i_units
,i_size)
select c_last_name
,c_first_name
,s_store_name
,sum(netpaid) paid
from ssales
where i_color = 'aquamarine'
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
;
-- start query 25 in stream 0 using template query25.tpl
select top 100
i_item_id
,i_item_desc
,s_store_id
,s_store_name
,max(ss_net_profit) as store_sales_profit
,max(sr_net_loss) as store_returns_loss
,max(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 = 1999
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and ss_customer_sk = sr_customer_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and sr_returned_date_sk = d2.d_date_sk
and d2.d_moy between 4 and 10
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_moy between 4 and 10
and d3.d_year = 1999
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
;
-- start query 26 in stream 0 using template query26.tpl
select top 100 i_item_id,
avg(cs_quantity) agg1,
avg(cs_list_price) agg2,
avg(cs_coupon_amt) agg3,
avg(cs_sales_price) agg4
from catalog_sales, customer_demographics, date_dim, item, promotion
where cs_sold_date_sk = d_date_sk and
cs_item_sk = i_item_sk and
cs_bill_cdemo_sk = cd_demo_sk and
cs_promo_sk = p_promo_sk and
cd_gender = 'M' and
cd_marital_status = 'W' and
cd_education_status = 'Unknown' and
(p_channel_email = 'N' or p_channel_event = 'N') and
d_year = 2002
group by i_item_id
order by i_item_id
;
-- start query 27 in stream 0 using template query27.tpl
select top 100 i_item_id,
s_state, grouping(s_state) g_state,
avg(ss_quantity) agg1,
avg(ss_list_price) agg2,
avg(ss_coupon_amt) agg3,
avg(ss_sales_price) agg4
from store_sales, customer_demographics, date_dim, store, item
where ss_sold_date_sk = d_date_sk and
ss_item_sk = i_item_sk and
ss_store_sk = s_store_sk and
ss_cdemo_sk = cd_demo_sk and
cd_gender = 'M' and
cd_marital_status = 'W' and
cd_education_status = 'Secondary' and
d_year = 1999 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
;
-- start query 28 in stream 0 using template query28.tpl
select top 100 *
from (select avg(ss_list_price) B1_LP
,count(ss_list_price) B1_CNT
,count(distinct ss_list_price) B1_CNTD
from store_sales
where ss_quantity between 0 and 5
and (ss_list_price between 107 and 107+10
or ss_coupon_amt between 1319 and 1319+1000
or ss_wholesale_cost between 60 and 60+20)) B1,
(select avg(ss_list_price) B2_LP
,count(ss_list_price) B2_CNT
,count(distinct ss_list_price) B2_CNTD
from store_sales
where ss_quantity between 6 and 10
and (ss_list_price between 23 and 23+10
or ss_coupon_amt between 825 and 825+1000
or ss_wholesale_cost between 43 and 43+20)) B2,
(select avg(ss_list_price) B3_LP
,count(ss_list_price) B3_CNT
,count(distinct ss_list_price) B3_CNTD
from store_sales
where ss_quantity between 11 and 15
and (ss_list_price between 74 and 74+10
or ss_coupon_amt between 4381 and 4381+1000
or ss_wholesale_cost between 57 and 57+20)) B3,
(select avg(ss_list_price) B4_LP
,count(ss_list_price) B4_CNT
,count(distinct ss_list_price) B4_CNTD
from store_sales
where ss_quantity between 16 and 20
and (ss_list_price between 89 and 89+10
or ss_coupon_amt between 3117 and 3117+1000
or ss_wholesale_cost between 68 and 68+20)) B4,
(select avg(ss_list_price) B5_LP
,count(ss_list_price) B5_CNT
,count(distinct ss_list_price) B5_CNTD
from store_sales
where ss_quantity between 21 and 25
and (ss_list_price between 58 and 58+10
or ss_coupon_amt between 9402 and 9402+1000
or ss_wholesale_cost between 38 and 38+20)) B5,
(select avg(ss_list_price) B6_LP
,count(ss_list_price) B6_CNT
,count(distinct ss_list_price) B6_CNTD
from store_sales
where ss_quantity between 26 and 30
and (ss_list_price between 64 and 64+10
or ss_coupon_amt between 5792 and 5792+1000
or ss_wholesale_cost between 73 and 73+20)) B6
;
-- start query 29 in stream 0 using template query29.tpl
select top 100
i_item_id
,i_item_desc
,s_store_id
,s_store_name
,max(ss_quantity) as store_sales_quantity
,max(sr_return_quantity) as store_returns_quantity
,max(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
;
-- start query 30 in stream 0 using template query30.tpl
with customer_total_return as
(select wr_returning_customer_sk as ctr_customer_sk
,ca_state as ctr_state,
sum(wr_return_amt) as ctr_total_return
from web_returns
,date_dim
,customer_address
where wr_returned_date_sk = d_date_sk
and d_year =2000
and wr_returning_addr_sk = ca_address_sk
group by wr_returning_customer_sk
,ca_state)
select top 100 c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag
,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
,c_last_review_date_sk,ctr_total_return
from customer_total_return ctr1
,customer_address
,customer
where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
from customer_total_return ctr2
where ctr1.ctr_state = ctr2.ctr_state)
and ca_address_sk = c_current_addr_sk
and ca_state = 'AR'
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
;
-- start query 31 in stream 0 using template query31.tpl
with ss as
(select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
from store_sales,date_dim,customer_address
where ss_sold_date_sk = d_date_sk
and ss_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year),
ws as
(select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
from web_sales,date_dim,customer_address
where ws_sold_date_sk = d_date_sk
and ws_bill_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year)
select
ss1.ca_county
,ss1.d_year
,ws2.web_sales/ws1.web_sales web_q1_q2_increase
,ss2.store_sales/ss1.store_sales store_q1_q2_increase
,ws3.web_sales/ws2.web_sales web_q2_q3_increase
,ss3.store_sales/ss2.store_sales store_q2_q3_increase
from
ss ss1
,ss ss2
,ss ss3
,ws ws1
,ws ws2
,ws ws3
where
ss1.d_qoy = 1
and ss1.d_year = 1999
and ss1.ca_county = ss2.ca_county
and ss2.d_qoy = 2
and ss2.d_year = 1999
and ss2.ca_county = ss3.ca_county
and ss3.d_qoy = 3
and ss3.d_year = 1999
and ss1.ca_county = ws1.ca_county
and ws1.d_qoy = 1
and ws1.d_year = 1999
and ws1.ca_county = ws2.ca_county
and ws2.d_qoy = 2
and ws2.d_year = 1999
and ws1.ca_county = ws3.ca_county
and ws3.d_qoy = 3
and ws3.d_year =1999
and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end
> case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end
and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end
> case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end
order by store_q2_q3_increase;
-- start query 32 in stream 0 using template query32.tpl
select top 100 sum(cs_ext_discount_amt) as "excess discount amount"
from
catalog_sales
,item
,date_dim
where
i_manufact_id = 722
and i_item_sk = cs_item_sk
and d_date between '2001-03-09' and
(date_add(cast('2001-03-09' 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 '2001-03-09' and
(date_add(cast('2001-03-09' as date),interval 90 day))
and d_date_sk = cs_sold_date_sk
)
;
-- start query 33 in stream 0 using template query33.tpl
with ss as (
select
i_manufact_id,sum(ss_ext_sales_price) total_sales
from
store_sales,
date_dim,
customer_address,
item
where
i_manufact_id in (select
i_manufact_id
from
item
where i_category in ('Books'))
and ss_item_sk = i_item_sk
and ss_sold_date_sk = d_date_sk
and d_year = 2001
and d_moy = 3
and ss_addr_sk = ca_address_sk
and ca_gmt_offset = -5
group by i_manufact_id),
cs as (
select
i_manufact_id,sum(cs_ext_sales_price) total_sales
from
catalog_sales,
date_dim,
customer_address,
item
where
i_manufact_id in (select
i_manufact_id
from
item
where i_category in ('Books'))
and cs_item_sk = i_item_sk
and cs_sold_date_sk = d_date_sk
and d_year = 2001
and d_moy = 3
and cs_bill_addr_sk = ca_address_sk
and ca_gmt_offset = -5
group by i_manufact_id),
ws as (
select
i_manufact_id,sum(ws_ext_sales_price) total_sales
from
web_sales,
date_dim,
customer_address,
item
where
i_manufact_id in (select
i_manufact_id
from
item
where i_category in ('Books'))
and ws_item_sk = i_item_sk
and ws_sold_date_sk = d_date_sk
and d_year = 2001
and d_moy = 3
and ws_bill_addr_sk = ca_address_sk
and ca_gmt_offset = -5
group by i_manufact_id)
select top 100 i_manufact_id ,sum(total_sales) total_sales
from (select * from ss
union all
select * from cs
union all
select * from ws) tmp1
group by i_manufact_id
order by total_sales
;
-- start query 34 in stream 0 using template query34.tpl
select c_last_name
,c_first_name
,c_salutation
,c_preferred_cust_flag
,ss_ticket_number
,cnt from
(select ss_ticket_number
,ss_customer_sk
,count(*) cnt
from store_sales,date_dim,store,household_demographics
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_store_sk = store.s_store_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and (date_dim.d_dom between 1 and 3 or date_dim.d_dom between 25 and 28)
and (household_demographics.hd_buy_potential = '1001-5000' or
household_demographics.hd_buy_potential = '0-500')
and household_demographics.hd_vehicle_count > 0
and (case when household_demographics.hd_vehicle_count > 0
then household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count
else null
end) > 1.2
and date_dim.d_year in (2000,2000+1,2000+2)
and store.s_county in ('Williamson County','Williamson County','Williamson County','Williamson County',
'Williamson County','Williamson County','Williamson County','Williamson County')
group by ss_ticket_number,ss_customer_sk) dn,customer
where ss_customer_sk = c_customer_sk
and cnt between 15 and 20
order by c_last_name,c_first_name,c_salutation,c_preferred_cust_flag desc, ss_ticket_number
;
-- start query 35 in stream 0 using template query35.tpl
select top 100
ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
count(*) cnt1,
avg(cd_dep_count),
stddev_samp(cd_dep_count),
sum(cd_dep_count),
cd_dep_employed_count,
count(*) cnt2,
avg(cd_dep_employed_count),
stddev_samp(cd_dep_employed_count),
sum(cd_dep_employed_count),
cd_dep_college_count,
count(*) cnt3,
avg(cd_dep_college_count),
stddev_samp(cd_dep_college_count),
sum(cd_dep_college_count)
from
customer c,customer_address ca,customer_demographics
where
c.c_current_addr_sk = ca.ca_address_sk and
cd_demo_sk = c.c_current_cdemo_sk and
exists (select *
from store_sales,date_dim
where c.c_customer_sk = ss_customer_sk and
ss_sold_date_sk = d_date_sk and
d_year = 1999 and
d_qoy < 4) and
(exists (select *
from web_sales,date_dim
where c.c_customer_sk = ws_bill_customer_sk and
ws_sold_date_sk = d_date_sk and
d_year = 1999 and
d_qoy < 4) or
exists (select *
from catalog_sales,date_dim
where c.c_customer_sk = cs_ship_customer_sk and
cs_sold_date_sk = d_date_sk and
d_year = 1999 and
d_qoy < 4))
group by ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
order by ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
;
-- start query 36 in stream 0 using template query36.tpl
select top 100
sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
,i_category
,i_class
,grouping(i_category)+grouping(i_class) as lochierarchy
,rank() over (
partition by grouping(i_category)+grouping(i_class),
case when grouping(i_class) = 0 then i_category end
order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
from
store_sales
,date_dim d1
,item
,store
where
d1.d_year = 2000
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and s_state in ('TN','TN','TN','TN',
'TN','TN','TN','TN')
group by rollup(i_category,i_class)
order by
lochierarchy desc
,case when lochierarchy = 0 then i_category end
,rank_within_parent
;
-- start query 37 in stream 0 using template query37.tpl
select top 100 i_item_id
,i_item_desc
,i_current_price
from item, inventory, date_dim, catalog_sales
where i_current_price between 29 and 29 + 30
and inv_item_sk = i_item_sk
and d_date_sk=inv_date_sk
and d_date between cast('2002-03-29' as date) and (date_add(cast('2002-03-29' as date),interval 60 day))
and i_manufact_id in (705,742,777,944)
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
;
-- start query 38 in stream 0 using template query38.tpl
select top 100 count(*) from (
select distinct c_last_name, c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1189 and 1189 + 11
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 1189 and 1189 + 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 1189 and 1189 + 11
) hot_cust
;
-- start query 39 in stream 0 using template query39.tpl
with inv as
(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
,stdev,mean, case mean when 0 then null else stdev/mean end cov
from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
from inventory
,item
,warehouse
,date_dim
where inv_item_sk = i_item_sk
and inv_warehouse_sk = w_warehouse_sk
and inv_date_sk = d_date_sk
and d_year =2000
group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
where case mean when 0 then 0 else stdev/mean end > 1)
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
from inv inv1,inv inv2
where inv1.i_item_sk = inv2.i_item_sk
and inv1.w_warehouse_sk = inv2.w_warehouse_sk
and inv1.d_moy=1
and inv2.d_moy=1+1
order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
,inv2.d_moy,inv2.mean, inv2.cov
;
-- start query 40 in stream 0 using template query40.tpl
select top 100
w_state
,i_item_id
,sum(case when (cast(d_date as date) < cast ('2001-05-02' 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 ('2001-05-02' 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 (date_sub(cast ('2001-05-02' as date),interval 30 day))
and (date_add(cast('2001-05-02' as date),interval 30 day))
group by
w_state,i_item_id
order by w_state,i_item_id
;
网友评论