DISTINCT contract_address,

RIGHT(symbol, LEN(symbol) - 2) as pool_name

FROM flipside_prod_db.ethereum.udm_events

WHERE

SYMBOL LIKE 'kmxSUSHI/DAI-LINK'

OR SYMBOL LIKE 'kmWETH/DAI-LINK'

OR SYMBOL LIKE 'kmLINK/DAI-LINK'

OR SYMBOL LIKE 'kmWBTC/DAI-LINK'

OR SYMBOL LIKE 'kmAAVE/DAI-LINK'

),

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

WHERE event_name = 'LogAddCollateral' -- add collateral to borrow

SUM(ZEROIFNULL(AMOUNT_USD)) as total_usd,

COUNT(*) as number_transactions,

total_usd/number_transactions as avg_collaterall_per_user,

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