select

case

when NAME ilike 'skill%' then 'skill'

when NAME ilike 'contract%' then 'contract'

when NAME ilike 'protocol%' then 'protocol'

when NAME ilike 'connection%' then 'connection'

when NAME ilike 'custom%' then 'custom'

else null

end as component_type,

count(distinct TX_HASH) as no_components

from crosschain.olas.ez_unit_registrations

where UNIT_TYPE = 'component'

group by 1 having component_type is not null