staked AS (

SELECT t.symbol, lower(c.contract_metadata:asset) as token_address, c2.symbol as loaned_asset,

SUM (CASE WHEN origin_function_signature = '0xe2bbb158' THEN amount ELSE -amount END) AS kashi_tokens_staked--, COUNT (DISTINCT from_address) wallets_staked

FROM ethereum.core.ez_token_transfers t JOIN ethereum.core.dim_contracts c ON t.symbol = c.symbol JOIN ethereum.core.dim_contracts c2 ON lower(c.contract_metadata:asset) = c2.address

WHERE t.symbol like '%km%'

and t.symbol like '%/%-%'

AND t.origin_function_signature IN ('0xe2bbb158', '0x441a3e70')

GROUP BY 1, 2, 3

),

prices AS (SELECT token_address, AVG (price) token_price

FROM ethereum.core.fact_hourly_token_prices p

WHERE hour::date = CURRENT_DATE - 1

GROUP BY 1

)

SELECT RANK () OVER (ORDER BY kashi_tokens_staked*token_price DESC) AS place,

symbol AS pool, loaned_asset,

-- kashi_tokens_staked,

--kashi_tokens_unstaked,

ROUND (kashi_tokens_staked*token_price, 0) AS pool_TVL

FROM staked LEFT JOIN prices p ON staked.token_address = p.token_address

ORDER BY 1

LIMIT 20