Query editor
/*This query calculates post Merge protocol APR, Execution & Consensus Layers APRs,displays the Compounding effect (Compounding EL rewards share in Protocol APR),protocol APR moving average for 7-day, 14-day and 30-day.*/-- This CTE calculates the pre and post share rates (before and after token rebased event)WITHshares AS (SELECT(preTotalEther * 1e27) / CAST(preTotalShares AS DOUBLE) AS pre_share_rate,(postTotalEther * 1e27) / CAST(postTotalShares AS DOUBLE) AS post_share_rate,*FROMlido_ethereum.steth_evt_TokenRebased),-- This CTE calculates the staking APR and Lido rewardsoracle_time AS (SELECTLAG(evt_block_time, 1, evt_block_time - INTERVAL '24' hour ) OVER (ORDER BY evt_block_time NULLS FIRST) AS time,evt_block_time AS next_time,((postTotalPooledEther - preTotalPooledEther) * 365 * 24 * 60 * 60) / CAST((preTotalPooledEther * timeElapsed) AS DOUBLE) AS staking_APR,(postTotalPooledEther - preTotalPooledEther) AS lido_rewards,preTotalPooledEther,postTotalPooledEther,timeElapsedFROM lido_ethereum.LegacyOracle_evt_PostTotalSharesWHERE evt_block_time >= date '2022-09-01'AND evt_block_time <= date '2023-05-16'UNION ALLSELECTLAG(d.evt_block_time, 1, d.evt_block_time - INTERVAL '24' hour) OVER (ORDER BY d.evt_block_time NULLS FIRST) AS time,d.evt_block_time AS next_time,(365 * 24 * 60 * 60 * (s.post_share_rate - s.pre_share_rate)) / CAST((s.pre_share_rate * s.timeElapsed * 0.9) AS DOUBLE),withdrawalsWithdrawn + postCLBalance - preCLBalance + executionLayerRewardsWithdrawn AS lido_rewards,r.preTotalEther,r.postTotalEther,r.timeElapsedFROM lido_ethereum.steth_evt_ETHDistributed AS dLEFT JOIN lido_ethereum.steth_evt_TokenRebased AS r ON d.evt_tx_hash = r.evt_tx_hashLEFT JOIN shares AS s ON d.evt_tx_hash = s.evt_tx_hash),-- This CTE calculates rewards withdrawnrewards_by_call AS (SELECToracle.next_time AS time,COALESCE(SUM(TRY_CAST(output_amount AS double)), 0) AS withdrawn_rewards,SUM(COALESCE(SUM(TRY_CAST(output_amount AS double)), 0)) OVER (ORDER BY oracle.next_time NULLS FIRST) AS cumul_rewardsFROM oracle_time AS oracleLEFT JOIN lido_ethereum.LidoExecutionLayerRewardsVault_call_withdrawRewards AS el ON el.call_block_time > oracle.timeAND el.call_block_time <= oracle.next_timeWHERE NOT oracle.time IS NULLAND el.call_success = TRUEGROUP BY 1),