1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
--!syntax_pg
--TPC-DS Q51
-- start query 1 in stream 0 using template ../query_templates/query51.tpl
WITH web_v1 as (
select
ws_item_sk item_sk, d_date,
sum(sum(ws_sales_price))
over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from plato.web_sales
,plato.date_dim
where ws_sold_date_sk=d_date_sk
and d_month_seq between 1212 and 1212+11
and ws_item_sk is not NULL
group by ws_item_sk, d_date),
store_v1 as (
select
ss_item_sk item_sk, d_date,
sum(sum(ss_sales_price))
over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from plato.store_sales
,plato.date_dim
where ss_sold_date_sk=d_date_sk
and d_month_seq between 1212 and 1212+11
and ss_item_sk is not NULL
group by ss_item_sk, d_date)
select *
from (select item_sk
,d_date
,web_sales
,store_sales
,max(web_sales)
over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative
,max(store_sales)
over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative
from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk
,case when web.d_date is not null then web.d_date else store.d_date end d_date
,web.cume_sales web_sales
,store.cume_sales store_sales
from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk
and web.d_date = store.d_date)
)x )y
where web_cumulative > store_cumulative
order by item_sk
,d_date
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query51.tpl
|
