---

SELECT
    io.individual_id AS client_id,
    io.contract_id AS contract_id,
    c.number AS contract_number,
    c.service_type AS contract_type,
    ind.insurance_number AS snils,
    SUM(t.value) AS contributions_amount
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
JOIN ourpension.individual ind
    ON ind.id = io.individual_id
WHERE io.payment_return IS NOT TRUE
  AND io.contract_id IS NOT NULL
  AND io.individual_id IS NOT NULL
  AND io.date >= DATE '2025-01-01'
  AND io.date < DATE '2026-01-01'
  AND (
        (c.service_type = 'NPO' AND at.mnemonics = 'ИПС_ФЛ')
        OR
        (c.service_type = 'PDS' AND at.mnemonics = 'ПДС СВ')
      )
  AND NULLIF(TRIM(ind.insurance_number), '') IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM ourpension.application_death_info adi
      WHERE adi.individual_id = ind.id
        AND adi.accepted IS NOT NULL
  )
GROUP BY
    io.individual_id,
    io.contract_id,
    c.number,
    c.service_type,
    ind.insurance_number
HAVING SUM(t.value) > 0
ORDER BY SUM(t.value) DESC
LIMIT 10;