WITH genesis AS(

SELECT

MIN(DATE_TRUNC('day', block_timestamp)) AS genesis_date

FROM arbitrum.core.ez_decoded_event_logs

WHERE contract_address = '0x912ce59144191c1204e64559fe8253a0e49e6548'

AND event_name = 'DelegateVotesChanged'

AND decoded_log:delegate <> LOWER('0x00000000000000000000000000000000000a4b86')

),

delegates AS(

SELECT

DISTINCT decoded_log:delegate AS delegate

FROM arbitrum.core.ez_decoded_event_logs

WHERE contract_address = '0x912ce59144191c1204e64559fe8253a0e49e6548'

AND event_name = 'DelegateVotesChanged'

AND decoded_log:delegate <> LOWER('0x00000000000000000000000000000000000a4b86')

),

date_gen AS(

WITH RECURSIVE date_seq AS (

SELECT genesis_date -- Start date

FROM genesis

UNION ALL

SELECT DATEADD('day', 1, genesis_date) AS date -- Increment date

FROM date_seq

WHERE date <= CURRENT_DATE() -- Ensure the recursion stops

)

SELECT *

FROM date_seq

),

date_delegates AS(

SELECT

genesis_date AS date,

delegate