with rewards as

(select

date_trunc('day',a.BLOCK_TIMESTAMP) as time,

sum(a.RAW_AMOUNT)/1e18 rewards,

a.TO_ADDRESS as address,

b.event_inputs:name as nodename

--TO_ADDRESS as node

from ethereum.core.fact_token_transfers a

left join ethereum.core.fact_event_logs b

on a.TO_ADDRESS = b.event_inputs:rewardAddress

where

a.FROM_ADDRESS = '0x0000000000000000000000000000000000000000'

and

a.CONTRACT_ADDRESS = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

and

b.event_name = 'NodeOperatorAdded'

AND b.contract_address = '0x55032650b14df07b85bf18a3a3ec8e0af2e028d5'

group by 1,3,4),

lido_amounts as

(

select

date_trunc('day',BLOCK_TIMESTAMP) as date,

sum(sum(AMOUNT)) over (order by date_trunc('day',BLOCK_TIMESTAMP)) lido_staked

from ethereum.core.ez_eth_transfers

where

"ETH_TO_ADDRESS" = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'

group BY 1)

select

rewards.time, rewards, rewards.nodename, lido_amounts.lido_staked,

rewards*365/lido_amounts.lido_staked/0.1*100*0.9 "Lido staking APR"

from rewards

left join lido_amounts on rewards.time = lido_amounts.date