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

     1  use tpch;
     2  select
     3      nation,
     4      o_year,
     5      sum(amount) as sum_profit
     6  from
     7      (
     8          select
     9              n_name as nation,
    10              extract(year from o_orderdate) as o_year,
    11              l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    12          from
    13              part,
    14              supplier,
    15              lineitem,
    16              partsupp,
    17              orders,
    18              nation
    19          where
    20              s_suppkey = l_suppkey
    21              and ps_suppkey = l_suppkey
    22              and ps_partkey = l_partkey
    23              and p_partkey = l_partkey
    24              and o_orderkey = l_orderkey
    25              and s_nationkey = n_nationkey
    26              and p_name like '%pink%'
    27      ) as profit
    28  group by
    29      nation,
    30      o_year
    31  order by
    32      nation,
    33      o_year desc
    34  ;