| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124 |
- --!syntax_pg
- --TPC-DS Q64
- -- start query 1 in stream 0 using template ../query_templates/query64.tpl
- 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 plato.catalog_sales
- ,plato.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::numeric*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 plato.store_sales
- ,plato.store_returns
- ,cs_ui
- ,plato.date_dim d1
- ,plato.date_dim d2
- ,plato.date_dim d3
- ,plato.store
- ,plato.customer
- ,plato.customer_demographics cd1
- ,plato.customer_demographics cd2
- ,plato.promotion
- ,plato.household_demographics hd1
- ,plato.household_demographics hd2
- ,plato.customer_address ad1
- ,plato.customer_address ad2
- ,plato.income_band ib1
- ,plato.income_band ib2
- ,plato.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 ('maroon','burnished','dim','steel','navajo','chocolate') and
- i_current_price between 35::numeric and (35 + 10)::numeric and
- i_current_price between (35 + 1)::numeric and (35 + 15)::numeric
- 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 as cs1_syear
- ,cs1.cnt as 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 as cs2_syear
- ,cs2.cnt as 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
- ,cs1.s1
- ,cs2.s1;
- -- end query 1 in stream 0 using template ../query_templates/query64.tpl
|
