ydb/yt/yql/tests/sql/suites/pg-tpch/q21.sql

--!syntax_pg
--TPC-H Q21


select 
s_name, 
count(*) as numwait
from 
plato."supplier", 
plato."lineitem" l1, 
plato."orders", 
plato."nation"
where 
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists ( 
select 
*
from 
plato."lineitem" l2
where 
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists ( 
select 
*
from 
plato."lineitem" l3
where 
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by 
s_name
order by 
numwait desc, 
s_name
limit 100;