---

AND NOT EXISTS (
    SELECT 1
    FROM ourpension.application_death_info adi
    WHERE adi.individual_id = ind.id
      AND adi.accepted IS NOT NULL
      AND adi.canceled IS NULL
)



SELECT
    c.service_type,
    CASE
        WHEN io.individual_id = c.individual_id
            THEN 'Вкладчик = участник'
        ELSE 'Вкладчик <> участник'
    END AS person_match,
    COALESCE(c.family_member, false) AS family_member,
    c.fm_relation_type,
    COUNT(DISTINCT (io.individual_id, io.contract_id)) AS contract_count
FROM back_office.incoming_order io
JOIN back_office.incoming_order_operation ioo
    ON ioo.incoming_order_id = io.id
JOIN back_office.operation o
    ON o.id = ioo.operation_id
JOIN back_office."transaction" t
    ON t.operation_id = ioo.operation_id
JOIN back_office.account a
    ON a.id = t.credit_account_id
JOIN back_office.account_type at
    ON at.id = a.account_type_id
JOIN ourpension.contract c
    ON c.id = io.contract_id
WHERE io.payment_return IS NOT TRUE
  AND EXTRACT(YEAR FROM o.operation_date) BETWEEN
      EXTRACT(YEAR FROM CURRENT_DATE) - 3
      AND EXTRACT(YEAR FROM CURRENT_DATE)
  AND (
        (c.service_type = 'NPO' AND at.mnemonics = 'ИПС_ФЛ')
        OR
        (c.service_type = 'PDS' AND at.mnemonics = 'ПДС СВ')
      )
GROUP BY
    c.service_type,
    CASE
        WHEN io.individual_id = c.individual_id
            THEN 'Вкладчик = участник'
        ELSE 'Вкладчик <> участник'
    END,
    COALESCE(c.family_member, false),
    c.fm_relation_type
ORDER BY
    c.service_type,
    person_match,
    family_member,
    c.fm_relation_type;