SELECT

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS day,

case when PROGRAM_ID ilike '%aleo_name_service%' or PROGRAM_ID ilike '%ans_%' then 'Aleo Name Service'

when PROGRAM_ID ilike '%alphaswap%' then 'Alphaswap'

when PROGRAM_ID ilike '%arcn_%' then 'ARCN'

when PROGRAM_ID ilike '%betastaker%' or PROGRAM_ID ilike '%betastaking%' or PROGRAM_ID ilike '%delegator%' or PROGRAM_ID ILIKE '%reference_delegator%' then 'Beta Staking'

when PROGRAM_ID ilike '%grant_disbursement%' then 'Grant Disbursement'

when PROGRAM_ID ilike '%lsp_host%' then 'LSP host'

when PROGRAM_ID ilike '%pondo_%' then 'Pondo Protocol'

when PROGRAM_ID ilike '%puzzle_%' then 'Puzzle'

when PROGRAM_ID ilike '%vlink_%' then 'vLINK'

else program_id end as program,

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 FUNCTION) AS daily_unique_functions

FROM aleo.core.fact_transitions where PROGRAM_ID <> 'vs__2_candidates.aleo' and PROGRAM_ID not ilike '%zvote_dao%'

GROUP BY day, PROGRAM

),

global_program_metrics AS (

SELECT

case when PROGRAM_ID ilike '%aleo_name_service%' or PROGRAM_ID ilike '%ans_%' then 'Aleo Name Service'

when PROGRAM_ID ilike '%alphaswap%' then 'Alphaswap'

when PROGRAM_ID ilike '%arcn_%' then 'ARCN'

when PROGRAM_ID ilike '%betastaker%' or PROGRAM_ID ilike '%betastaking%' or PROGRAM_ID ilike '%delegator%' or PROGRAM_ID ILIKE '%reference_delegator%' then 'Beta Staking'

when PROGRAM_ID ilike '%grant_disbursement%' then 'Grant Disbursement'

when PROGRAM_ID ilike '%lsp_host%' then 'LSP host'

when PROGRAM_ID ilike '%pondo_%' then 'Pondo Protocol'

when PROGRAM_ID ilike '%puzzle_%' then 'Puzzle'

when PROGRAM_ID ilike '%vlink_%' then 'vLINK'

else program_id end as program,

COUNT(DISTINCT TX_ID) AS total_transactions,

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