select

date_trunc('day', block_timestamp) as day,

sum(full_decoded_log:data[2]:value) / pow(10, 18) as daily_deposit

from ronin.core.ez_decoded_event_logs

where event_name = 'Deposit'

and full_decoded_log:data[2]:name = 'assets'

and contract_address = '0xcad9e7aa2c3ef07bad0a7b69f97d059d8f36edd2'

group by 1

),

withdraw as (

select

date_trunc('day', block_timestamp) as day,

sum(full_decoded_log:data[3]:value) / pow(10, 18) as daily_withdraw

from ronin.core.ez_decoded_event_logs

where event_name = 'Withdraw'

and full_decoded_log:data[3]:name = 'assets'

and contract_address = '0xcad9e7aa2c3ef07bad0a7b69f97d059d8f36edd2'

group by 1

),

combined as (

select

coalesce(d.day, w.day) as day,

coalesce(d.daily_deposit, 0) as hourly_RON_deposited,

- coalesce(w.daily_withdraw, 0) as hourly_RON_withdrawn,

coalesce(d.daily_deposit, 0) - coalesce(w.daily_withdraw, 0) as net_hourly_stake,

sum(coalesce(d.daily_deposit, 0) - coalesce(w.daily_withdraw, 0))

over (order by coalesce(d.day, w.day)) as cumulative_net_stake

from deposit d

full outer join withdraw w

on d.day = w.day

select *