github.com/matrixorigin/matrixone@v1.2.0/pkg/sql/plan/tpch/q20.sql (about)

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