top_pools as (

select * from (

select

p.pool_name as pool_name,

p.platform,

p.pool_address,

(IFF(amount_usd > pow(10,9), amount_usd/pow(10,18), amount_usd)) as balance,

rank() over (partition by p.platform order by balance desc) as rank

from ethereum.erc20_balances u

join ethereum.dex_liquidity_pools p

on u.user_address = p.pool_address

where balance_date = CURRENT_DATE - 1 and amount_usd > 0

and p.platform in ('sushiswap','uniswap-v2','uniswap-v3')

) where rank <= 5

),

in_txns as (

select

s.block_timestamp::date as date,

s.platform,

s.pool_address,

s.pool_name,

s.tx_id,

s.event_index,

s.amount_usd as amount_in_usd

from ethereum.dex_swaps s

join top_pools t on t.pool_address = s.pool_address and s.platform = t.platform