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_wholesale_cost - ss_ext_discount_amt) +
0012 ss_ext_sales_price) / 2) year_total,
0013 's' sale_type
0014 FROM customer, store_sales, date_dim
0015 WHERE c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk
0016 GROUP BY c_customer_id,
0017 c_first_name,
0018 c_last_name,
0019 c_preferred_cust_flag,
0020 c_birth_country,
0021 c_login,
0022 c_email_address,
0023 d_year
0024 UNION ALL
0025 SELECT
0026 c_customer_id customer_id,
0027 c_first_name customer_first_name,
0028 c_last_name customer_last_name,
0029 c_preferred_cust_flag customer_preferred_cust_flag,
0030 c_birth_country customer_birth_country,
0031 c_login customer_login,
0032 c_email_address customer_email_address,
0033 d_year dyear,
0034 sum((((cs_ext_list_price - cs_ext_wholesale_cost - cs_ext_discount_amt) +
0035 cs_ext_sales_price) / 2)) year_total,
0036 'c' sale_type
0037 FROM customer, catalog_sales, date_dim
0038 WHERE c_customer_sk = cs_bill_customer_sk AND cs_sold_date_sk = d_date_sk
0039 GROUP BY c_customer_id,
0040 c_first_name,
0041 c_last_name,
0042 c_preferred_cust_flag,
0043 c_birth_country,
0044 c_login,
0045 c_email_address,
0046 d_year
0047 UNION ALL
0048 SELECT
0049 c_customer_id customer_id,
0050 c_first_name customer_first_name,
0051 c_last_name customer_last_name,
0052 c_preferred_cust_flag customer_preferred_cust_flag,
0053 c_birth_country customer_birth_country,
0054 c_login customer_login,
0055 c_email_address customer_email_address,
0056 d_year dyear,
0057 sum((((ws_ext_list_price - ws_ext_wholesale_cost - ws_ext_discount_amt) + ws_ext_sales_price) /
0058 2)) year_total,
0059 'w' sale_type
0060 FROM customer, web_sales, date_dim
0061 WHERE c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk
0062 GROUP BY c_customer_id,
0063 c_first_name,
0064 c_last_name,
0065 c_preferred_cust_flag,
0066 c_birth_country,
0067 c_login,
0068 c_email_address,
0069 d_year)
0070 SELECT
0071 t_s_secyear.customer_id,
0072 t_s_secyear.customer_first_name,
0073 t_s_secyear.customer_last_name,
0074 t_s_secyear.customer_preferred_cust_flag,
0075 t_s_secyear.customer_birth_country,
0076 t_s_secyear.customer_login,
0077 t_s_secyear.customer_email_address
0078 FROM year_total t_s_firstyear, year_total t_s_secyear, year_total t_c_firstyear,
0079 year_total t_c_secyear, year_total t_w_firstyear, year_total t_w_secyear
0080 WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
0081 AND t_s_firstyear.customer_id = t_c_secyear.customer_id
0082 AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
0083 AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
0084 AND t_s_firstyear.customer_id = t_w_secyear.customer_id
0085 AND t_s_firstyear.sale_type = 's'
0086 AND t_c_firstyear.sale_type = 'c'
0087 AND t_w_firstyear.sale_type = 'w'
0088 AND t_s_secyear.sale_type = 's'
0089 AND t_c_secyear.sale_type = 'c'
0090 AND t_w_secyear.sale_type = 'w'
0091 AND t_s_firstyear.dyear = 2001
0092 AND t_s_secyear.dyear = 2001 + 1
0093 AND t_c_firstyear.dyear = 2001
0094 AND t_c_secyear.dyear = 2001 + 1
0095 AND t_w_firstyear.dyear = 2001
0096 AND t_w_secyear.dyear = 2001 + 1
0097 AND t_s_firstyear.year_total > 0
0098 AND t_c_firstyear.year_total > 0
0099 AND t_w_firstyear.year_total > 0
0100 AND CASE WHEN t_c_firstyear.year_total > 0
0101 THEN t_c_secyear.year_total / t_c_firstyear.year_total
0102 ELSE NULL END
0103 > CASE WHEN t_s_firstyear.year_total > 0
0104 THEN t_s_secyear.year_total / t_s_firstyear.year_total
0105 ELSE NULL END
0106 AND CASE WHEN t_c_firstyear.year_total > 0
0107 THEN t_c_secyear.year_total / t_c_firstyear.year_total
0108 ELSE NULL END
0109 > CASE WHEN t_w_firstyear.year_total > 0
0110 THEN t_w_secyear.year_total / t_w_firstyear.year_total
0111 ELSE NULL END
0112 ORDER BY
0113 t_s_secyear.customer_id,
0114 t_s_secyear.customer_first_name,
0115 t_s_secyear.customer_last_name,
0116 t_s_secyear.customer_preferred_cust_flag,
0117 t_s_secyear.customer_birth_country,
0118 t_s_secyear.customer_login,
0119 t_s_secyear.customer_email_address
0120 LIMIT 100
