github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/benchmark/tpch/03_QUERIES/q21.sql (about)

     1  use tpch;
     2  select
     3      s_name,
     4      count(*) as numwait
     5  from
     6      supplier,
     7      lineitem l1,
     8      orders,
     9      nation
    10  where
    11      s_suppkey = l1.l_suppkey
    12      and o_orderkey = l1.l_orderkey
    13      and o_orderstatus = 'F'
    14      and l1.l_receiptdate > l1.l_commitdate
    15      and exists (
    16          select
    17              *
    18          from
    19              lineitem l2
    20          where
    21              l2.l_orderkey = l1.l_orderkey
    22              and l2.l_suppkey <> l1.l_suppkey
    23      )
    24      and not exists (
    25          select
    26              *
    27          from
    28              lineitem l3
    29          where
    30              l3.l_orderkey = l1.l_orderkey
    31              and l3.l_suppkey <> l1.l_suppkey
    32              and l3.l_receiptdate > l3.l_commitdate
    33      )
    34      and s_nationkey = n_nationkey
    35      and n_name = 'BRAZIL'
    36  group by
    37      s_name
    38  order by
    39      numwait desc,
    40      s_name
    41  limit 100
    42  ;