BEGIN; BEGIN select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from 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 '%pink%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261805778.766613 ALGERIA | 1997 | 453117752.565196 ALGERIA | 1996 | 457691875.041363 ALGERIA | 1995 | 457907552.278786 ALGERIA | 1994 | 454497796.312146 ALGERIA | 1993 | 449285748.763689 ALGERIA | 1992 | 454729037.166168 ARGENTINA | 1998 | 282028460.215042 ARGENTINA | 1997 | 476571751.757854 ARGENTINA | 1996 | 483697821.134282 ARGENTINA | 1995 | 472390429.958679 ARGENTINA | 1994 | 471998223.671242 ARGENTINA | 1993 | 476318242.509371 ARGENTINA | 1992 | 474526831.949607 BRAZIL | 1998 | 272706173.46447 BRAZIL | 1997 | 476258453.872012 BRAZIL | 1996 | 472198104.603917 BRAZIL | 1995 | 475211368.109148 BRAZIL | 1994 | 469417540.146107 BRAZIL | 1993 | 465584617.378562 BRAZIL | 1992 | 476516296.988188 CANADA | 1998 | 277812085.081057 CANADA | 1997 | 474781722.591127 CANADA | 1996 | 481887761.185298 CANADA | 1995 | 478037679.300844 CANADA | 1994 | 483536732.909385 CANADA | 1993 | 483768801.513178 CANADA | 1992 | 475736978.876953 CHINA | 1998 | 273012410.309319 CHINA | 1997 | 460143445.20971 CHINA | 1996 | 458734273.52165 CHINA | 1995 | 464081933.386148 CHINA | 1994 | 464410291.104741 CHINA | 1993 | 461665977.841246 CHINA | 1992 | 467001957.61536 EGYPT | 1998 | 270799055.212957 EGYPT | 1997 | 466709709.149971 EGYPT | 1996 | 471144119.107713 EGYPT | 1995 | 470924070.837272 EGYPT | 1994 | 466352318.386728 EGYPT | 1993 | 462686168.854195 EGYPT | 1992 | 467616094.233641 ETHIOPIA | 1998 | 272169516.545657 ETHIOPIA | 1997 | 451540001.79631 ETHIOPIA | 1996 | 454825068.955438 ETHIOPIA | 1995 | 456520842.147616 ETHIOPIA | 1994 | 454714765.625034 ETHIOPIA | 1993 | 456362474.242864 ETHIOPIA | 1992 | 453545739.979745 FRANCE | 1998 | 268648605.080387 FRANCE | 1997 | 454687528.03976 FRANCE | 1996 | 462091834.929905 FRANCE | 1995 | 468681437.133963 FRANCE | 1994 | 461717323.28358 FRANCE | 1993 | 460627905.137734 FRANCE | 1992 | 456432716.118368 GERMANY | 1998 | 279376337.697652 GERMANY | 1997 | 478612520.82904 GERMANY | 1996 | 485613017.929218 GERMANY | 1995 | 490586250.310193 GERMANY | 1994 | 477886250.875375 GERMANY | 1993 | 483857113.413539 GERMANY | 1992 | 482519077.121186 INDIA | 1998 | 277583467.678216 INDIA | 1997 | 473342975.83436 INDIA | 1996 | 472831650.730139 INDIA | 1995 | 478694930.876501 INDIA | 1994 | 482170792.579031 INDIA | 1993 | 475931560.922049 INDIA | 1992 | 474643383.874189 INDONESIA | 1998 | 274354389.915505 INDONESIA | 1997 | 464658377.924197 INDONESIA | 1996 | 463323042.744553 INDONESIA | 1995 | 472300352.663785 INDONESIA | 1994 | 461987730.079104 INDONESIA | 1993 | 462727469.002739 INDONESIA | 1992 | 460858420.769386 IRAN | 1998 | 269113481.117817 IRAN | 1997 | 464239642.702911 IRAN | 1996 | 461251088.851937 IRAN | 1995 | 455112965.882846 IRAN | 1994 | 457305421.500989 IRAN | 1993 | 456103465.575611 IRAN | 1992 | 464149584.171501 IRAQ | 1998 | 281660864.901725 IRAQ | 1997 | 476845854.340149 IRAQ | 1996 | 475114994.420865 IRAQ | 1995 | 482232359.385752 IRAQ | 1994 | 479557697.30157 IRAQ | 1993 | 481765720.125988 IRAQ | 1992 | 482438745.854271 JAPAN | 1998 | 277567408.175296 JAPAN | 1997 | 467037843.612154 JAPAN | 1996 | 466894082.315547 JAPAN | 1995 | 472885931.358756 JAPAN | 1994 | 475193148.95109 JAPAN | 1993 | 474404239.812131 JAPAN | 1992 | 473956570.113105 JORDAN | 1998 | 273550582.163508 JORDAN | 1997 | 458238274.589262 JORDAN | 1996 | 468298570.743831 JORDAN | 1995 | 465955109.600296 JORDAN | 1994 | 456770002.626192 JORDAN | 1993 | 466078445.573905 JORDAN | 1992 | 462734853.668494 KENYA | 1998 | 274512650.888439 KENYA | 1997 | 480050077.551405 KENYA | 1996 | 474437226.802615 KENYA | 1995 | 479002994.926724 KENYA | 1994 | 475159350.380852 KENYA | 1993 | 477662368.367606 KENYA | 1992 | 476456461.810561 MOROCCO | 1998 | 276966939.398301 MOROCCO | 1997 | 482391051.532282 MOROCCO | 1996 | 474285744.627446 MOROCCO | 1995 | 480799661.930463 MOROCCO | 1994 | 470783000.943256 MOROCCO | 1993 | 479712235.498121 MOROCCO | 1992 | 477555219.141889 MOZAMBIQUE | 1998 | 272456245.125542 MOZAMBIQUE | 1997 | 458772389.840835 MOZAMBIQUE | 1996 | 461880228.469979 MOZAMBIQUE | 1995 | 461796356.955555 MOZAMBIQUE | 1994 | 465508003.864258 MOZAMBIQUE | 1993 | 458119772.293422 MOZAMBIQUE | 1992 | 459818194.783946 PERU | 1998 | 281671819.814089 PERU | 1997 | 462706146.578648 PERU | 1996 | 462163086.598141 PERU | 1995 | 465335076.013677 PERU | 1994 | 457658961.931042 PERU | 1993 | 462949277.898226 PERU | 1992 | 466335055.899589 ROMANIA | 1998 | 271159006.953897 ROMANIA | 1997 | 476641621.261389 ROMANIA | 1996 | 471941358.216075 ROMANIA | 1995 | 470789817.139154 ROMANIA | 1994 | 471668267.422568 ROMANIA | 1993 | 467524592.687435 ROMANIA | 1992 | 468946156.403616 RUSSIA | 1998 | 284449303.49202 RUSSIA | 1997 | 477829866.689471 RUSSIA | 1996 | 482316588.126346 RUSSIA | 1995 | 471427662.245521 RUSSIA | 1994 | 478063236.292116 RUSSIA | 1993 | 481027823.525201 RUSSIA | 1992 | 475342688.206153 SAUDI ARABIA | 1998 | 272364493.89408 SAUDI ARABIA | 1997 | 464854315.545308 SAUDI ARABIA | 1996 | 472336555.408322 SAUDI ARABIA | 1995 | 469273955.079004 SAUDI ARABIA | 1994 | 466129055.70751 SAUDI ARABIA | 1993 | 453847781.434688 SAUDI ARABIA | 1992 | 471210352.809178 UNITED KINGDOM | 1998 | 270196275.954177 UNITED KINGDOM | 1997 | 461979316.986824 UNITED KINGDOM | 1996 | 469093422.611829 UNITED KINGDOM | 1995 | 466333496.738945 UNITED KINGDOM | 1994 | 457662089.868597 UNITED KINGDOM | 1993 | 466011689.107442 UNITED KINGDOM | 1992 | 464046242.551872 UNITED STATES | 1998 | 272542687.214647 UNITED STATES | 1997 | 472486787.271167 UNITED STATES | 1996 | 468920309.767304 UNITED STATES | 1995 | 471554971.534121 UNITED STATES | 1994 | 475624926.747427 UNITED STATES | 1993 | 471075275.549362 UNITED STATES | 1992 | 464159008.854765 VIETNAM | 1998 | 266630284.489018 VIETNAM | 1997 | 460167632.493807 VIETNAM | 1996 | 454599674.701251 VIETNAM | 1995 | 457887526.628938 VIETNAM | 1994 | 453543132.567576 VIETNAM | 1993 | 461204753.705017 VIETNAM | 1992 | 460658095.750273 (175 rows) COMMIT; COMMIT