| ==== |
| ---- QUERY: TPCDS-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 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 ('coral','sienna','orange','salmon','ghost','red') and |
| i_current_price between 79 and 79 + 10 and |
| i_current_price between 79 + 1 and 79 + 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 = 2000 and |
| cs2.syear = 2000 + 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; |
| |
| |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING, DECIMAL |
| ==== |
