---

WITH step1 AS (
    SELECT DISTINCT ON (
        np.contract_id,
        np.numb_of_monts,
        np.payment_type_code
    )
        np.contract_id,
        c.number AS contract_number,
        i.insurance_number,
        np.payment_type_code,
        np.numb_of_monts,
        np.effective_date
    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 regexp_replace(
              i.insurance_number, '[^0-9]', '', 'g'
          ) = '03994124684'
      AND np.effective_date >= DATE '2026-06-01'
      AND np.effective_date <= DATE '2026-06-30'
      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.numb_of_monts,
        np.effective_date DESC
),
step2 AS (
    SELECT
        *,
        numb_of_monts - lag(numb_of_monts) OVER (
            PARTITION BY contract_id, payment_type_code
            ORDER BY numb_of_monts
        ) AS payment_gap
    FROM step1
)
SELECT
    *,
    coalesce(
        payment_gap > 1 OR payment_gap < 0,
        false
    ) AS passes_final_filter
FROM step2
ORDER BY contract_id, payment_type_code, numb_of_monts;