avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WBTC'

group by 1)

,

tb1 as ( select trunc(block_timestamp,'day') as day,

value*avg_price as value_usd

from bitcoin.core.fact_outputs a join tb0 b on a.block_timestamp::date = b.day

where PUBKEY_SCRIPT_ADDRESS = 'bc1qw8wrek2m7nlqldll66ajnwr9mh64syvkt67zlu'

,

tb2 as ( select trunc(block_timestamp,'day') as day,

value*avg_price as value_usd

from bitcoin.core.fact_inputs a join tb0 b on a.block_timestamp::date = b.day

where PUBKEY_SCRIPT_ADDRESS = 'bc1qw8wrek2m7nlqldll66ajnwr9mh64syvkt67zlu'

,

tb3 as ( select trunc(day,'week') as weekly,

sum(value_usd) as deposit_usd,

sum(deposit_usd) over (order by weekly asc) as cum_deposit_usd

from tb1

group by 1)

,

tb4 as ( select trunc(day,'week') as weekly,

sum(value_usd)*-1 as withdraw_usd,

sum(withdraw_usd) over (order by weekly asc) as cum_withdraw_usd

from tb2

group by 1)

select a.weekly,

withdraw_usd,

deposit_usd,

deposit_usd+withdraw_usd as weekly_net_usd

from tb3 a left outer join tb4 b on a.weekly = b.weekly