trunc(hour,'day') as date,

symbol,

token_address,

avg(price) as average_of_price,

decimals

from ethereum.core.fact_hourly_token_prices

where hour::date >= '2022-09-01'

and hour::date < '2022-10-01'

group by date,symbol,token_address,decimals),

t2 as (

select 'Add Liquidity' as cased,

trunc(block_timestamp,'day') as date,

tx_hash,

pool_name,

origin_from_address,

EVENT_INPUTS:amount0/pow(10,d.decimals)*d.average_of_price as volume,

EVENT_INPUTS:amount1/pow(10,c.decimals)*c.average_of_price as volume_

from ethereum.core.fact_event_logs a join ethereum.core.dim_dex_liquidity_pools b on a.contract_address = b.pool_address

left outer join t1 d on a.block_timestamp::date = d.date and b.token0 = d.token_address

left outer join t1 c on a.block_timestamp::date = c.date and b.token1 = c.token_address

where a.origin_to_address = lower('0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f')

and a.event_name = 'Mint' or a.event_name ilike '%addl%'),

t3 as (select 'Remove Liquidity' as cased,

trunc(block_timestamp,'day') as date,

tx_hash,

pool_name,

origin_from_address,

EVENT_INPUTS:amount0/pow(10,d.decimals)*d.average_of_price as volume,

EVENT_INPUTS:amount1/pow(10,c.decimals)*c.average_of_price as volume_

from ethereum.core.fact_event_logs a join ethereum.core.dim_dex_liquidity_pools b on a.contract_address = b.pool_address

left outer join t1 c on a.block_timestamp::date = c.date and b.token1 = c.token_address

left outer join t1 d on a.block_timestamp::date = d.date and b.token0 = d.token_address

where a.origin_to_address = lower('0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f')