calculate count from 4 sources, joins and exclusions

Question

I want to calculate count of orders with its status :

1. in progress
2. completed
3. pending deletion
4. deleted

i have 4 sources for each data

• source of truth as st

ORDER ID	OrderUNIQUE_ID
123	123-a
456	456-a
789	789-a

• list of pending deletion as PD

ORDER ID	ststus
456	pending deletion

• manually updated table with Orders as MO

ORDER ID	ststus
123	in progress
456	pending deletion
789	in progress
123	deleted

• list of DELETED (this table contains orders in case that they have been deleted and they are not present anymore in source of truth table)

ORDER ID	ORDER_unique_ID
123	123-b

Each table has columns Order, st, PD, and MO they have column : status and source of truth and DELETED : unique_order_ID

Initial query that i was thinking about is :

with
   basis as
(
   SELECT
      st.orders,
      MO.orders_status 
   FROM
      st
   left join
      PD 
         on st.orders = PD.orders
   left join
      MO      
         on st.oders = MO.orders
   where
          PD.status <> "Pending_deletion"
      and MO.orders not in (
            Select distinct orders from LIST of Deleted
          )
),    
   --list of orders Completed
   completed as 
(
   SELECT
      b.orders
   from
      basis as b 
   WHERE     
      status = "Completed"
),
   --list of orders In progress 
   in_progress as
(
   SELECT
      b.orders
   from
      basis as b 
   WHERE
      status = "In progress"
), 
   --list of pending deletion 
   pending_deletion as 
(
   select distinct
     orders
   from
     pending_deletion
),
   --list of deleted SAs     
   deleted as 
(
   select distinct
      orders
   from deleted
)
select
   *
from 
(
   select
      'pending deletion' as name, 
      count(*) as count
   from
      pending_deletion

   UNION ALL
 
   select
      'Completed' as name,
      count(*) as count
   from
      Completed

   UNION ALL

   select
      'in progress' as name,
      count(*) as count
   from
      in_progress

   UNION ALL

   select
      'deleted' as name,
      count(*) as count
   from
      in_deleted
)     
order by
   3

count of orders:

• in progress
• Completed
• Pending deletion
• deleted

There is are some limiations :

• in case that order is in deleted is not present on source of truth -Some of orders they can have both statuses 'deleted' list and on 'in progress' , "completed" because with the same name order was deleted but new created (BUT they have different unique ID to distinguish them) so somehow maybe they can be calculated in both of the tables ? but how to add this info ?