Oracle-DB, Joining 3 Tables Limiting the second without using LATERAL() or nesting

Hello everyone I have a kind of specific problem. I am using Oracle DB

For the example I will use the Northwind Database.

I want to join the Tables: Customers, Orders, Orderdetails and Products.

Simple solution:

SELECT Customers.customerid, Customers.companyname, 
       Orders.orderid, Orders.orderdate, 
       Products.productname, Products.unitprice
FROM Customers
INNER JOIN Orders
  ON Customers.customerid= Orders.customerid
INNER JOIN Orderdetails 
  ON Orders.orderid = Orderdetails.orderid
INNER JOIN Products 
  ON Orderdetails.productid = Products.productid

Now I want to Limit the output to the newest 3 Orders per Customer, showing only the most expensive Product of the order. The uutput would look roughly like this (Edit: Table doesn't show correctly) :

I have a solution using LATERAL() and a subselect, but especially on the big data I want to use this for, LATERAL() has a lot of costs and makes it quite slow. So I want to have a solution WITHOUT LATERAL()

Another solution I've been trying is using ROW_NUMBER() which is a lot faster:

Select * FROM( 
  SELECT Customers.customerid, Customers.companyname, 
         Orders.orderid, Orders.orderdate, 
         Products.productname, Products.unitprice,
         ROW_NUMBER() OVER (PARTITION BY Customers.customerid
                            ORDER BY o.orderdate desc, p.unitprice desc ) AS rn
        ...
 ) WHERE rn <= 3;

Now if the limit was 1, there would be no problem. But I can't find an acceptable solution for a limit >= 2 for the orders, like I explained above. I tried a second ROW_NUMBER() OVER (PARTIONION BY Orders.orderid ...), but to make this work, I would need some nesting. The problem about nesting is, that I am about to make something kind of like a parser for an application. And too much nesting may get too complicated and confusing (Escpecially for the purpose of maintainance).

So I was wondering for a join, is there a pure SQL Solution in Oracle to:

• Join 3 or more Tables.
• Use a Limit >= 2 on a table (B) in the middle of the Join that has multiple joins with another tablefollowing it.
• That doesn't use LATERAL()
• Is not requiering nesting ?

You can use an in-line view within the FROM clause to filter the joined tables.

Something like:

SELECT c.customer_id,
       c.company_name, 
       o.order_id,
       o.order_date, 
       odp.product_name,
       odp.unit_price
FROM   Customers c
       INNER JOIN (
         SELECT customer_id,
                order_id,
                order_date,
                ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
         FROM   Orders
       ) o
       ON c.customer_id= o.customer_id AND o.rn <= 3
       INNER JOIN (
         SELECT od.order_id,
                p.product_name,
                p.unit_price,
                ROW_NUMBER() OVER (PARTITION BY od.order_id ORDER BY p.unit_price DESC) AS rn
         FROM   Order_details od
                INNER JOIN Products p
                ON od.product_id = p.product_id
       ) odp
       ON o.order_id = odp.order_id AND odp.rn = 1
ORDER BY customer_id, order_date DESC

However, you should be able to use an equivalent LATERAL join:

SELECT c.customer_id,
       c.company_name, 
       o.order_id,
       o.order_date, 
       odp.product_name,
       odp.unit_price
FROM   Customers c
       CROSS JOIN LATERAL (
         SELECT order_id,
                order_date
         FROM   Orders o
         WHERE  c.customer_id = o.customer_id
         FETCH FIRST 3 ROWS ONLY
       ) o
       CROSS JOIN LATERAL (
         SELECT od.order_id,
                p.product_name,
                p.unit_price
         FROM   Order_details od
                INNER JOIN Products p
                ON od.product_id = p.product_id
         WHERE  o.order_id = od.order_id
         FETCH FIRST ROW ONLY
       ) odp

fiddle