WITH kashi_contracts as (
SELECT
DISTINCT contract_address,
RIGHT(symbol, LEN(symbol) - 2) as pool_name
FROM flipside_prod_db.ethereum.udm_events
WHERE SYMBOL LIKE 'km%'
),
events as (SELECT *
FROM flipside_prod_db.ethereum_core.fact_event_logs fl
INNER JOIN kashi_contracts as kc
ON kc.contract_address=fl.contract_address
)
SELECT
SUM(ZEROIFNULL(AMOUNT_USD)) as total_usd,
COUNT(*) as number_transactions,
pool_name
FROM flipside_prod_db.ethereum_core.ez_token_transfers tt
INNER JOIN events
ON events.TX_HASH=tt.TX_HASH
GROUP BY pool_name
ORDER BY total_usd DESC
LIMIT 5