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
|
-- TPC-H/TPC-R Shipping Priority Query (Q3)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$join1 = (
select
c.c_mktsegment as c_mktsegment,
o.o_orderdate as o_orderdate,
o.o_shippriority as o_shippriority,
o.o_orderkey as o_orderkey
from
plato.customer as c
join
plato.orders as o
on
c.c_custkey = o.o_custkey
);
$join2 = (
select
j1.c_mktsegment as c_mktsegment,
j1.o_orderdate as o_orderdate,
j1.o_shippriority as o_shippriority,
l.l_orderkey as l_orderkey,
l.l_discount as l_discount,
l.l_shipdate as l_shipdate,
l.l_extendedprice as l_extendedprice
from
$join1 as j1
join
plato.lineitem as l
on
l.l_orderkey = j1.o_orderkey
);
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
$join2
where
c_mktsegment = 'MACHINERY'
and CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
and CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10;
|
