---

WITH step1 AS (
    -- Последняя запись внутри выбранного периода
    -- по каждому договору и типу выплаты.
    SELECT DISTINCT ON (
        np.contract_id,
        np.payment_type_code
    )
        np.contract_id,
        np.numb_of_monts,
        np.effective_date,
        np.ops_payment_decision_id AS decision_id,
        np.payment_type_code,
        c.number AS contract_number,
        i.id AS individual_id,
        i.last_name,
        i.first_name,
        i.middle_name,
        i.last_name || ' ' || i.first_name || ' '
            || coalesce(i.middle_name, '') AS fio,
        i.insurance_number
    FROM back_office.ops_number_of_payments np
    JOIN ourpension.contract c
        ON c.id = np.contract_id
    JOIN ourpension.sharer sh
        ON sh.contract_id = np.contract_id
    JOIN ourpension.individual i
        ON i.id = sh.individual_id
    JOIN ourpension.ops_pension_assignment_application pa
        ON pa.sharer_id = sh.id
    LEFT JOIN ourpension.application_death_info adi
        ON adi.individual_id = i.id
    WHERE np.effective_date >= cast(:filter_period_date_from AS date)
      AND np.effective_date < cast(:filter_period_date_to AS date)
                              + INTERVAL '1 day'
      AND pa.accepted IS NOT NULL
      AND pa.rejected IS NULL
      AND pa.canceled IS NULL
      AND adi.id IS NULL
    ORDER BY
        np.contract_id,
        np.payment_type_code,
        np.effective_date DESC,
        np.id DESC
),
step2 AS (
    -- Разница между последним значением за период
    -- и последним значением перед началом периода.
    SELECT
        s.*,
        s.numb_of_monts - prev.numb_of_monts AS payment_gap
    FROM step1 s
    LEFT JOIN LATERAL (
        SELECT np.numb_of_monts
        FROM back_office.ops_number_of_payments np
        WHERE np.contract_id = s.contract_id
          AND np.payment_type_code
              IS NOT DISTINCT FROM s.payment_type_code
          AND np.effective_date
              < cast(:filter_period_date_from AS date)
        ORDER BY
            np.effective_date DESC,
            np.id DESC
        LIMIT 1
    ) prev ON true
)