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

     1  /*
     2  create view q15_revenue0 (supplier_no, total_revenue) as
     3  	select
     4  		l_suppkey,
     5  		sum(l_extendedprice * (1 - l_discount))
     6  	from
     7  		lineitem
     8  	where
     9  		l_shipdate >= date '1995-12-01'
    10  		and l_shipdate < date '1995-12-01' + interval '3 month'
    11  	group by
    12  		l_suppkey;
    13  
    14  Rewrite Q15 using with.
    15  */
    16  
    17  with q15_revenue0 as (
    18  	select
    19  		l_suppkey as supplier_no,
    20  		sum(l_extendedprice * (1 - l_discount)) as total_revenue
    21  	from
    22  		lineitem
    23  	where
    24  		l_shipdate >= date '1995-12-01'
    25  		and l_shipdate < date '1995-12-01' + interval '3' month
    26  	group by
    27  		l_suppkey
    28      )
    29  select
    30  	s_suppkey,
    31  	s_name,
    32  	s_address,
    33  	s_phone,
    34  	total_revenue
    35  from
    36  	supplier,
    37  	q15_revenue0
    38  where
    39  	s_suppkey = supplier_no
    40  	and total_revenue = (
    41  		select
    42  			max(total_revenue)
    43  		from
    44  			q15_revenue0
    45  	)
    46  order by
    47  	s_suppkey
    48  ;