---

WITH hist AS MATERIALIZED (
    SELECT DISTINCT ON (h.contract_id)
        h.contract_id,
        h.period,
        h.pension_rule_id
    FROM ourpension.npo_contract_pension_rules_history h
    JOIN ourpension.contract c
        ON c.id = h.contract_id
    WHERE c.service_type IN ('NPO', 'PDS')
      AND h.period <= c.date
    ORDER BY
        h.contract_id,
        h.period DESC,
        h.version DESC
)
SELECT
    c.id AS contract_id,
    c.date AS contract_date,

    c.pension_rule_id AS current_rule_id,
    pr_current.edition_number AS current_rule,

    hist.period AS history_period,
    hist.pension_rule_id AS history_rule_id,
    pr_hist.edition_number AS history_rule

FROM hist

JOIN ourpension.contract c
    ON c.id = hist.contract_id

LEFT JOIN ourpension.pension_rule pr_current
    ON pr_current.id = c.pension_rule_id

LEFT JOIN ourpension.pension_rule pr_hist
    ON pr_hist.id = hist.pension_rule_id

WHERE hist.pension_rule_id IS DISTINCT FROM c.pension_rule_id

LIMIT 50;