tajo-core/benchmark/tpch/q9.sql

select 
    nation,
    o_year,
    sum(amount) as sum_profit
  from (
    select
      n_name as nation,
      o_orderdate as o_year,
      l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    from
      part,
      supplier,
      lineitem,
      partsupp,
      orders,
      nation
    where
      s_suppkey = l_suppkey
      and ps_suppkey = l_suppkey
      and ps_partkey = l_partkey
      and p_partkey = l_partkey
      and o_orderkey = l_orderkey
      and s_nationkey = n_nationkey
      and p_name like 'green'
  ) as profit
group by 
  nation,
  o_year
order by 
  nation,
  o_year desc;