resources/tpcds-v2.7.0/q11.sql

0001 WITH year_total AS (
0002   SELECT
0003     c_customer_id customer_id,
0004     c_first_name customer_first_name,
0005     c_last_name customer_last_name,
0006     c_preferred_cust_flag customer_preferred_cust_flag,
0007     c_birth_country customer_birth_country,
0008     c_login customer_login,
0009     c_email_address customer_email_address,
0010     d_year dyear,
0011     sum(ss_ext_list_price - ss_ext_discount_amt) year_total,
0012     's' sale_type
0013   FROM customer, store_sales, date_dim
0014   WHERE c_customer_sk = ss_customer_sk
0015     AND ss_sold_date_sk = d_date_sk
0016   GROUP BY c_customer_id
0017     , c_first_name
0018     , c_last_name
0019     , d_year
0020     , c_preferred_cust_flag
0021     , c_birth_country
0022     , c_login
0023     , c_email_address
0024     , d_year
0025   UNION ALL
0026   SELECT
0027     c_customer_id customer_id,
0028     c_first_name customer_first_name,
0029     c_last_name customer_last_name,
0030     c_preferred_cust_flag customer_preferred_cust_flag,
0031     c_birth_country customer_birth_country,
0032     c_login customer_login,
0033     c_email_address customer_email_address,
0034     d_year dyear,
0035     sum(ws_ext_list_price - ws_ext_discount_amt) year_total,
0036     'w' sale_type
0037   FROM customer, web_sales, date_dim
0038   WHERE c_customer_sk = ws_bill_customer_sk
0039     AND ws_sold_date_sk = d_date_sk
0040   GROUP BY
0041     c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country,
0042     c_login, c_email_address, d_year)
0043 SELECT
0044   -- select list of q11 in TPCDS v1.4 is below:
0045   -- t_s_secyear.customer_preferred_cust_flag
0046   t_s_secyear.customer_id,
0047   t_s_secyear.customer_first_name,
0048   t_s_secyear.customer_last_name,
0049   t_s_secyear.customer_email_address
0050 FROM year_total t_s_firstyear
0051   , year_total t_s_secyear
0052   , year_total t_w_firstyear
0053   , year_total t_w_secyear
0054 WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
0055   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
0056   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
0057   AND t_s_firstyear.sale_type = 's'
0058   AND t_w_firstyear.sale_type = 'w'
0059   AND t_s_secyear.sale_type = 's'
0060   AND t_w_secyear.sale_type = 'w'
0061   AND t_s_firstyear.dyear = 2001
0062   AND t_s_secyear.dyear = 2001 + 1
0063   AND t_w_firstyear.dyear = 2001
0064   AND t_w_secyear.dyear = 2001 + 1
0065   AND t_s_firstyear.year_total > 0
0066   AND t_w_firstyear.year_total > 0
0067   AND CASE WHEN t_w_firstyear.year_total > 0
0068   THEN t_w_secyear.year_total / t_w_firstyear.year_total
0069   -- q11 in TPCDS v1.4 used NULL
0070   --     ELSE NULL END
0071       ELSE 0.0 END
0072   > CASE WHEN t_s_firstyear.year_total > 0
0073   THEN t_s_secyear.year_total / t_s_firstyear.year_total
0074   -- q11 in TPCDS v1.4 used NULL
0075   --   ELSE NULL END
0076     ELSE 0.0 END
0077 ORDER BY
0078   -- order-by list of q11 in TPCDS v1.4 is below:
0079   -- t_s_secyear.customer_preferred_cust_flag
0080   t_s_secyear.customer_id,
0081   t_s_secyear.customer_first_name,
0082   t_s_secyear.customer_last_name,
0083   t_s_secyear.customer_email_address
0084 LIMIT 100