SELECT

DISTINCT r.SERVICE_ID,

DATE_TRUNC('month', r.BLOCK_TIMESTAMP) AS creation_month,

CASE

-- General Trading

WHEN LOWER(r.DESCRIPTION) LIKE '%trading%' THEN 'Trading' -- Trader-Specific Services

WHEN LOWER(r.NAME) LIKE '%trader%' THEN 'Traders' -- Arbitrage

WHEN LOWER(r.DESCRIPTION) LIKE '%arbitrage%' THEN 'Arbitrage' -- Liquidity Management

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

OR LOWER(r.DESCRIPTION) LIKE '%liquidation%' THEN 'Liquidity Management' -- Prediction Markets & Options

WHEN LOWER(r.DESCRIPTION) LIKE '%prediction market%'

OR LOWER(r.DESCRIPTION) LIKE '%options trading%' THEN 'Prediction Markets & Options' -- DAO & Multi-Agent Systems

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

OR LOWER(r.DESCRIPTION) LIKE '%multi-agent%'

OR LOWER(r.DESCRIPTION) LIKE '%governance%' THEN 'DAO & Multi-Agent Systems' -- AI & Machine Learning

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

OR LOWER(r.DESCRIPTION) LIKE '%rag-based%'

OR LOWER(r.DESCRIPTION) LIKE '%machine learning%'

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

OR LOWER(r.DESCRIPTION) LIKE '%short films%' THEN 'AI & Machine Learning' -- Infrastructure & Tooling

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

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

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

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

OR LOWER(r.NAME) LIKE '%hello_world%' THEN 'Infrastructure & Tooling' -- NFT & Social Media

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

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

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

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

OR LOWER(r.NAME) LIKE '%memeooorr%' THEN 'NFT & Social Media' -- Prediction & Oracles

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

OR LOWER(r.DESCRIPTION) LIKE '%price oracle%' THEN 'Prediction & Oracles' -- Testing & Development

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

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

OR r.NAME IN ('asd', 'pred', 'Koi') THEN 'Testing & Development' -- Miscellaneous