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
|
-- TPC-H/TPC-R Returned Item Reporting Query (Q10)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$border = Date("1993-12-01");
$join1 = (
select
c.c_custkey as c_custkey,
c.c_name as c_name,
c.c_acctbal as c_acctbal,
c.c_address as c_address,
c.c_phone as c_phone,
c.c_comment as c_comment,
c.c_nationkey as c_nationkey,
o.o_orderkey as o_orderkey
from
plato.customer as c
join
plato.orders as o
on
c.c_custkey = o.o_custkey
where
cast(o.o_orderdate as timestamp) >= $border and
cast(o.o_orderdate as timestamp) < ($border + Interval("P90D"))
);
$join2 = (
select
j.c_custkey as c_custkey,
j.c_name as c_name,
j.c_acctbal as c_acctbal,
j.c_address as c_address,
j.c_phone as c_phone,
j.c_comment as c_comment,
j.c_nationkey as c_nationkey,
l.l_extendedprice as l_extendedprice,
l.l_discount as l_discount
from
$join1 as j
join
plato.lineitem as l
on
l.l_orderkey = j.o_orderkey
where
l.l_returnflag = 'R'
);
$join3 = (
select
j.c_custkey as c_custkey,
j.c_name as c_name,
j.c_acctbal as c_acctbal,
j.c_address as c_address,
j.c_phone as c_phone,
j.c_comment as c_comment,
j.c_nationkey as c_nationkey,
j.l_extendedprice as l_extendedprice,
j.l_discount as l_discount,
n.n_name as n_name
from
$join2 as j
join
plato.nation as n
on
n.n_nationkey = j.c_nationkey
);
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
$join3
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20;
|
