---

select
    c.service_type,
    case
        when c.service_type = 'NPO' and c.individual_id is not null then 'ИПП'
        when c.service_type = 'NPO' and c.entity_id is not null then 'КПП'
        when c.service_type = 'PDS' then 'ДДС'
        else 'Другое'
    end as contract_type,
    count(*) as total,
    count(c.sale_channel_id) as sale_channel_filled,
    count(*) filter (where c.sale_channel_id is null) as sale_channel_empty,
    count(c.sale_channel_id_1c) as sale_channel_1c_filled
from ourpension.contract c
where c.service_type in ('NPO', 'PDS')
group by
    c.service_type,
    case
        when c.service_type = 'NPO' and c.individual_id is not null then 'ИПП'
        when c.service_type = 'NPO' and c.entity_id is not null then 'КПП'
        when c.service_type = 'PDS' then 'ДДС'
        else 'Другое'
    end
order by c.service_type, contract_type;