SELECT tx_hash

FROM arbitrum.core.fact_transactions

WHERE to_address LIKE lower('0x912CE59144191C1204E64559FE8253a0e49E6548')

),

in_out_t AS (

SELECT --OUT

from_address AS user,

-1 * SUM(raw_amount / 1e18) AS "Volume"

FROM arbitrum.core.fact_token_transfers

WHERE CONTRACT_ADDRESS LIKE lower('0x912CE59144191C1204E64559FE8253a0e49E6548')

GROUP BY 1

UNION

SELECT --IN

to_address AS user,

SUM(raw_amount / 1e18) AS "Volume"

FROM arbitrum.core.fact_token_transfers

WHERE CONTRACT_ADDRESS LIKE lower('0x912CE59144191C1204E64559FE8253a0e49E6548')

GROUP BY 1

),

balance_t AS (

SELECT

user,

SUM("Volume") AS "Balance"

FROM in_out_t

GROUP BY 1

),

last_delegation_time AS (

SELECT

event_inputs:delegator as delegator,

max(block_timestamp) as last_delegate

FROM arbitrum.core.fact_event_logs

WHERE tx_hash in (SELECT * from tx_t) AND event_name LIKE 'DelegateChanged'

GROUP BY 1

),

delegator_t AS (