SELECT

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS day,

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 daily_transactions,

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

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

COUNT(DISTINCT PROGRAM_ID) AS daily_unique_programs

FROM aleo.core.fact_transitions

GROUP BY day, function_category

),

global_function_metrics AS (

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'