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' -- OP governance token

and event_name = 'DelegateVotesChanged'

and event_inputs:delegate::string = '0x62a43123fe71f9764f26554b3f5017627996816a' -- Flipside Governance Wallet - flipsidecrypto.eth

order by block_timestamp desc

)

select

date(block_timestamp) as date,

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

group by 1

order by date desc