Optimizing Sub-queries in SQL join [closed]

It's impossible to optimize a query without access to the real query, table and index structures, and execution plan. However simplifying it to remove the subqueries and reduce the number of joins is unlikely to hurt, and might give you a better place to start investigating the plan and look for real optimization opportunities.

It also doesn't help that your 'simplified, fake' version uses illegal identifiers and has various other errors (including needing outer joins to get all five rows back), which you said to ignore but as Mitch commented, those things and the general fakeness make it kind of useless. Still, as a general idea of where to start, perhaps, with your example you could rewrite it using conditional aggregation, for example as:

SELECT 
o.order_num
,o.part
,o.status
,MIN(CASE WHEN oh.message = 'Created' AND oh.NewStatus = 'Planned' THEN oh.timestamp END) AS cDate
,MIN(CASE WHEN oh.message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.timestamp END) AS frDate
,MAX(CASE WHEN oh.message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.timestamp END) AS lrDate
,MAX(CASE WHEN oh.message = 'Status Changed' AND oh.NewStatus = 'Closed' THEN oh.timestamp END) AS cdDate
FROM orders o
LEFT JOIN order_history oh
ON o.order_num = oh.order_num
GROUP BY o.order_num, o.part, o.status
ORDER BY o.order_num, o.part, o.status

fiddle including a working version of your query with legal identifiers (though 'timestamp' still isn't ideal) and other changes, and this modified query.

(This works with the 'timestamp' as strings with your fake values, but would also work with real dates or timestamps, or course. And it doesn't necessarily need to be an outer join if there is always a 'Created' record, which seems likely.)

You can use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT m.order_num,
       o.part,
       m.status,
       m.cdate,
       m.frdate,
       m.lrdate,
       m.cddate
FROM   order_history
       MATCH_RECOGNIZE(
         PARTITION BY order_num
         ORDER BY timestamp
         MEASURES
           LAST(newstatus)           AS status,
           FIRST(timestamp)          AS cdate,
           FIRST(released.timestamp) AS frdate,
           LAST(released.timestamp)  AS lrdate,
           LAST(closed.timestamp)    AS cddate
         PATTERN (created (planned|released|closed)*)
         DEFINE
           created  AS message = 'Created',
           planned  AS message = 'Status Changed' AND newstatus = 'Planned',
           released AS message = 'Status Changed' AND newstatus = 'Released',
           closed   AS message = 'Status Changed' AND newstatus = 'Closed'
       ) m
       INNER JOIN orders o
       ON o.order_num = m.order_num

Note: The only thing the orders table is necessary for is the part column and everything else can be generated from the order_history table; so if you don't need that column then you can eliminate the join.

Which, borrowing Alex Poole's sample data, outputs:

fiddle