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 | 261501018.07327 ALGERIA | 1997 | 452932004.640995 ALGERIA | 1996 | 457426404.118048 ALGERIA | 1995 | 457446260.629279 ALGERIA | 1994 | 453726425.030201 ALGERIA | 1993 | 449020851.910678 ALGERIA | 1992 | 454078605.921845 ARGENTINA | 1998 | 281825068.318534 ARGENTINA | 1997 | 476053131.826015 ARGENTINA | 1996 | 483156913.561408 ARGENTINA | 1995 | 472095367.369895 ARGENTINA | 1994 | 471192440.42186 ARGENTINA | 1993 | 476338308.499868 ARGENTINA | 1992 | 474291096.02373 BRAZIL | 1998 | 272428259.038718 BRAZIL | 1997 | 476113081.553683 BRAZIL | 1996 | 472094748.476606 BRAZIL | 1995 | 474310480.454687 BRAZIL | 1994 | 469344354.664537 BRAZIL | 1993 | 465327651.458598 BRAZIL | 1992 | 476127337.401695 CANADA | 1998 | 277271278.315222 CANADA | 1997 | 473900181.450196 CANADA | 1996 | 481455300.268675 CANADA | 1995 | 477151961.282261 CANADA | 1994 | 483103019.722485 CANADA | 1993 | 483313349.117681 CANADA | 1992 | 475387161.087568 CHINA | 1998 | 272899380.029755 CHINA | 1997 | 460020136.724532 CHINA | 1996 | 458072140.407282 CHINA | 1995 | 463658020.876174 CHINA | 1994 | 463908408.722916 CHINA | 1993 | 461089604.852408 CHINA | 1992 | 466437797.283861 EGYPT | 1998 | 270299361.613221 EGYPT | 1997 | 465997850.673413 EGYPT | 1996 | 470728164.500085 EGYPT | 1995 | 470505887.736127 EGYPT | 1994 | 466443649.156124 EGYPT | 1993 | 461669805.873305 EGYPT | 1992 | 467456104.341502 ETHIOPIA | 1998 | 272035884.542314 ETHIOPIA | 1997 | 450985928.409653 ETHIOPIA | 1996 | 454349937.151971 ETHIOPIA | 1995 | 456259910.265566 ETHIOPIA | 1994 | 454488939.442496 ETHIOPIA | 1993 | 455836533.515128 ETHIOPIA | 1992 | 453219129.258939 FRANCE | 1998 | 268146729.475325 FRANCE | 1997 | 454292764.192812 FRANCE | 1996 | 461536778.816213 FRANCE | 1995 | 468275710.909768 FRANCE | 1994 | 461678790.340089 FRANCE | 1993 | 459701274.836596 FRANCE | 1992 | 456003875.759476 GERMANY | 1998 | 279091105.159621 GERMANY | 1997 | 478111383.111823 GERMANY | 1996 | 485445834.541672 GERMANY | 1995 | 489741164.175827 GERMANY | 1994 | 477510158.055962 GERMANY | 1993 | 483310539.296593 GERMANY | 1992 | 481646009.400859 INDIA | 1998 | 277180002.311583 INDIA | 1997 | 472656582.521386 INDIA | 1996 | 472157523.042324 INDIA | 1995 | 478514510.78973 INDIA | 1994 | 481585953.709111 INDIA | 1993 | 475694763.888101 INDIA | 1992 | 474535252.377208 INDONESIA | 1998 | 273789504.21165 INDONESIA | 1997 | 464131624.776606 INDONESIA | 1996 | 462925887.320963 INDONESIA | 1995 | 471844813.315513 INDONESIA | 1994 | 461046611.916621 INDONESIA | 1993 | 462504123.691069 INDONESIA | 1992 | 460578047.638525 IRAN | 1998 | 268591305.629765 IRAN | 1997 | 463417139.495576 IRAN | 1996 | 460645356.556822 IRAN | 1995 | 454509320.684631 IRAN | 1994 | 457079258.116398 IRAN | 1993 | 455541134.955302 IRAN | 1992 | 463559471.502331 IRAQ | 1998 | 281310959.080698 IRAQ | 1997 | 476508805.269183 IRAQ | 1996 | 474697008.935032 IRAQ | 1995 | 482173641.275605 IRAQ | 1994 | 478859304.868095 IRAQ | 1993 | 481337537.599059 IRAQ | 1992 | 482109416.740499 JAPAN | 1998 | 277307731.585121 JAPAN | 1997 | 466637845.99161 JAPAN | 1996 | 466087719.371642 JAPAN | 1995 | 472186062.009824 JAPAN | 1994 | 474705711.205612 JAPAN | 1993 | 473973809.356046 JAPAN | 1992 | 473316948.700383 JORDAN | 1998 | 273427582.945043 JORDAN | 1997 | 457727322.33783 JORDAN | 1996 | 467830322.188203 JORDAN | 1995 | 465498409.674655 JORDAN | 1994 | 456458830.04712 JORDAN | 1993 | 466081491.117256 JORDAN | 1992 | 462255338.247336 KENYA | 1998 | 274136935.880294 KENYA | 1997 | 479474737.510442 KENYA | 1996 | 473758314.412302 KENYA | 1995 | 478639637.014716 KENYA | 1994 | 474517190.848698 KENYA | 1993 | 477271337.804648 KENYA | 1992 | 475883889.304107 MOROCCO | 1998 | 276760709.96657 MOROCCO | 1997 | 481662698.142162 MOROCCO | 1996 | 474182192.24976 MOROCCO | 1995 | 480162941.400073 MOROCCO | 1994 | 470613874.552863 MOROCCO | 1993 | 479307523.169661 MOROCCO | 1992 | 476913944.076349 MOZAMBIQUE | 1998 | 272374393.61356 MOZAMBIQUE | 1997 | 457966478.987514 MOZAMBIQUE | 1996 | 461244395.645548 MOZAMBIQUE | 1995 | 461431503.732249 MOZAMBIQUE | 1994 | 465645494.622902 MOZAMBIQUE | 1993 | 457694855.083047 MOZAMBIQUE | 1992 | 459375541.918586 PERU | 1998 | 281269048.826641 PERU | 1997 | 462392346.72878 PERU | 1996 | 461644007.553716 PERU | 1995 | 464417212.04596 PERU | 1994 | 457238781.375159 PERU | 1993 | 462583745.057205 PERU | 1992 | 465761503.394043 ROMANIA | 1998 | 270657976.817738 ROMANIA | 1997 | 476251592.748427 ROMANIA | 1996 | 471567599.016061 ROMANIA | 1995 | 470304705.12444 ROMANIA | 1994 | 471094879.478745 ROMANIA | 1993 | 467147363.326032 ROMANIA | 1992 | 468712356.256344 RUSSIA | 1998 | 283784899.42094 RUSSIA | 1997 | 477481131.466628 RUSSIA | 1996 | 482040413.2744 RUSSIA | 1995 | 470669648.759995 RUSSIA | 1994 | 477739645.734865 RUSSIA | 1993 | 480456207.329304 RUSSIA | 1992 | 474590954.708572 SAUDI ARABIA | 1998 | 272117295.105875 SAUDI ARABIA | 1997 | 464512006.329076 SAUDI ARABIA | 1996 | 471577925.580572 SAUDI ARABIA | 1995 | 468793018.508828 SAUDI ARABIA | 1994 | 465840850.489563 SAUDI ARABIA | 1993 | 453381343.33722 SAUDI ARABIA | 1992 | 470716008.462523 UNITED KINGDOM | 1998 | 270178590.070844 UNITED KINGDOM | 1997 | 461358628.243191 UNITED KINGDOM | 1996 | 469050128.858262 UNITED KINGDOM | 1995 | 466109340.427341 UNITED KINGDOM | 1994 | 457289850.654267 UNITED KINGDOM | 1993 | 465249929.53054 UNITED KINGDOM | 1992 | 463838307.215347 UNITED STATES | 1998 | 272353364.966572 UNITED STATES | 1997 | 472164711.382463 UNITED STATES | 1996 | 468542032.166106 UNITED STATES | 1995 | 470642496.271048 UNITED STATES | 1994 | 475454211.8691 UNITED STATES | 1993 | 470898909.757521 UNITED STATES | 1992 | 463550992.457067 VIETNAM | 1998 | 266445034.502073 VIETNAM | 1997 | 459649125.367667 VIETNAM | 1996 | 454050204.578335 VIETNAM | 1995 | 457560885.521662 VIETNAM | 1994 | 453313187.239748 VIETNAM | 1993 | 460312293.945609 VIETNAM | 1992 | 460445851.648454 (175 rows) COMMIT; COMMIT