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.REWARD), 0) AS total_rewards,

-- Extract the actual start date of the week

TO_CHAR(DATE_TRUNC('WEEK', r.BLOCK_TIMESTAMP), 'YYYY-MM-DD') AS week_start_date

FROM

crosschain.olas.ez_service_registrations r

LEFT JOIN

crosschain.olas.ez_service_checkpoints c

ON

r.SERVICE_ID = c.SERVICE_ID

WHERE

r.BLOCKCHAIN = 'gnosis' -- Filter for Gnosis blockchain

AND r.BLOCK_TIMESTAMP >= DATEADD(YEAR, -1, CURRENT_DATE) -- Filter for the past year

GROUP BY

r.BLOCKCHAIN, service_category, week_start_date

ORDER BY

week_start_date;