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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
--!syntax_pg
--TPC-DS Q23
-- start query 1 in stream 0 using template ../query_templates/query23.tpl
with frequent_ss_items as
(select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
from plato.store_sales
,plato.date_dim
,plato.item
where ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and d_year in (1999,1999+1,1999+2,1999+3)
group by substr(i_item_desc,1,30),i_item_sk,d_date
having count(*) >4),
max_store_sales as
(select max(csales) tpcds_cmax
from (select c_customer_sk,sum(ss_quantity::numeric*ss_sales_price) csales
from plato.store_sales
,plato.customer
,plato.date_dim
where ss_customer_sk = c_customer_sk
and ss_sold_date_sk = d_date_sk
and d_year in (1999,1999+1,1999+2,1999+3)
group by c_customer_sk) a),
best_ss_customer as
(select c_customer_sk,sum(ss_quantity::numeric*ss_sales_price) ssales
from plato.store_sales
,plato.customer
where ss_customer_sk = c_customer_sk
group by c_customer_sk
having sum(ss_quantity::numeric*ss_sales_price) > (95.0/100.0)::numeric * (select
*
from
max_store_sales)::numeric)
select sum(sales)
from (select cs_quantity::numeric*cs_list_price sales
from plato.catalog_sales
,plato.date_dim
where d_year = 1999
and d_moy = 1
and cs_sold_date_sk = d_date_sk
and cs_item_sk in (select item_sk from frequent_ss_items)
and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
union all
select ws_quantity::numeric*ws_list_price sales
from plato.web_sales
,plato.date_dim
where d_year = 1999
and d_moy = 1
and ws_sold_date_sk = d_date_sk
and ws_item_sk in (select item_sk from frequent_ss_items)
and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) a
limit 100;
with frequent_ss_items as
(select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
from plato.store_sales
,plato.date_dim
,plato.item
where ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and d_year in (1999,1999 + 1,1999 + 2,1999 + 3)
group by substr(i_item_desc,1,30),i_item_sk,d_date
having count(*) >4),
max_store_sales as
(select max(csales) tpcds_cmax
from (select c_customer_sk,sum(ss_quantity::numeric*ss_sales_price) csales
from plato.store_sales
,plato.customer
,plato.date_dim
where ss_customer_sk = c_customer_sk
and ss_sold_date_sk = d_date_sk
and d_year in (1999,1999+1,1999+2,1999+3)
group by c_customer_sk) a),
best_ss_customer as
(select c_customer_sk,sum(ss_quantity::numeric*ss_sales_price) ssales
from plato.store_sales
,plato.customer
where ss_customer_sk = c_customer_sk
group by c_customer_sk
having sum(ss_quantity::numeric*ss_sales_price) > (95.0/100.0)::numeric * (select
*
from max_store_sales)::numeric)
select c_last_name,c_first_name,sales
from (select c_last_name,c_first_name,sum(cs_quantity::numeric*cs_list_price) sales
from plato.catalog_sales
,plato.customer
,plato.date_dim
where d_year = 1999
and d_moy = 1
and cs_sold_date_sk = d_date_sk
and cs_item_sk in (select item_sk from frequent_ss_items)
and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
and cs_bill_customer_sk = c_customer_sk
group by c_last_name,c_first_name
union all
select c_last_name,c_first_name,sum(ws_quantity::numeric*ws_list_price) sales
from plato.web_sales
,plato.customer
,plato.date_dim
where d_year = 1999
and d_moy = 1
and ws_sold_date_sk = d_date_sk
and ws_item_sk in (select item_sk from frequent_ss_items)
and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)
and ws_bill_customer_sk = c_customer_sk
group by c_last_name,c_first_name) a
order by c_last_name,c_first_name,sales
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query23.tpl
|
