
SELECT
   n_name AS nation,
   EXTRACT ( YEAR FROM o_orderdate) AS o_year,
   SUM (l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity) AS sum_profit
  FROM
   part,
   partsupp,
   lineitem table option (index r_l_pksk),
   orders,
   supplier,
   nation
  WHERE
   s_suppkey = l_suppkey
   AND ps_suppkey = l_suppkey
   AND ps_partkey = l_partkey
   AND p_partkey = ps_partkey
   AND o_orderkey = l_orderkey
   AND s_nationkey = n_nationkey
   AND p_name LIKE '%green%'
GROUP BY
 nation,
 o_year
ORDER BY
 nation,
 o_year desc
option (order, loop);
