select day, sum(amount) as delegated from (

select date_trunc('day',block_timestamp) as day, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount

from berachain.testnet.fact_event_logs

where contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'

and topics[0]='0x99966631dd6d6c02c5416ca2369709e025ff974a2f1b3f11c8b74acc67731f0e'

and concat('0x',substr(TOPICS[2], 27,64))=lower('{{validator_address}}')

)

group by 1

),

unbond as (

select day, sum(amount) as unbonded from (

select date_trunc('day',block_timestamp) as day, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount

from berachain.testnet.fact_event_logs

where ORIGIN_TO_ADDRESS='0xbda130737bdd9618301681329bf2e46a016ff9ad'

and contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'

and ORIGIN_FUNCTION_SIGNATURE='0xe1f63d2e'

and concat('0x',substr(TOPICS[2], 27,64))=lower('{{validator_address}}')

)

group by 1

select day, delegated,unbonded, sum(net_delegations) over (order by day) as cum_delegated from (

select coalesce(d.day,u.day) as day,coalesce(delegated,0) as delegated,coalesce(unbonded,0) as unbonded, delegated-coalesce(unbonded,0) as net_delegations from delegate d

left join unbond u on u.day=d.day

order by day desc

order by day desc