---

SELECT
    io.individual_id AS client_id,
    io.contract_id,
    c.number AS contract_number,
    c.service_type AS contract_type,
    EXTRACT(YEAR FROM o.operation_date)::integer AS tax_period_year,
    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.operation_type ot
    ON ot.id = o.operation_type_id
JOIN back_office."transaction" t
    ON t.operation_id = o.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 o.operation_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '3 years'
  AND o.operation_date < DATE_TRUNC('year', CURRENT_DATE)
  AND io.payment_return IS NOT TRUE
  AND o.approved IS NOT NULL
  AND o.deleted IS NULL
  AND NULLIF(TRIM(ind.insurance_number), '') IS NOT NULL
  AND (
        (
            c.service_type = 'NPO'
            AND ot.code = 'DEPOSIT_ENROLLMENT'
            AND at.mnemonics = 'ИПС_ФЛ'
        )
        OR
        (
            c.service_type = 'PDS'
            AND ot.code = 'PDS_DEPOSIT_ENROLLMENT'
            AND at.mnemonics = 'ПДС СВ'
        )
      )
  AND NOT EXISTS (
        SELECT 1
        FROM ourpension.application_death_info di
        WHERE di.individual_id = ind.id
          AND di.accepted IS NOT NULL
          AND di.canceled IS NULL
          AND di.rejected IS NULL
      )
GROUP BY
    io.individual_id,
    io.contract_id,
    c.number,
    c.service_type,
    EXTRACT(YEAR FROM o.operation_date)
HAVING SUM(t.value) > 0
ORDER BY tax_period_year DESC
LIMIT 20;