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

     1  
     2  use tpch;
     3  
     4  with q15_revenue0 as (
     5      select
     6          l_suppkey as supplier_no,
     7          sum(l_extendedprice * (1 - l_discount)) as total_revenue
     8      from
     9          lineitem
    10      where
    11          l_shipdate >= date '1995-12-01'
    12          and l_shipdate < date '1995-12-01' + interval '3' month
    13      group by
    14          l_suppkey
    15      )
    16  select
    17      s_suppkey,
    18      s_name,
    19      s_address,
    20      s_phone,
    21      total_revenue
    22  from
    23      supplier,
    24      q15_revenue0
    25  where
    26      s_suppkey = supplier_no
    27      and total_revenue = (
    28          select
    29              max(total_revenue)
    30          from
    31              q15_revenue0
    32      )
    33  order by
    34      s_suppkey
    35  ;