---

select
    ot.code as operation_code,
    ot.short_name as operation_name,
    cat.mnemonics as credit_type,
    count(*) as transaction_count,
    sum(t.value) as total_value
from back_office.account da
join back_office.transaction t
    on t.debit_account_id = da.id
join back_office.operation o
    on o.id = t.operation_id
join back_office.operation_type ot
    on ot.id = o.operation_type_id
left join back_office.account ca
    on ca.id = t.credit_account_id
left join back_office.account_type cat
    on cat.id = ca.account_type_id
left join back_office.account_type_group catg
    on catg.id = cat.account_type_group_id
where da.owner_sharer_id = 14280820
    and o.approved is not null
    and o.deleted is null
    and t.effective_date < cast('2026-10-20' as date)
    and (
        cat.mnemonics is null
        or cat.mnemonics not in (
            'ИПС', 'СПС', 'СПС И', 'ИПС ИД',
            'СПС ИД', 'СПС И ИД', 'Р С'
        )
    )
    and (
        catg.mnemonics is null
        or catg.mnemonics <> 'В'
    )
    and not (
        ot.code = 'REDISTRIBUTION'
        and t.credit_account_id is null
    )
    and not (
        ot.code = 'TRANSFER_PENSION_TO_RESERVE'
        and coalesce(
            da.owner_sharer_id = ca.owner_sharer_id,
            false
        )
    )
group by ot.code, ot.short_name, cat.mnemonics
order by ot.code, cat.mnemonics;