from arbitrum.core.fact_token_transfers

where origin_to_address = lower('0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9')

and contract_address = lower('0x912ce59144191c1204e64559fe8253a0e49e6548')

and from_address = origin_to_address)

,

final as ( select a.origin_from_address, a.tx_hash, arb, EVENT_INPUTS:delegate::string as delegate

from delegator a join arbitrum.core.fact_event_logs b on a.tx_hash = b.tx_hash

where EVENT_INPUTS:delegate = a.origin_from_address

having delegate is not null

,

final_2 as ( select a.origin_from_address, a.tx_hash, arb, EVENT_INPUTS:delegate::string as delegate

from delegator a join arbitrum.core.fact_event_logs b on a.tx_hash = b.tx_hash

where EVENT_INPUTS:delegate != a.origin_from_address

and EVENT_INPUTS:delegate != '0x00000000000000000000000000000000000a4b86'

having delegate is not null

,

final_3 as ( select 'Delegators to Others' as type, label_type, sum(amount_usd) as volume

from arbitrum.core.ez_eth_transfers a join arbitrum.core.dim_labels b on a.eth_to_address = b.address

where block_timestamp::date >= '2022-11-01'

and eth_from_address in (select origin_from_address from final_2)

group by 1,2

select 'Non-Delegators' as type, label_type, sum(amount_usd) as volume

from arbitrum.core.ez_eth_transfers a join arbitrum.core.dim_labels b on a.eth_to_address = b.address

where block_timestamp::date >= '2022-11-01'

and eth_from_address not in (select origin_from_address from final)

and eth_from_address not in (select origin_from_address from final_2)

group by 1,2

select 'Self Delegators' as type, label_type, sum(amount_usd) as volume

from arbitrum.core.ez_eth_transfers a join arbitrum.core.dim_labels b on a.eth_to_address = b.address