select hour::date as datee,

avg(price) as usdprice

from ink.price.ez_prices_hourly

where symbol = 'WETH'

group by 1

),

deposit as (

SELECT count(DISTINCT origin_from_address) as depositors,

sum(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) / pow(10,18)) AS deposited,

sum((ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) / pow(10,18))*usdprice) as deposited_usd

from ink.core.fact_event_logs

join price on block_timestamp::date=datee

where topic_0 = '0x90890809c654f11d6e72a28fa60149770a0d11ec6c92319d6ceb2bb0a4ea1a15'

and origin_to_address = '0xcab283e4bb527aa9b157bae7180fef19e2aaa71a'

),

withdraw as (select

sum(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) / pow(10,18)) AS withdrawn,

sum((ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) / pow(10,18))*usdprice) AS withdrawn_usd

from ink.core.fact_event_logs

join price on block_timestamp::date=datee

where topic_0 = '0x6c3a45613039e0a1117bd6ce110ab3c920271709c010983d921a2cd268e2ea47'

and origin_to_address = '0xcab283e4bb527aa9b157bae7180fef19e2aaa71a')

select depositors,

deposited_usd,

withdrawn_usd,

deposited_usd-withdrawn_usd as net

from deposit,withdraw