from flipside_prod_db.ethereum.udm_events

where to_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

select DATE_TRUNC('day', b.block_timestamp) AS time,count(distinct a.wallet) as wallets,

count( b.TX_HASH) as swaps, (sum(b.AMOUNT_OUT)-sum(b.AMOUNT_IN)) as Gain

from wallets a inner join ethereum.core.ez_dex_swaps b on a.wallet=b.ORIGIN_FROM_ADDRESS

and token_in = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

and TOKEN_out = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

group by 1),

price as (with ETH as (select DATE_TRUNC('day', hour) AS time , sum(price)/count(hour) as ETH_USD

from ethereum.core.fact_hourly_token_prices

where symbol='WETH'

group by 1),

stETH as (select DATE_TRUNC('day', hour) AS time , sum(price)/count(hour) as stETH_USD

from ethereum.core.fact_hourly_token_prices

where symbol='stETH'

group by 1)

select a.time,(stETH_USD/ETH_USD) as stETH_to_ETH

from ETH a join stETH b on a.time=b.time)

select a.TIME, WALLETS, SWAPS, GAIN,stETH_to_ETH

from swaps a join price b on a.time=b.time),

ES as (with swaps as (with wallets as (select DISTINCT from_address as wallet

from flipside_prod_db.ethereum.udm_events

where to_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

select DATE_TRUNC('day', b.block_timestamp) AS time,count(distinct a.wallet) as wallets,

count( b.TX_HASH) as swaps, (sum(b.AMOUNT_OUT)-sum(b.AMOUNT_IN)) as Gain

from wallets a inner join ethereum.core.ez_dex_swaps b on a.wallet=b.ORIGIN_FROM_ADDRESS

and token_out = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

and TOKEN_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

group by 1),

price as (with ETH as (select DATE_TRUNC('day', hour) AS time , sum(price)/count(hour) as ETH_USD

from ethereum.core.fact_hourly_token_prices

where symbol='WETH'

group by 1),