SELECT nation, o_year, sum(1) sum_profit
FROM (SELECT n_name nation, EXTRACT(YEAR FROM o_orderdate) o_year, l_extendedprice - ps_supplycost * l_quantity amount
FROM /users/composite/drill/ds/tpchorcl234/C##TPCH10/H_part, /users/composite/drill/ds/tpchorcl236/C##TPCH10/H_supplier, /users/composite/drill/ds/tpchorcl234/C##TPCH10/H_lineitem, /users/composite/drill/ds/impala20_247/tpch/partsupp, /users/composite/drill/ds/tpchorcl234/C##TPCH10/H_order, /users/composite/drill/ds/impala20_247/tpch/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 s_nationkey < 19) AND n_nationkey > 5) AND p_name LIKE '%dodger%')) profit
GROUP BY nation, o_year
ORDER BY nation ASC, o_year DESC |