---

select
    o.id as operation_id,
    o.ef_year,
    o.operation_type_id,
    ot.code,
    ot.name,
    o.deb_mnemonics,
    o.cred_mnemonics,
    count(*) as row_count,
    sum(o.value) as sum_value,
    case
        when o.deb_mnemonics like '%СВ%'
        then 'Да'
        else 'Нет'
    end as passes_debit_filter,
    case
        when o.cred_mnemonics like '%НДФЛ%'
        then 'Да'
        else 'Нет'
    end as is_ndfl_credit
from operations o
join back_office.operation_type ot
    on ot.id = o.operation_type_id
where ot.name ilike '%выкуп%'
   or ot.code ilike '%REDEMPTION%'
group by
    o.id,
    o.ef_year,
    o.operation_type_id,
    ot.code,
    ot.name,
    o.deb_mnemonics,
    o.cred_mnemonics
order by
    o.ef_year,
    o.id,
    o.deb_mnemonics,
    o.cred_mnemonics;