select

pool_name,

pool_address,

avg(token0_balance_usd + token1_balance_usd) as tvl

from uniswapv3.pool_stats

where token0_balance_usd is not null and token1_balance_usd is not null

and pool_name is not null

group by 1,2

order by tvl desc

limit 100

),

swap_txns as (

select

pool_name,

pool_address,

tx_id,

sender

from uniswapv3.swaps

where pool_name in (select pool_name from lp_ltv)

),

swaps as (

select

pool_name,

count(distinct(tx_id)) as n_swaps,

count(distinct(sender)) as n_wallets

from uniswapv3.swaps

group by pool_name

),

fees as (

select

s.pool_name,

sum(t.fee_usd) as total_fees

from ethereum.transactions t

join swap_txns s on s.tx_id = t.tx_id