---

EXPLAIN (VERBOSE, COSTS)
WITH latest_death_info AS MATERIALIZED (
    SELECT DISTINCT ON (a.individual_id)
        a.individual_id,
        a.death_date
    FROM ourpension.application_death_info a
    WHERE a.accepted IS NOT NULL
      AND a.canceled IS NULL
      AND a.rejected IS NULL
    ORDER BY
        a.individual_id,
        a.version DESC
),
transferred_sharers AS MATERIALIZED (
    SELECT DISTINCT
        d.sharer_id,
        p.report_date
    FROM report.dia_param p
    JOIN back_office.operation o
        ON o.effective_date <= p.report_date
       AND o.approved IS NOT NULL
    JOIN back_office.ops_transfer_register tr
        ON tr.id = o.ops_transfer_register_id
    JOIN back_office.ops_transfer_details d
        ON d.ops_transfer_register_id = tr.id
)
SELECT
    s.id,
    death_info.death_date AS individual_death_date
FROM latest_death_info death_info
JOIN report.dia_param p
    ON death_info.death_date <= p.report_date
JOIN ourpension.sharer s
    ON s.individual_id = death_info.individual_id
JOIN ourpension.contract c
    ON c.id = s.contract_id
WHERE c.service_type = 'OPS'
  AND NOT EXISTS (
      SELECT 1
      FROM transferred_sharers transfer
      WHERE transfer.sharer_id = s.id
        AND transfer.report_date = p.report_date
  );