Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
tpcds.query64.sql Maven / Gradle / Ivy
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 as syear_2
, cs2.cnt as cnt_2
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
, cnt_2
