blob: 32d677913483247b636f2d88e97c1015166d700a [file] [log] [blame]
====
---- QUERY: TPCDS-Q64
-- RESULT MISMATCH FROM ORIGINAL
-- ADDED ORDER BY COLUMNS.
with cs_ui as
(select cs_item_sk
,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
from catalog_sales
,catalog_returns
where cs_item_sk = cr_item_sk
and cs_order_number = cr_order_number
group by cs_item_sk
having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
cross_sales as
(select i_product_name product_name
,i_item_sk item_sk
,s_store_name store_name
,s_zip store_zip
,ad1.ca_street_number b_street_number
,ad1.ca_street_name b_street_name
,ad1.ca_city b_city
,ad1.ca_zip b_zip
,ad2.ca_street_number c_street_number
,ad2.ca_street_name c_street_name
,ad2.ca_city c_city
,ad2.ca_zip c_zip
,d1.d_year as syear
,d2.d_year as fsyear
,d3.d_year s2year
,count(*) cnt
,sum(ss_wholesale_cost) s1
,sum(ss_list_price) s2
,sum(ss_coupon_amt) s3
FROM store_sales
,store_returns
,cs_ui
,date_dim d1
,date_dim d2
,date_dim d3
,store
,customer
,customer_demographics cd1
,customer_demographics cd2
,promotion
,household_demographics hd1
,household_demographics hd2
,customer_address ad1
,customer_address ad2
,income_band ib1
,income_band ib2
,item
WHERE ss_store_sk = s_store_sk AND
ss_sold_date_sk = d1.d_date_sk AND
ss_customer_sk = c_customer_sk AND
ss_cdemo_sk= cd1.cd_demo_sk AND
ss_hdemo_sk = hd1.hd_demo_sk AND
ss_addr_sk = ad1.ca_address_sk and
ss_item_sk = i_item_sk and
ss_item_sk = sr_item_sk and
ss_ticket_number = sr_ticket_number and
ss_item_sk = cs_ui.cs_item_sk and
c_current_cdemo_sk = cd2.cd_demo_sk AND
c_current_hdemo_sk = hd2.hd_demo_sk AND
c_current_addr_sk = ad2.ca_address_sk and
c_first_sales_date_sk = d2.d_date_sk and
c_first_shipto_date_sk = d3.d_date_sk and
ss_promo_sk = p_promo_sk and
hd1.hd_income_band_sk = ib1.ib_income_band_sk and
hd2.hd_income_band_sk = ib2.ib_income_band_sk and
cd1.cd_marital_status <> cd2.cd_marital_status and
i_color in ('purple','burlywood','indian','spring','floral','medium') and
i_current_price between 64 and 64 + 10 and
i_current_price between 64 + 1 and 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 cs1,cross_sales 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
-- Added to fix result ordering
,b_street_number desc
,b_street_name desc
,b_zip desc
,s11 desc
,s21 desc
,s31 desc
,s12 desc
,s22 desc
,s32 desc;
---- RESULTS
'n stableableantiought','able','31904','987','Hillcrest ','Fairbanks','46653','216','3rd ','Reno','40344',1999,1,55.42,80.91,954.53,59.08,76.80,0.00,2000,1
'n stableableantiought','ation','31904','425','Green ','Enterprise','11757','772','Valley ','NULL','NULL',1999,1,16.24,25.17,0.00,44.27,58.43,0.00,2000,1
'n stableableantiought','ation','31904','425','Green ','Enterprise','11757','772','Valley ','NULL','NULL',1999,1,16.24,25.17,0.00,7.92,11.80,0.00,2000,1
'n stableableantiought','ation','31904','316','Valley Tenth','Pine Grove','74593','806','Wilson Main','Jackson','59583',1999,1,49.63,78.41,14.22,44.27,58.43,0.00,2000,1
'n stableableantiought','ation','31904','316','Valley Tenth','Pine Grove','74593','806','Wilson Main','Jackson','59583',1999,1,49.63,78.41,14.22,7.92,11.80,0.00,2000,1
'n stableableantiought','ation','31904','173','Park Maple','Sulphur Springs','68354','232','Franklin ','Fairfield','66192',1999,1,60.78,85.09,0.00,44.27,58.43,0.00,2000,1
'n stableableantiought','ation','31904','173','Park Maple','Sulphur Springs','68354','232','Franklin ','Fairfield','66192',1999,1,60.78,85.09,0.00,7.92,11.80,0.00,2000,1
'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,58.19,85.53,0.00,2000,1
'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,30.28,49.65,0.00,2000,1
'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,10.25,12.09,0.00,2000,1
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, INT, BIGINT, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, INT, BIGINT
====