
SELECT
                                                                             n_name  AS  nation,
                                                  EXTRACT ( YEAR FROM od_orderdate)  AS  o_year,
         SUM (lep_extendedprice * (1 - ld_discount) - psc_supplycost * lq_quantity)  AS  sum_profit
  FROM
         p_name,
         ps_supplycost,
         l_partkey,  
         l_suppkey, 
         l_extendedprice, 
         l_quantity, 
         l_discount,
         o_orderdate,
         s_nationkey,
         nation
  WHERE
           snk_suppkey = lsk_suppkey
   AND     psc_suppkey = lsk_suppkey
   AND     psc_partkey = pn_partkey
   AND      pn_partkey = lpk_partkey
   AND     od_orderkey = lpk_orderkey
   AND   snk_nationkey = n_nationkey
   AND  pn_name LIKE '%green%'
   AND    lpk_orderkey = lep_orderkey 
   AND  lpk_linenumber = lep_linenumber
   AND    lpk_orderkey = lq_orderkey 
   AND  lpk_linenumber = lq_linenumber
   AND    lpk_orderkey = ld_orderkey 
   AND  lpk_linenumber = ld_linenumber
   AND    lpk_orderkey = lsk_orderkey 
   AND  lpk_linenumber = lsk_linenumber

GROUP BY
          nation,
          o_year
ORDER BY
          nation,
          o_year desc
  OPTION (order, loop);
