with delegates as (

select DISTINCT EVENT_INPUTS:toDelegate as Delegate

from arbitrum.core.fact_event_logs

where EVENT_NAME = 'DelegateChanged'

and CONTRACT_ADDRESS = '0x912ce59144191c1204e64559fe8253a0e49e6548' -- ARB tokens

and TX_STATUS = 'SUCCESS' and EVENT_REMOVED = 'false'

and BLOCK_TIMESTAMP != current_date)

select

BLOCK_TIMESTAMP::date as date,

initcap(project_NAME) as platfrom,

count(DISTINCT TX_HASH) as count_tx

from arbitrum.core.fact_event_logs a JOIN

arbitrum.core.dim_labels b on a.CONTRACT_ADDRESS = b.address

where ORIGIN_FROM_ADDRESS in (select * from delegates)

and BLOCK_TIMESTAMP >= '2023-01-01' and LABEL_TYPE in ('layer2')

and TX_STATUS = 'SUCCESS' and EVENT_REMOVED = 'false'

group by 1,2