select pool_name,pool_address ,date_trunc('day',creation_time) as date

from ethereum.dex_liquidity_pools

where platform = 'sushiswap' and creation_time::date > current_date-30

GROUP by pool_name,pool_address,creation_time

),

TVL as ( select

pool_name,

sum(amount_usd) as liquidity

from ethereum.erc20_balances

inner join sushiswap_pools

on (lower(pool_address) = lower(user_address))

group by pool_name HAVING liquidity is NOt null

order by liquidity

),

sushiswap_volume as (

select

pool_name,

sum(amount_usd) as volume

from ethereum.udm_events

inner join sushiswap_pools

on (lower(pool_address) = lower(to_address) or lower(pool_address) = lower(from_address) )

group by 1 HAVING volume is NOt null

order by volume

select sushiswap_volume.pool_name , liquidity , volume

from TVL INNER join sushiswap_volume

on (sushiswap_volume.pool_name =TVL.pool_name )

GROUP by 1,2,3