0001 WITH ss AS
0002 (SELECT
0003 ca_county,
0004 d_qoy,
0005 d_year,
0006 sum(ss_ext_sales_price) AS store_sales
0007 FROM store_sales, date_dim, customer_address
0008 WHERE ss_sold_date_sk = d_date_sk
0009 AND ss_addr_sk = ca_address_sk
0010 GROUP BY ca_county, d_qoy, d_year),
0011 ws AS
0012 (SELECT
0013 ca_county,
0014 d_qoy,
0015 d_year,
0016 sum(ws_ext_sales_price) AS web_sales
0017 FROM web_sales, date_dim, customer_address
0018 WHERE ws_sold_date_sk = d_date_sk
0019 AND ws_bill_addr_sk = ca_address_sk
0020 GROUP BY ca_county, d_qoy, d_year)
0021 SELECT
0022 ss1.ca_county,
0023 ss1.d_year,
0024 ws2.web_sales / ws1.web_sales web_q1_q2_increase,
0025 ss2.store_sales / ss1.store_sales store_q1_q2_increase,
0026 ws3.web_sales / ws2.web_sales web_q2_q3_increase,
0027 ss3.store_sales / ss2.store_sales store_q2_q3_increase
0028 FROM
0029 ss ss1, ss ss2, ss ss3, ws ws1, ws ws2, ws ws3
0030 WHERE
0031 ss1.d_qoy = 1
0032 AND ss1.d_year = 2000
0033 AND ss1.ca_county = ss2.ca_county
0034 AND ss2.d_qoy = 2
0035 AND ss2.d_year = 2000
0036 AND ss2.ca_county = ss3.ca_county
0037 AND ss3.d_qoy = 3
0038 AND ss3.d_year = 2000
0039 AND ss1.ca_county = ws1.ca_county
0040 AND ws1.d_qoy = 1
0041 AND ws1.d_year = 2000
0042 AND ws1.ca_county = ws2.ca_county
0043 AND ws2.d_qoy = 2
0044 AND ws2.d_year = 2000
0045 AND ws1.ca_county = ws3.ca_county
0046 AND ws3.d_qoy = 3
0047 AND ws3.d_year = 2000
0048 AND CASE WHEN ws1.web_sales > 0
0049 THEN ws2.web_sales / ws1.web_sales
0050 ELSE NULL END
0051 > CASE WHEN ss1.store_sales > 0
0052 THEN ss2.store_sales / ss1.store_sales
0053 ELSE NULL END
0054 AND CASE WHEN ws2.web_sales > 0
0055 THEN ws3.web_sales / ws2.web_sales
0056 ELSE NULL END
0057 > CASE WHEN ss2.store_sales > 0
0058 THEN ss3.store_sales / ss2.store_sales
0059 ELSE NULL END
0060 ORDER BY ss1.ca_county
