0001 WITH frequent_ss_items AS
0002 (SELECT
0003 substr(i_item_desc, 1, 30) itemdesc,
0004 i_item_sk item_sk,
0005 d_date solddate,
0006 count(*) cnt
0007 FROM store_sales, date_dim, item
0008 WHERE ss_sold_date_sk = d_date_sk
0009 AND ss_item_sk = i_item_sk
0010 AND d_year IN (2000, 2000 + 1, 2000 + 2, 2000 + 3)
0011 GROUP BY substr(i_item_desc, 1, 30), i_item_sk, d_date
0012 HAVING count(*) > 4),
0013 max_store_sales AS
0014 (SELECT max(csales) tpcds_cmax
0015 FROM (SELECT
0016 c_customer_sk,
0017 sum(ss_quantity * ss_sales_price) csales
0018 FROM store_sales, customer, date_dim
0019 WHERE ss_customer_sk = c_customer_sk
0020 AND ss_sold_date_sk = d_date_sk
0021 AND d_year IN (2000, 2000 + 1, 2000 + 2, 2000 + 3)
0022 GROUP BY c_customer_sk) x),
0023 best_ss_customer AS
0024 (SELECT
0025 c_customer_sk,
0026 sum(ss_quantity * ss_sales_price) ssales
0027 FROM store_sales, customer
0028 WHERE ss_customer_sk = c_customer_sk
0029 GROUP BY c_customer_sk
0030 HAVING sum(ss_quantity * ss_sales_price) > (50 / 100.0) *
0031 (SELECT *
0032 FROM max_store_sales))
0033 SELECT sum(sales)
0034 FROM ((SELECT cs_quantity * cs_list_price sales
0035 FROM catalog_sales, date_dim
0036 WHERE d_year = 2000
0037 AND d_moy = 2
0038 AND cs_sold_date_sk = d_date_sk
0039 AND cs_item_sk IN (SELECT item_sk
0040 FROM frequent_ss_items)
0041 AND cs_bill_customer_sk IN (SELECT c_customer_sk
0042 FROM best_ss_customer))
0043 UNION ALL
0044 (SELECT ws_quantity * ws_list_price sales
0045 FROM web_sales, date_dim
0046 WHERE d_year = 2000
0047 AND d_moy = 2
0048 AND ws_sold_date_sk = d_date_sk
0049 AND ws_item_sk IN (SELECT item_sk
0050 FROM frequent_ss_items)
0051 AND ws_bill_customer_sk IN (SELECT c_customer_sk
0052 FROM best_ss_customer))) y
0053 LIMIT 100
