with shares AS (

SELECT

DECODED_LOG:preTotalEther *1e27 / DECODED_LOG:preTotalShares as pre_share_rate

, DECODED_LOG:postTotalEther * 1e27 / DECODED_LOG:postTotalShares as post_share_rate

, *

FROM ethereum.core.ez_decoded_event_logs

where EVENT_NAME = 'TokenRebased'

)

, oracles_data AS (

SELECT

date_trunc('minute', BLOCK_TIMESTAMP) as time

, ((DECODED_LOG:postTotalPooledEther - DECODED_LOG:preTotalPooledEther) * 365 * 86400 / DECODED_LOG:preTotalPooledEther ) / DECODED_LOG:timeElapsed * 100 AS protocol_apr

, ((DECODED_LOG:postTotalPooledEther - DECODED_LOG:preTotalPooledEther) * 365 * 86400 / DECODED_LOG:preTotalPooledEther ) / DECODED_LOG:timeElapsed * 0.9 * 100 AS "Lido staking APR(instant)"

, DECODED_LOG:postTotalPooledEther as postTotalPooledEther

, DECODED_LOG:preTotalPooledEther as preTotalPooledEther

FROM ethereum.core.ez_decoded_event_logs

WHERE event_name = 'PostTotalShares'

AND BLOCK_TIMESTAMP BETWEEN '2022-09-01 00:00' AND '2023-05-16 00:00'

AND postTotalPooledEther > ''

UNION all

SELECT

date_trunc('minute', BLOCK_TIMESTAMP) as time

, (365 * 24 * 60 * 60 * (post_share_rate - pre_share_rate) / pre_share_rate / DECODED_LOG:timeElapsed * 100) / 0.9 AS protocol_apr

, 365 * 24 * 60 * 60 * (post_share_rate - pre_share_rate) / pre_share_rate / DECODED_LOG:timeElapsed * 100 AS "Lido staking APR(instant)"

, DECODED_LOG:preTotalEther

, DECODED_LOG:postTotalEther

FROM shares

)

SELECT