SELECT

event_inputs:delegator as delegator,

max(block_timestamp) as last_delegate

FROM arbitrum.core.fact_event_logs

WHERE tx_hash in (SELECT * from (SELECT tx_hash FROM arbitrum.core.fact_transactions WHERE to_address LIKE lower('0x912CE59144191C1204E64559FE8253a0e49E6548'))) AND event_name LIKE 'DelegateChanged'

GROUP BY 1

),

delegator_t AS (

SELECT

delegator,

event_inputs:toDelegate as Delegate

FROM arbitrum.core.fact_event_logs

LEFT outer JOIN last_delegation_time

on last_delegate = block_timestamp

AND delegator = event_inputs:delegator

WHERE event_name LIKE 'DelegateChanged' and tx_hash in (SELECT * from (SELECT tx_hash FROM arbitrum.core.fact_transactions WHERE to_address LIKE lower('0x912CE59144191C1204E64559FE8253a0e49E6548'))) AND DELEGATOR is not NULL

SELECT

date_trunc('week', block_timestamp) as date,

count(DISTINCT tx_hash) as "Number of Transactions",

sum("Number of Transactions") over (order by date) as "Cumulative Number of Transactions",

count(DISTINCT from_address) as "Number of Delegators",

sum(tx_fee) as "Total Gas Fee (ETH)",

avg(tx_fee) as "Average Gas Fee (ETH)"

FROM arbitrum.core.fact_transactions

where from_address IN (SELECT delegator from delegator_t)

GROUP BY 1