select op_main.id as opportunity_id,  u.id as main_user_id, c.id as certificate_id, required2.required as required_certificates

from opportunities as op_main
join opportunity_certificates as oc on oc.opportunity_id = op_main.id
join certificates as c on c.id = oc.certificate_id and oc.is_required
join user_certificates as uc on uc.certificate_id = c.id
join users as u on u.id = uc.user_id

join (

    select id as op_id, (
      select count(distinct c.id)
     from opportunities as op
     join opportunity_certificates as oc on oc.opportunity_id = op.id
     join certificates as c on c.id = oc.certificate_id and oc.is_required
     join user_certificates as uc on uc.certificate_id = c.id
     join users as u on u.id = uc.user_id
     where uc.certificate_id = oc.certificate_id and oc.is_required and op.id = op_id
    ) as required from opportunities

 ) as required2 on required2.op_id = op_main.id

where uc.certificate_id = oc.certificate_id and oc.is_required and op_id = op_main.id