select hour, avg(price) as steth_price from ethereum.core.fact_hourly_token_prices where token_address='0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

group by hour

),

eth_price as (

select hour, price as eth_price from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null

select date, sum(eth_usd) as total_eth_gain, sum(gain_usd) as total_gain_usd

from

(select date_trunc('day',a.block_timestamp) as date, b.amount_out - a.amount_in as eth_usd,

b.amount_out*eth_price - a.amount_in*steth_price as gain_usd

from flipside_prod_db.ethereum.dex_swaps a join flipside_prod_db.ethereum.dex_swaps b on a.tx_id=b.tx_id and b.direction = 'OUT'

left join steth_price c on date_trunc('hour',a.block_timestamp)=c.hour

left join eth_price d on date_trunc('hour',a.block_timestamp)=d.hour

where (a.token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' and a.direction = 'IN') and a.pool_address in ('0xdc24316b9ae028f1497c275eb9192a3ea0f67022', '0x4028daac072e492d34a3afdbef0ba7e35d8b55c4') and a.amount_usd>0 and b.amount_usd>0 and abs(b.amount_out-a.amount_in)<1)

group by date