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,

rank() over(partition by delegate order by block_timestamp desc) as rank

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

count(distinct delegate) as delegates,

sum(current_bal) as total_op_gov,

median(current_bal) as med_op_gov,

avg(current_bal) as avg_op_gov

from delegations

where rank = 1

and current_bal > 0

order by current_bal desc