0001
0002 WITH web_tv as (
0003 select
0004 ws_item_sk item_sk,
0005 d_date,
0006 sum(ws_sales_price) sumws,
0007 row_number() over (partition by ws_item_sk order by d_date) rk
0008 from
0009 web_sales, date_dim
0010 where
0011 ws_sold_date_sk=d_date_sk
0012 and d_month_seq between 1212 and 1212 + 11
0013 and ws_item_sk is not NULL
0014 group by
0015 ws_item_sk, d_date),
0016 web_v1 as (
0017 select
0018 v1.item_sk,
0019 v1.d_date,
0020 v1.sumws,
0021 sum(v2.sumws) cume_sales
0022 from
0023 web_tv v1, web_tv v2
0024 where
0025 v1.item_sk = v2.item_sk
0026 and v1.rk >= v2.rk
0027 group by
0028 v1.item_sk,
0029 v1.d_date,
0030 v1.sumws),
0031 store_tv as (
0032 select
0033 ss_item_sk item_sk,
0034 d_date,
0035 sum(ss_sales_price) sumss,
0036 row_number() over (partition by ss_item_sk order by d_date) rk
0037 from
0038 store_sales, date_dim
0039 where
0040 ss_sold_date_sk = d_date_sk
0041 and d_month_seq between 1212 and 1212 + 11
0042 and ss_item_sk is not NULL
0043 group by ss_item_sk, d_date),
0044 store_v1 as (
0045 select
0046 v1.item_sk,
0047 v1.d_date,
0048 v1.sumss,
0049 sum(v2.sumss) cume_sales
0050 from
0051 store_tv v1, store_tv v2
0052 where
0053 v1.item_sk = v2.item_sk
0054 and v1.rk >= v2.rk
0055 group by
0056 v1.item_sk,
0057 v1.d_date,
0058 v1.sumss),
0059 v as (
0060 select
0061 item_sk,
0062 d_date,
0063 web_sales,
0064 store_sales,
0065 row_number() over (partition by item_sk order by d_date) rk
0066 from (
0067 select
0068 case when web.item_sk is not null
0069 then web.item_sk
0070 else store.item_sk end item_sk,
0071 case when web.d_date is not null
0072 then web.d_date
0073 else store.d_date end d_date,
0074 web.cume_sales web_sales,
0075 store.cume_sales store_sales
0076 from
0077 web_v1 web full outer join store_v1 store
0078 on (web.item_sk = store.item_sk and web.d_date = store.d_date)))
0079 select *
0080 from (
0081 select
0082 v1.item_sk,
0083 v1.d_date,
0084 v1.web_sales,
0085 v1.store_sales,
0086 max(v2.web_sales) web_cumulative,
0087 max(v2.store_sales) store_cumulative
0088 from
0089 v v1, v v2
0090 where
0091 v1.item_sk = v2.item_sk
0092 and v1.rk >= v2.rk
0093 group by
0094 v1.item_sk,
0095 v1.d_date,
0096 v1.web_sales,
0097 v1.store_sales) x
0098 where
0099 web_cumulative > store_cumulative
0100 order by
0101 item_sk,
0102 d_date
0103 limit 100
