---

SELECT
    count(*) AS total_spv,

    count(*) FILTER (
        WHERE opaa.decision_payment_type_code IS NULL
    ) AS without_app_decision_code,

    count(*) FILTER (
        WHERE opaa.decision_payment_type_code IS NOT NULL
    ) AS with_app_decision_code,

    count(*) FILTER (
        WHERE aibd.id IS NOT NULL
    ) AS with_bank_detail,

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

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

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

    count(*) FILTER (
        WHERE EXISTS (
            SELECT 1
            FROM back_office.ops_payment_decision opd
            WHERE opd.ops_pension_assignment_application_id = opaa.id
              AND opd.decision_payment_type_code = '02'
        )
    ) AS with_spv_decision

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';