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

     1  use tpch;
     2  select
     3      s_name,
     4      s_address
     5  from
     6      supplier,
     7      nation
     8  where
     9      s_suppkey in (
    10          select
    11              ps_suppkey
    12          from
    13              partsupp
    14          where
    15              ps_partkey in (
    16                  select
    17                      p_partkey
    18                  from
    19                      part
    20                  where
    21                      p_name like 'lime%'
    22              )
    23              and ps_availqty > (
    24                  select
    25                      0.5 * sum(l_quantity)
    26                  from
    27                      lineitem
    28                  where
    29                      l_partkey = ps_partkey
    30                      and l_suppkey = ps_suppkey
    31                      and l_shipdate >= date '1993-01-01'
    32                      and l_shipdate < date '1993-01-01' + interval '1' year
    33              )
    34      )
    35      and s_nationkey = n_nationkey
    36      and n_name = 'VIETNAM'
    37  order by s_name
    38  ;