| ==== |
| ---- 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 |
| ==== |
