with lp as (select block_timestamp, tx_hash, action, LIQUIDITY_PROVIDER,

amount0_adjusted, amount1_adjusted,

amount0_usd, amount1_usd, (amount0_usd+amount1_usd) as lp_vol, case

when action = 'DECREASE_LIQUIDITY' then (lp_vol*-1)

when action = 'INCREASE_LIQUIDITY' then lp_vol

else null end as vol

from ethereum.uniswapv3.ez_lp_actions

where pool_address = '0x99ac8ca7087fa4a2a1fb6357269965a2014abc35'

and date(block_timestamp) >= current_date - 33

and liquidity > 0

order by block_timestamp desc),

tvl as (select pool_name, date(block_timestamp) as dt, token0_symbol, token1_symbol,

TOKEN0_BALANCE_ADJUSTED,

TOKEN1_BALANCE_ADJUSTED,

TOKEN0_BALANCE_USD as BTC_usd,

TOKEN1_BALANCE_USD as USDC_usd,

ROW_NUMBER() OVER (PARTITION BY dt ORDER BY block_timestamp desc) as rank

from ethereum.uniswapv3.ez_pool_stats

where pool_address = '0x99ac8ca7087fa4a2a1fb6357269965a2014abc35' AND

date(block_timestamp) >= current_date - 31

order by block_timestamp desc),

tvl2 as (select *, (btc_usd+usdc_usd) as total_bal,

round((btc_usd/total_bal)*100,1) as wbtc_perc,

round((usdc_usd/total_bal)*100,1) as usdc_perc,

concat(wbtc_perc,'% - ',usdc_perc, '%') as pool_ratio

from tvl

where rank = 1),

lp2 as (select date_trunc('day', block_timestamp) as dt, action,

count (distinct tx_hash) as tx_count,

count (distinct LIQUIDITY_PROVIDER) as address_count,