0001 WITH web_v1 AS (
0002 SELECT
0003 ws_item_sk item_sk,
0004 d_date,
0005 sum(sum(ws_sales_price))
0006 OVER (PARTITION BY ws_item_sk
0007 ORDER BY d_date
0008 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales
0009 FROM web_sales, date_dim
0010 WHERE ws_sold_date_sk = d_date_sk
0011 AND d_month_seq BETWEEN 1200 AND 1200 + 11
0012 AND ws_item_sk IS NOT NULL
0013 GROUP BY ws_item_sk, d_date),
0014 store_v1 AS (
0015 SELECT
0016 ss_item_sk item_sk,
0017 d_date,
0018 sum(sum(ss_sales_price))
0019 OVER (PARTITION BY ss_item_sk
0020 ORDER BY d_date
0021 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales
0022 FROM store_sales, date_dim
0023 WHERE ss_sold_date_sk = d_date_sk
0024 AND d_month_seq BETWEEN 1200 AND 1200 + 11
0025 AND ss_item_sk IS NOT NULL
0026 GROUP BY ss_item_sk, d_date)
0027 SELECT *
0028 FROM (SELECT
0029 item_sk,
0030 d_date,
0031 web_sales,
0032 store_sales,
0033 max(web_sales)
0034 OVER (PARTITION BY item_sk
0035 ORDER BY d_date
0036 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) web_cumulative,
0037 max(store_sales)
0038 OVER (PARTITION BY item_sk
0039 ORDER BY d_date
0040 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) store_cumulative
0041 FROM (SELECT
0042 CASE WHEN web.item_sk IS NOT NULL
0043 THEN web.item_sk
0044 ELSE store.item_sk END item_sk,
0045 CASE WHEN web.d_date IS NOT NULL
0046 THEN web.d_date
0047 ELSE store.d_date END d_date,
0048 web.cume_sales web_sales,
0049 store.cume_sales store_sales
0050 FROM web_v1 web FULL OUTER JOIN store_v1 store ON (web.item_sk = store.item_sk
0051 AND web.d_date = store.d_date)
0052 ) x) y
0053 WHERE web_cumulative > store_cumulative
0054 ORDER BY item_sk, d_date
0055 LIMIT 100
