sum(value) as deposit_amount,

count(DISTINCT tx_id) as deposit_tx,

avg(value) as avg_deposit,

sum(deposit_amount) over (order by weekly asc) as cum_deposit

from bitcoin.core.fact_outputs

where PUBKEY_SCRIPT_ADDRESS = 'bc1qw8wrek2m7nlqldll66ajnwr9mh64syvkt67zlu'

group by 1)

,

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

sum(value)*-1 as withdraw_amount,

avg(value) as avg_withdraw,

count(DISTINCT tx_id) as withdraw_tx,

sum(withdraw_amount) over (order by weekly asc) as cum_withdraw

from bitcoin.core.fact_inputs

where PUBKEY_SCRIPT_ADDRESS = 'bc1qw8wrek2m7nlqldll66ajnwr9mh64syvkt67zlu'

group by 1)

,

tb3 as ( select a.weekly,

deposit_tx,

withdraw_tx,

deposit_amount,

withdraw_amount,

avg_deposit,

avg_withdraw,

deposit_amount+withdraw_amount as weekly_net,

cum_deposit+cum_withdraw as balance

from tb2 a left outer join tb1 b on a.weekly = b.weekly

,

tb4 as ( select trunc(hour,'day') as day,

avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WBTC'

and hour::date = current_date - 1

group by 1)