How to convert a query into a nested query

How do I change this query into a nested query? The query and tables are listed below.

SELECT 
  Nation.N_NAME as "nation", 
  ROUND(
    SUM(
      Lineitem.L_QUANTITY * (Lineitem.L_EXTENDEDPRICE - Lineitem.L_DISCOUNT)
    ), 2
  ) AS "order size"
FROM Nation
JOIN Supplier ON Nation.N_NATIONKEY = Supplier.S_NATIONKEY
JOIN Customer ON Supplier.S_NATIONKEY = Customer.C_NATIONKEY
JOIN Orders ON Customer.C_CUSTKEY = Orders.O_CUSTKEY
JOIN Lineitem ON Orders.O_ORDERKEY = Lineitem.L_ORDERKEY
WHERE Lineitem.L_SUPPKEY = Supplier.S_SUPPKEY
GROUP BY Nation.N_NAME
;

tables goes as follows

Nation : N_NATIONKEY, N_NAME

Supplier : S_SUPPKEY, S_NAME, S_NATIONKEY

Customer : C_CUSTKEY, C_NAME, C_NATIONKEY

Orders: O_ORDERKEY, O_CUSTKEY

Lineitem: L_ORDERKEY, L_SUPPKEY, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT

Answer

I’m not sure exactly what kind of nested join you’re looking for, but here is one option:

SELECT
  src.N_NAME as "nation", 
  ROUND(
    SUM(
      lineitem.L_QUANTITY * (lineitem.L_EXTENDEDPRICE - lineitem.L_DISCOUNT)
    ), 2
  ) AS "order size"
FROM lineitem -- Get line items
INNER JOIN (
  SELECT nation.N_NAME, supplier.S_SUPPKEY, Orders.O_ORDERKEY
  FROM nation
  JOIN Supplier ON Nation.N_NATIONKEY = Supplier.S_NATIONKEY
  JOIN Customer ON Supplier.S_NATIONKEY = Customer.C_NATIONKEY
  JOIN Orders ON Customer.C_CUSTKEY = Orders.O_CUSTKEY
) src ON lineitem.L_SUPPKEY = src.S_SUPPKEY AND lineitem.L_ORDERKEY = src.O_ORDERKEY
GROUP BY src.N_NAME

I haven’t tested it but give it a try and see if it works. If it doesn’t give you what you want, please post some sample data.