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) * 100.0 / COUNT(*) AS success_rate_percentage,

COUNT(DISTINCT FUNCTION) AS unique_functions,

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

FROM aleo.core.fact_transitions

WHERE PROGRAM_ID <> 'vs__2_candidates.aleo'

AND PROGRAM_ID NOT ILIKE '%zvote_dao%'

GROUP BY program

program,

total_transactions,

success_rate_percentage,

unique_functions,

program_usage_percentage

FROM program_metrics

ORDER BY 2 desc, 1 asc