SELECT

CASE

WHEN PROGRAM_ID LIKE '%puzzle%' THEN 'Puzzle Wallet'

WHEN PROGRAM_ID LIKE '%arcn%' THEN 'Arcane Finance'

WHEN PROGRAM_ID LIKE '%vlink%' THEN 'Verulink'

WHEN PROGRAM_ID LIKE '%pondo%' THEN 'Pondo'

WHEN PROGRAM_ID = 'token_registry.aleo' THEN 'Aleo Token Registry'

WHEN PROGRAM_ID = 'credits.aleo' THEN 'Aleo Credits'

WHEN PROGRAM_ID = 'wrapped_credits.aleo' THEN 'Aleo Wrapped Credits'

WHEN PROGRAM_ID LIKE '%betastaking%' OR PROGRAM_ID LIKE 'betastaker_%' THEN 'Beta Staking'

WHEN PROGRAM_ID = 'lsp_host_02.aleo' THEN 'LSP'

WHEN PROGRAM_ID LIKE '%ans%' OR PROGRAM_ID = 'aleo_name_service_registry.aleo' THEN 'Aleo Name Service'

WHEN PROGRAM_ID LIKE '%alphaswap%' THEN 'AlphaSwap'

WHEN PROGRAM_ID = 'validator_oracle.aleo' THEN 'Validator Oracle'

WHEN PROGRAM_ID LIKE 'delegator%' THEN 'Delegator Contract'

WHEN PROGRAM_ID = 'official_oracle.aleo' THEN 'Official Oracle'

WHEN PROGRAM_ID LIKE '%zvote%' THEN 'zVote'

ELSE 'Other'

END AS app_name,

PROGRAM_ID,

COUNT(*) as contract_tx_count

FROM aleo.core.fact_transitions

GROUP BY PROGRAM_ID

),

app_totals AS (

SELECT

app_name,

SUM(contract_tx_count) as total_tx_count,

LISTAGG(PROGRAM_ID, ', ') WITHIN GROUP (ORDER BY contract_tx_count DESC) as included_contracts,

COUNT(DISTINCT PROGRAM_ID) as contract_count,

ROW_NUMBER() OVER (ORDER BY SUM(contract_tx_count) DESC) as rank

FROM app_transactions

GROUP BY app_name

SELECT