TPC series - TPC-H Query 8, 10 and 12 - Join order Training

Query 8

SELECT o_year,
       SUM(CASE
           WHEN nation = 'GERMANY'
               THEN volume
           ELSE 0
           END) / SUM(volume) AS mkt_share
FROM (SELECT EXTRACT(YEAR FROM o_orderdate)     AS o_year,
             l_extendedprice * (1 - l_discount) AS volume,
             n2.n_name                          AS nation
      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 n2 ON n2.n_nationkey = s_nationkey
      INNER JOIN tpch.orders ON l_orderkey = o_orderkey
      INNER JOIN tpch.customer ON o_custkey = c_custkey
      INNER JOIN tpch.nation n1 ON c_nationkey = n1.n_nationkey
      INNER JOIN region ON n1.n_regionkey = r_regionkey
      WHERE r_name = 'EUROPE'
        AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
        AND p_type = 'MEDIUM PLATED BRASS') AS all_nations
GROUP BY o_year
ORDER BY o_year;

Query 10

SELECT c_custkey,
       c_name,
       SUM(l_extendedprice * (1 - l_discount)) AS revenue,
       c_acctbal,
       n_name,
       c_address,
       c_phone,
       c_comment
FROM tpch.lineitem
INNER JOIN tpch.orders ON l_orderkey = o_orderkey
INNER JOIN tpch.customer ON o_custkey = c_custkey
INNER JOIN tpch.nation ON c_nationkey = n_nationkey
WHERE o_orderdate >= '1994-03-01'
  AND o_orderdate < '1994-06-01'
  AND l_returnflag = 'R'
  AND
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC

Query 12

SELECT l_shipmode,
       SUM(CASE
           WHEN o_orderpriority = '1-URGENT'
               OR o_orderpriority = '2-HIGH'
               THEN 1
           ELSE 0
           END) AS high_line_count,
       SUM(CASE
           WHEN o_orderpriority <> '1-URGENT'
               AND o_orderpriority <> '2-HIGH'
               THEN 1
           ELSE 0
           END) AS low_line_count
FROM tpch.lineitem
INNER JOIN tpch.orders ON l_orderkey = o_orderkey
WHERE l_shipmode IN ('AIR', 'TRUCK')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= '1994-01-01'
  AND l_receiptdate < '1995-01-01'
GROUP BY l_shipmode
ORDER BY l_shipmode;