SELECT

block_number,

DATE(block_timestamp) AS date,

delegation_type,

delegator,

from_delegate AS old_delegate,

to_delegate AS delegate,

raw_previous_balance / POW(10, 18) AS old_bal,

raw_new_balance / POW(10, 18) AS new_bal,

new_bal - old_bal AS amount,

tx_hash

FROM optimism.core.fact_delegations

ORDER BY date DESC

),

latest_del AS (

SELECT

delegator,

delegate,

SUM(amount) AS delegated_amount

FROM all_del

GROUP BY 1, 2

ORDER BY delegated_amount DESC

LIMIT 1

date,

delegator,

delegate,

SUM(amount) AS delegated_amount,

RANK() OVER (PARTITION BY date ORDER BY SUM(amount) DESC) AS rank

FROM all_del

GROUP BY 1, 2, 3

order by delegated_amount desc