---

, payments as (
    select
        o.id,
        o.ef_year,
        sum(o.value) as sum_value
    from operations o
    join back_office.operation_type ot
        on ot.id = o.operation_type_id
    where o.deb_mnemonics like '%СВ%'
      and coalesce(o.cred_mnemonics, '') not like '%НДФЛ%'
      and ot.code in (
          'PAYMENT_TERMINATED',
          'PDS_REDEMPTION_PAYMENT',
          'PDS_REDEMPTION_PAYMENT_TO_NPF',
          'PDS_REDEMPTION_LIFELONG_PAYMENT',
          'PDS_REDEMPTION_REDEMPTION_LOSS',
          'PDS_ADDITIONAL_PAYMENT_REDEMPTION',
          'PDS_REP_PAYMENT_TERMINATED',
          'SECOND_PAYMENT_TERMINATED',
          'PDS_RETURN_NDFL_REDEMPTION'
      )
    group by o.id, o.ef_year
),
payment_totals as (
    select
        ef_year,
        count(*) as count_oper,
        sum(sum_value) as sum_value
    from payments
    group by ef_year
),
tax_totals as (
    select
        o.ef_year,
        sum(o.value) as sum_ndfl
    from operations o
    join back_office.operation_type ot
        on ot.id = o.operation_type_id
    where
        -- НДФЛ отдельной операцией, как в текущем примере
        ot.code = 'PDS_NDFL_REDEMPTION_PAYMENT'

        or (
            -- Сохраняем учёт НДФЛ внутри операции выплаты
            o.cred_mnemonics like '%НДФЛ%'
            and exists (
                select 1
                from payments p
                where p.id = o.id
            )
        )
    group by o.ef_year
)
select
    cast(coalesce(p.ef_year, n.ef_year) as varchar(4)) as year_name,
    coalesce(p.count_oper, 0) as count_oper,
    p.sum_value,
    n.sum_ndfl
from payment_totals p
full join tax_totals n
    on n.ef_year = p.ef_year
order by coalesce(p.ef_year, n.ef_year);