with t1 as (

select

BLOCK_TIMESTAMP,

tx_hash,

SERVICE_ID,

NAME,

case

when NAME ilike '%market_maker%' then 'Creator'

when NAME ilike '%Trader%' then 'Trader'

when NAME ilike '%Mech%' then 'Mech'

else null

end as service_type,

AGENT_IDS[0] as agent_Id

from crosschain.olas.ez_service_registrations

),

base as (

select

events.BLOCKCHAIN,

events.BLOCK_TIMESTAMP,

events.tx_hash,

events.service_id,

agent_Id,

NAME,

from crosschain.olas.fact_service_events events

left join t1 on events.service_id=t1.SERVICE_ID

select

service_type,

count(distinct tx_hash) as no_transaction,

from base