0001
0002
0003 select
0004 s_name,
0005 count(*) as numwait
0006 from
0007 supplier,
0008 lineitem l1,
0009 orders,
0010 nation
0011 where
0012 s_suppkey = l1.l_suppkey
0013 and o_orderkey = l1.l_orderkey
0014 and o_orderstatus = 'F'
0015 and l1.l_receiptdate > l1.l_commitdate
0016 and exists (
0017 select
0018 *
0019 from
0020 lineitem l2
0021 where
0022 l2.l_orderkey = l1.l_orderkey
0023 and l2.l_suppkey <> l1.l_suppkey
0024 )
0025 and not exists (
0026 select
0027 *
0028 from
0029 lineitem l3
0030 where
0031 l3.l_orderkey = l1.l_orderkey
0032 and l3.l_suppkey <> l1.l_suppkey
0033 and l3.l_receiptdate > l3.l_commitdate
0034 )
0035 and s_nationkey = n_nationkey
0036 and n_name = 'SAUDI ARABIA'
0037 group by
0038 s_name
0039 order by
0040 numwait desc,
0041 s_name
0042 limit 100
