from ethereum.core.dim_dex_liquidity_pools

where platform ilike '%sushiswap%')

,

tb1 as ( select date(block_timestamp) as date,tx_hash ,event_name,origin_from_address, pool_name , token0, EVENT_INPUTS:amount0 as amounts0, token1,

EVENT_INPUTS:amount1 as amounts1

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

where (event_name in ('Burn','Mint') or event_name ilike '%addl%' or event_name ilike '%Remov%')

and origin_to_address = lower('0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f')

and block_timestamp::date >= CURRENT_DATE - 31 and block_timestamp::date < CURRENT_DATE - 3)

,

price as ( select date(hour) as p_date, symbol, token_address, decimals, avg(price) as prices

from ethereum.core.fact_hourly_token_prices

where hour::date >= CURRENT_DATE - 31 and hour::date < CURRENT_DATE - 3

group by 1,2,3,4)

,

final as ( select date, tx_hash, event_name, origin_from_address, pool_name , b.symbol as token_name0 , amounts0/pow(10,b.decimals)*b.prices as amount0_usd,

c.symbol as token_name_1, amounts1/pow(10,c.decimals)*c.prices as amount1_usd

from tb1 a left outer join price b on a.date = b.p_date and token0 = b.token_address

left outer join price c on a.date = c.p_date and token1 = c.token_address

select date,concat(token_name0,'->',token_name_1) as pairs,case when date < '2022-09-15' then 'Pre-Merge'

when date >= '2022-09-15' then 'Post-Merge' end as type, count(DISTINCT(tx_hash)) as total_tx, count(DISTINCT(origin_from_address)) as total_user,

sum(amount0_usd) as total_add , sum(amount1_usd) as total_add1 , sum(amount0_usd+amount1_usd)*-1 as volume

from final

where amount0_usd is not null and amount1_usd is not null

and (event_name = 'Burn' or event_name ilike '%remov%')

group by 1,2,3

having volume < -5000