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
49
50
|
--!syntax_pg
--TPC-DS Q29
-- start query 1 in stream 0 using template ../query_templates/query29.tpl
select
i_item_id
,i_item_desc
,s_store_id
,s_store_name
,sum(ss_quantity) as store_sales_quantity
,sum(sr_return_quantity) as store_returns_quantity
,sum(cs_quantity) as catalog_sales_quantity
from
plato.store_sales
,plato.store_returns
,plato.catalog_sales
,plato.date_dim d1
,plato.date_dim d2
,plato.date_dim d3
,plato.store
,plato.item
where
d1.d_moy = 4
and d1.d_year = 1999
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and ss_customer_sk = sr_customer_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and sr_returned_date_sk = d2.d_date_sk
and d2.d_moy between 4 and 4 + 3
and d2.d_year = 1999
and sr_customer_sk = cs_bill_customer_sk
and sr_item_sk = cs_item_sk
and cs_sold_date_sk = d3.d_date_sk
and d3.d_year in (1999,1999+1,1999+2)
group by
i_item_id
,i_item_desc
,s_store_id
,s_store_name
order by
i_item_id
,i_item_desc
,s_store_id
,s_store_name
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query29.tpl
|
