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
0028 , customer
0029 WHERE ss_customer_sk = c_customer_sk
0030 GROUP BY c_customer_sk
0031 HAVING sum(ss_quantity * ss_sales_price) > (50 / 100.0) *
0032 (SELECT *
0033 FROM max_store_sales))
0034 SELECT
0035 c_last_name,
0036 c_first_name,
0037 sales
0038 FROM ((SELECT
0039 c_last_name,
0040 c_first_name,
0041 sum(cs_quantity * cs_list_price) sales
0042 FROM catalog_sales, customer, date_dim
0043 WHERE d_year = 2000
0044 AND d_moy = 2
0045 AND cs_sold_date_sk = d_date_sk
0046 AND cs_item_sk IN (SELECT item_sk
0047 FROM frequent_ss_items)
0048 AND cs_bill_customer_sk IN (SELECT c_customer_sk
0049 FROM best_ss_customer)
0050 AND cs_bill_customer_sk = c_customer_sk
0051 GROUP BY c_last_name, c_first_name)
0052 UNION ALL
0053 (SELECT
0054 c_last_name,
0055 c_first_name,
0056 sum(ws_quantity * ws_list_price) sales
0057 FROM web_sales, customer, date_dim
0058 WHERE d_year = 2000
0059 AND d_moy = 2
0060 AND ws_sold_date_sk = d_date_sk
0061 AND ws_item_sk IN (SELECT item_sk
0062 FROM frequent_ss_items)
0063 AND ws_bill_customer_sk IN (SELECT c_customer_sk
0064 FROM best_ss_customer)
0065 AND ws_bill_customer_sk = c_customer_sk
0066 GROUP BY c_last_name, c_first_name)) y
0067 ORDER BY c_last_name, c_first_name, sales
0068 LIMIT 100
