Query with multiple foreign keys to the same table
I've updated the Fiddle with your initial query and 2 alternatives.

This is the initial query plan:



Using OUTER APPLY instead of the nested SELECT statements.

select created_at, prior_dd_rcvd_status_id, 
    new_dd_rcvd_status_id, prior_dd_status, new_dd_status, filing_id
from ES_LOG
outer apply (
    select dd_doc_status_name as prior_dd_status 
    from ES_DOCUMENT_STATUSES where dd_doc_status_id = prior_dd_rcvd_status_id
) a
outer apply (
    select dd_doc_status_name as new_dd_status 
    from ES_DOCUMENT_STATUSES where dd_doc_status_id = new_dd_rcvd_status_id
) b
where filing_id = 988;


The query plan gets simplified:



And using LEFT OUTER JOIN instead of the OUTER APPLY.

select created_at, prior_dd_rcvd_status_id, 
    new_dd_rcvd_status_id, 
    a.dd_doc_status_name as prior_dd_status, 
    b.dd_doc_status_name as new_dd_status, 
    filing_id
from ES_LOG
left outer join ES_DOCUMENT_STATUSES a 
    on a.dd_doc_status_id = prior_dd_rcvd_status_id
left outer join ES_DOCUMENT_STATUSES b 
    on b.dd_doc_status_id = new_dd_rcvd_status_id
where filing_id = 988;


With an even more simplified query plan:



However, as you can see, the table scans take most of the time. So I'm not sure how much performance you could gain by picking either of the alternatives.

Further improvements require the use of indexes (Table Scan vs Index Scan). Deciding which index to provide depends not only on this query, but also the general design of how these tables will be used for other queries and CRUD operations. I would advise you learn about them.