---

SELECT
    CAST(EXTRACT(YEAR FROM io.date) AS integer) AS contribution_year,
    c.service_type AS contract_type,
    at.mnemonics,
    COUNT(DISTINCT io.id) AS incoming_order_count,
    COUNT(DISTINCT io.contract_id) AS contract_count,
    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."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 io.contract_id IS NOT NULL
  AND at.mnemonics IN ('ИПС_ФЛ', 'ПДС СВ')
GROUP BY
    CAST(EXTRACT(YEAR FROM io.date) AS integer),
    c.service_type,
    at.mnemonics
ORDER BY
    contribution_year DESC,
    contract_type,
    at.mnemonics;