stakers as (

select

trunc(block_timestamp,'week') as date,

tx_hash as staking_action,

decoded_log:user as staker,

decoded_log:amount/pow(10,17) as amount_staked

from arbitrum.core.ez_decoded_event_logs

where contract_address='0x5ca0f33f1ebd140def87721291ff313a9141f79e'

and event_name in ('StakeStarted','Restaked')

),

unstakers as (

select

trunc(block_timestamp,'week') as date,

tx_hash as unstaking_action,

decoded_log:user as unstaker,

decoded_log:amount/pow(10,17) as amount_unstaked

from arbitrum.core.ez_decoded_event_logs

where contract_address='0x5ca0f33f1ebd140def87721291ff313a9141f79e'

and event_name in ('UnstakeStarted','UnstakeFinished')

),

final as (

ifnull(x.date,y.date) as week,

ifnull(staker,unstaker) as user,

ifnull(count(distinct staking_action),0) as staking_actions,

ifnull(count(distinct unstaking_action),0) as unstaking_actions,

ifnull(sum(amount_staked),0) as staked_amount,

ifnull(sum(amount_unstaked),0) as unstaked_amount,

sum(staked_amount) over (partition by user order by week) as total_staked_amount,

sum(unstaked_amount) over (partition by user order by week) as total_unstaked_amount,

unstaked_amount-staked_amount as net_staked,

sum(net_staked) over (partition by user order by week) as total_staked

from stakers x join unstakers y on x.date=y.date and x.staker=y.unstaker