SELECT

CASE

WHEN FUNCTION ILIKE '%_liq%' OR FUNCTION ILIKE '%pool%' OR FUNCTION ILIKE '%deposit%' OR FUNCTION ILIKE '%withdraw%' OR FUNCTION ILIKE '%tvl%' THEN 'Liquidity Management'

WHEN FUNCTION ILIKE '%attestor%' THEN 'Attestation'

WHEN FUNCTION ILIKE '%registry%' OR FUNCTION ILIKE '%regist%' THEN 'Registry Management'

WHEN FUNCTION ILIKE '%token%' OR FUNCTION ILIKE '%balance%' THEN 'Token Management'

WHEN FUNCTION ILIKE '%service%' THEN 'Service Functions'

WHEN FUNCTION ILIKE '%allow%' OR FUNCTION ILIKE '%approve%' THEN 'Approval and Allowance Management'

WHEN FUNCTION ILIKE '%bond%' OR FUNCTION ILIKE '%validator%' OR FUNCTION ILIKE '%delegator%' OR FUNCTION ILIKE '%stake%' THEN 'Staking and Bonding'

WHEN FUNCTION ILIKE '%burn%' OR FUNCTION ILIKE '%mint%' OR FUNCTION ILIKE '%wrap%' THEN 'Minting and Burning'

WHEN FUNCTION ILIKE '%entries%' OR FUNCTION ILIKE '%winner%' OR FUNCTION ILIKE '%coupon%' OR FUNCTION ILIKE '%reward%' OR FUNCTION = 'spin' THEN 'Gaming Functions'

WHEN FUNCTION ILIKE '%check%' OR FUNCTION ILIKE '%claim%' OR FUNCTION ILIKE '%eligible%' THEN 'Verification and Claims'

WHEN FUNCTION ILIKE '%pull%' THEN 'Pull Functions'

WHEN FUNCTION ILIKE '%pair%' OR FUNCTION ILIKE '%swap%' THEN 'DeFi and Swapping Functions'

WHEN FUNCTION ILIKE '%transfer%' OR FUNCTION ILIKE '%spend%' THEN 'Transfer and Spending Functions'

WHEN FUNCTION ILIKE '%split%' THEN 'Splitting Functions'

ELSE 'Other Functions'

END AS function_category,

COUNT(DISTINCT TX_ID) AS total_transactions,

COUNT(CASE WHEN SUCCEEDED = TRUE THEN 1 END) AS total_successful_transactions,

COUNT(CASE WHEN SUCCEEDED = TRUE THEN 1 END) * 100.0 / COUNT(*) AS global_success_rate_percentage,

COUNT(DISTINCT PROGRAM_ID) AS total_unique_programs,

COUNT(DISTINCT TX_ID) * 100.0 / SUM(COUNT(DISTINCT TX_ID)) OVER () AS function_usage_percentage

FROM aleo.core.fact_transitions

GROUP BY function_category

gfm.function_category,

gfm.total_transactions,

gfm.total_successful_transactions,

gfm.global_success_rate_percentage,

gfm.total_unique_programs,

gfm.function_usage_percentage

FROM global_function_metrics gfm