blob: 729fd88641b175d47bc3fe166050a35b63a98fee (
plain) (
blame)
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
|
--!syntax_pg
--ignore runonopt plan diff
--TPC-H Q15
create view revenue_STREAM_ID (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1::numeric - l_discount))
from
plato."lineitem"
where
l_shipdate >= date '1996-01-01'
and l_shipdate < date '1996-01-01' + interval '3' month
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
plato."supplier",
revenue_STREAM_ID
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue_STREAM_ID
)
order by
s_suppkey;
drop view revenue_STREAM_ID;
|
