---

SELECT
    count(*) AS total_spv,

    count(*) FILTER (
        WHERE opaa.bank_detail_id IS NOT NULL
    ) AS bank_detail_id_exists,

    count(*) FILTER (
        WHERE opaa.bank_account_id IS NOT NULL
    ) AS bank_account_id_exists,

    count(*) FILTER (
        WHERE aibd.personal_account IS NOT NULL
    ) AS old_personal_account_exists,

    count(*) FILTER (
        WHERE aibd.bic IS NOT NULL
    ) AS old_bic_exists,

    count(*) FILTER (
        WHERE aibd.accepted IS NOT NULL
    ) AS br_date_exists

FROM ourpension.ops_pension_assignment_application opaa

LEFT JOIN ourpension.application_individual_bank_detail aibd
    ON aibd.id = opaa.bank_detail_id

WHERE opaa.ops_payment_type_code = '05'
  AND opaa.registration >= CURRENT_DATE - INTERVAL '30 days';