SELECT

token_address,

AVG(price) AS price,

date_trunc('day', hour) AS p_day

FROM base.price.ez_hourly_token_prices

GROUP BY token_address, p_day

),

provide_liquidity as (

SELECT tx_hash,

origin_from_address,

date_trunc('day', block_timestamp) as day,

decoded_log:"amount0"/pow(10,p.decimals:"token0") as amount0,

decoded_log:"amount1"/pow(10,p.decimals:"token1") as amount1,

p.tokens,

amount0*pr0.price as amount0_usd,

amount1*pr1.price as amount1_usd,

amount0_usd + amount1_usd as liquidity_provided_usd

FROM base.core.ez_decoded_event_logs l

JOIN base.defi.dim_dex_liquidity_pools p ON l.contract_address = p.pool_address

JOIN prices pr0 ON day = pr0.p_day AND p.tokens:"token0" = pr0.token_address

JOIN prices pr1 ON day = pr1.p_day AND p.tokens:"token1" = pr1.token_address

WHERE event_name = 'Mint'

AND contract_name = 'BaseSwap LPs'

,

remove_liquidity as (

SELECT tx_hash,

origin_from_address,

date_trunc('day', block_timestamp) as day,

decoded_log:"amount0"/pow(10,p.decimals:"token0") as amount0,

decoded_log:"amount1"/pow(10,p.decimals:"token1") as amount1,

p.tokens,