---

WITH x AS (
    SELECT
        opaa.decision_payment_type_code IS NOT NULL
            AS app_decision_code_exists,

        dpt.code IS NOT NULL
            AS app_decision_type_found,

        opaa.bank_detail_id IS NOT NULL
            AS bank_detail_id_exists,

        aibd.id IS NOT NULL
            AS bank_detail_found,

        aibd.personal_account IS NOT NULL
            AS personal_account_exists,

        aibd.bic IS NOT NULL
            AS bic_exists,

        aibd.accepted IS NOT NULL
            AS bank_detail_accepted,

        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 spv_decision_exists,

        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'
              AND opd.accepted IS NOT NULL
              AND opd.rejected IS NULL
              AND opd.canceled IS NULL
        ) AS accepted_spv_decision_exists

    FROM ourpension.ops_pension_assignment_application opaa

    LEFT JOIN ourpension.decision_payment_type dpt
        ON dpt.code = opaa.decision_payment_type_code

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

SELECT
    app_decision_code_exists,
    app_decision_type_found,
    bank_detail_id_exists,
    bank_detail_found,
    personal_account_exists,
    bic_exists,
    bank_detail_accepted,
    spv_decision_exists,
    accepted_spv_decision_exists,
    count(*) AS cnt
FROM x
GROUP BY
    app_decision_code_exists,
    app_decision_type_found,
    bank_detail_id_exists,
    bank_detail_found,
    personal_account_exists,
    bic_exists,
    bank_detail_accepted,
    spv_decision_exists,
    accepted_spv_decision_exists;