SELECT

tx_hash,

event_inputs :to :: STRING AS delegator,

event_inputs :value / POW(10,18) :: NUMBER AS amount

FROM ethereum.core.fact_event_logs

WHERE contract_address IN ('0x5afe3855358e112b5647b952709e6165e1c1eeee', LOWER('0xA0b937D5c8E32a80E3a8ed4227CD020221544ee6'))

AND event_name = 'Transfer'

),

delegates AS (

SELECT

l.tx_hash,

l.block_timestamp,

event_inputs :delegate :: STRING AS delegate,

amount,

event_inputs :delegator :: STRING AS delegator

FROM "ETHEREUM"."CORE"."FACT_EVENT_LOGS" l

INNER JOIN safe_balances_airdrop b

ON l.tx_hash = b.tx_hash

WHERE contract_address = '0x469788fe6e9e9681c6ebf3bf78e7fd26fc015446'

AND contract_name = 'DelegateRegistry'

AND event_name = 'SetDelegate'

),

pre_final AS (

SELECT

delegate,

COUNT(distinct delegator) as num_delegators,

sum(amount) as amount_delegated,

count(distinct id) as num_votes_cast,

ARRAY_AGG(DISTINCT proposal_title :: STRING) as props_voted_on

FROM delegates d

LEFT OUTER JOIN ethereum.core.ez_snapshot s

ON d.delegate = LOWER(s.voter)

WHERE space_id ILIKE 'safe.eth'