WITH all_delegations AS (

SELECT

DELEGATOR,

TO_DELEGATE AS delegate,

NEW_BALANCE - PREVIOUS_BALANCE AS amount,

BLOCK_TIMESTAMP

FROM optimism.core.fact_delegations

WHERE TO_DELEGATE = lower('{{delegate}}')

UNION ALL

SELECT

DELEGATOR,

FROM_DELEGATE AS delegate,

-(NEW_BALANCE - PREVIOUS_BALANCE) AS amount,

BLOCK_TIMESTAMP

FROM optimism.core.fact_delegations

WHERE FROM_DELEGATE = lower('{{delegate}}')

),

latest_delegations AS (

SELECT

DELEGATOR,

amount,

ROW_NUMBER() OVER (PARTITION BY DELEGATOR ORDER BY BLOCK_TIMESTAMP DESC) AS rn

FROM all_delegations

),

summed_del AS (

SELECT

DELEGATOR,

SUM(amount) AS tot_amount

FROM latest_delegations

WHERE rn = 1