---

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
)
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 back_office.ops_transfer_details d
      JOIN back_office.ops_transfer_register tr
          ON tr.id = d.ops_transfer_register_id
      JOIN back_office.operation o
          ON o.ops_transfer_register_id = tr.id
      WHERE d.sharer_id = s.id
        AND o.approved IS NOT NULL
        AND o.effective_date <= p.report_date
  );