select date_trunc('day', block_timestamp) as metric_date,

block_number,

tx_hash,

status,

delegator,

to_delegate,

from_delegate,

delegation_type

from optimism.core.fact_delegations

),

delegations as (

select

block_timestamp,

tx_hash,

event_inputs:delegate::string as delegate,

event_inputs:newBalance/pow(10,18) as current_bal,

event_inputs:newBalance/pow(10,18) - event_inputs:previousBalance/pow(10,18) as net_change

from optimism.core.fact_event_logs

where contract_address = '0x4200000000000000000000000000000000000042'

and event_name = 'DelegateVotesChanged'

order by block_timestamp desc

),

delegate_vp as (

select

1 as index,

date(block_timestamp) as date,

delegate as delegate_name,

sum(case when net_change>= 0 then 1 else null end) as delegations,

sum(case when net_change<0 then -1 else null end) as undelegations,

sum(case when net_change>= 0 then net_change else null end) as dele_vol,

sum(case when net_change<0 then net_change else null end) as undele_vol,

median(current_bal) as bal

from delegations