---

SELECT
    original.payment_type AS original_payment_type,
    newer.payment_type AS newer_payment_type,
    coalesce(
        newer.canceled::date <= DATE '2026-06-30', false
    ) AS canceled_by_report_date,
    coalesce(
        newer.rejected::date <= DATE '2026-06-30', false
    ) AS rejected_by_report_date,
    count(*) AS pensioners,
    count(*) FILTER (
        WHERE ROW(original.bic,
                  original.personal_account,
                  original.payment_way)
              IS DISTINCT FROM
              ROW(newer.bic,
                  newer.personal_account,
                  newer.payment_way)
    ) AS with_changed_details
FROM pens p
JOIN ourpension.application_individual_bank_detail original
    ON original.id = p.bank_detail_id
JOIN LATERAL (
    SELECT br.*
    FROM ourpension.application_individual_bank_detail br
    WHERE br.sharer_id = p.sharer_id
      AND br.id <> original.id
      AND br.accepted > original.accepted
      AND br.accepted::date <= DATE '2026-06-30'
    ORDER BY br.accepted DESC, br.id DESC
    LIMIT 1
) newer ON true
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4;