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 d_year AS year,
0007 sum(ss_net_paid) year_total,
0008 's' sale_type
0009 FROM
0010 customer, store_sales, date_dim
0011 WHERE c_customer_sk = ss_customer_sk
0012 AND ss_sold_date_sk = d_date_sk
0013 AND d_year IN (2001, 2001 + 1)
0014 GROUP BY
0015 c_customer_id, c_first_name, c_last_name, d_year
0016 UNION ALL
0017 SELECT
0018 c_customer_id customer_id,
0019 c_first_name customer_first_name,
0020 c_last_name customer_last_name,
0021 d_year AS year,
0022 sum(ws_net_paid) year_total,
0023 'w' sale_type
0024 FROM
0025 customer, web_sales, date_dim
0026 WHERE c_customer_sk = ws_bill_customer_sk
0027 AND ws_sold_date_sk = d_date_sk
0028 AND d_year IN (2001, 2001 + 1)
0029 GROUP BY
0030 c_customer_id, c_first_name, c_last_name, d_year)
0031 SELECT
0032 t_s_secyear.customer_id,
0033 t_s_secyear.customer_first_name,
0034 t_s_secyear.customer_last_name
0035 FROM
0036 year_total t_s_firstyear, year_total t_s_secyear,
0037 year_total t_w_firstyear, year_total t_w_secyear
0038 WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
0039 AND t_s_firstyear.customer_id = t_w_secyear.customer_id
0040 AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
0041 AND t_s_firstyear.sale_type = 's'
0042 AND t_w_firstyear.sale_type = 'w'
0043 AND t_s_secyear.sale_type = 's'
0044 AND t_w_secyear.sale_type = 'w'
0045 AND t_s_firstyear.year = 2001
0046 AND t_s_secyear.year = 2001 + 1
0047 AND t_w_firstyear.year = 2001
0048 AND t_w_secyear.year = 2001 + 1
0049 AND t_s_firstyear.year_total > 0
0050 AND t_w_firstyear.year_total > 0
0051 AND CASE WHEN t_w_firstyear.year_total > 0
0052 THEN t_w_secyear.year_total / t_w_firstyear.year_total
0053 ELSE NULL END
0054 > CASE WHEN t_s_firstyear.year_total > 0
0055 THEN t_s_secyear.year_total / t_s_firstyear.year_total
0056 ELSE NULL END
0057 ORDER BY 1, 1, 1
0058 LIMIT 100
