SELECT

BLOCKCHAIN,

CASE

WHEN LOWER(DESCRIPTION) LIKE '%trading%'

OR LOWER(NAME) LIKE '%trader%'

OR LOWER(DESCRIPTION) LIKE '%arbitrage%'

THEN 'Trading & Arbitrage'

ELSE 'Other'

END AS service_category,

COUNT(DISTINCT TX_HASH) AS distinct_transaction_count

FROM

crosschain.olas.ez_service_registrations

GROUP BY

BLOCKCHAIN, service_category

ORDER BY

BLOCKCHAIN;