• performance
• query‑planning
• tpch

1 min

1/1/2025

Listen

TPC series - TPC-H Query 9 - Composite Key Joins

Query 9

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 tpch.lineitem
      INNER JOIN tpch.part ON l_partkey = p_partkey
      INNER JOIN tpch.supplier ON l_suppkey = s_suppkey
      INNER JOIN tpch.nation ON s_nationkey = n_nationkey
      INNER JOIN tpch.partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
      INNER JOIN tpch.orders ON l_orderkey = o_orderkey
      WHERE p_name LIKE '%grey%') AS profit
GROUP BY nation,
         o_year
ORDER BY nation,
         o_year DESC