SELECT

r.BLOCKCHAIN,

CASE

WHEN LOWER(r.DESCRIPTION) LIKE '%trading%'

OR LOWER(r.NAME) LIKE '%trader%'

OR LOWER(r.DESCRIPTION) LIKE '%arbitrage%'

THEN 'Trading & Arbitrage'

ELSE 'Other'

END AS service_category,

COUNT(DISTINCT r.TX_HASH) AS distinct_transaction_count,

COALESCE(SUM(c.TOTAL_AVAILABLE_REWARDS), 0) AS total_rewards

FROM

crosschain.olas.ez_service_registrations r

LEFT JOIN

crosschain.olas.ez_service_checkpoints c

ON

r.SERVICE_ID = c.SERVICE_ID

GROUP BY

r.BLOCKCHAIN, service_category

ORDER BY

r.BLOCKCHAIN;