Sql for learning join, complex query pattern

select pfo.id as order_id, oli.id, oli.qty as required_qty, count(assigned_si.id) as assigned_qty, sum(olivs.confirmed_qty) as confirmed_qty
from (
    select * 
    from orders 
    where status = 5
) as pfo
inner join order_line_items as oli on pfo.id = oli.order_id
left join (
    select * 
    from stock_items 
    where status = 3
) as assigned_si on pfo.id = assigned_si.order_id and oli.stock_record_id = assigned_si.stock_record_id 
left join (
    select order_line_item_id, confirmed_qty
    from order_line_items_virtual_stocks olivs
    left join purchase_orders as po on olivs.purchase_order_id = po.id
    where olivs.purchase_order_id is null or po.status < 15 
) as olivs on oli.id = olivs.order_line_item_id
group by pfo.id, oli.id, oli.qty