select

case

when NAME ilike '%valory%' then 'valory'

when NAME ilike '%eightballer%' then 'eightballer'

when NAME ilike '%elcollectooorr%' then 'elcollectooorr'

when NAME ilike '%dvilela%' then 'dvilela'

when NAME ilike '%zarathustra%' then 'zarathustra'

when NAME ilike '%balancer%' then 'balancer'

when NAME ilike '%jhehemann%' then 'jhehemann'

when NAME ilike '%algovera%' then 'algovera'

when NAME ilike '%postmint%' then 'postmint'

when NAME ilike '%fetchai%' then 'fetchai'

else 'others'

end as authorship,

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 , 2 having component_type is not null