flipside

    Lido Buffer
    lidoLido Buffer
    Updated 3 days ago
    /*
    The buffer is formed by partial withdrawals, new ETH deposits, and execution layer rewards
    */
    -- retrieves Lido blocks data
    WITH blocks AS (
    SELECT
    BLOCK_NUMBER
    , BLOCK_TIMESTAMP
    , GAS_USED
    , block_header_json:baseFeePerGas
    , block_header_json:baseFeePerGas * GAS_USED/ 1e18 AS total_burn
    FROM ethereum.core.fact_blocks
    WHERE MINER = '0x388c818ca8b9251b393131c08a736a67ccb19297' -- EL vault
    and block_timestamp >= '2022-09-15 06:43:23.000' -- earliest block timestamp
    )

    -- calculates daily fee cost for each Lido block
    , eth_tx AS (
    SELECT
    BLOCK_TIMESTAMP
    , BLOCK_NUMBER
    , GAS_USED
    , GAS_USED * GAS_PRICE/1e9 AS fee
    FROM ethereum.core.fact_transactions
    WHERE BLOCK_NUMBER in (SELECT DISTINCT BLOCK_NUMBER from blocks )
    and block_timestamp >= '2022-09-15 06:43:23.000'
    )

    -- aggregates txns by block
    , eth_tx_agg AS (
    SELECT
    BLOCK_NUMBER
    , max(BLOCK_TIMESTAMP) AS BLOCK_TIMESTAMP
    , sum(GAS_USED) AS block_gas_used
    , sum(fee) AS fee
    Last run: 3 days ago
    TIME
    AMOUNT
    ETH_BALANCE
    1
    		2023-05-01 00:00:00.0003970.835659455272479.401816265
    2
    		2023-05-02 00:00:00.000-1515.159595964270964.242220301
    3
    		2023-05-03 00:00:00.000-1007.96295084269956.27926946
    4
    		2023-05-04 00:00:00.0001316.440533457271272.719802918
    5
    		2023-05-05 00:00:00.0004347.844003248275620.563806166
    6
    		2023-05-06 00:00:00.0006386.197916429282006.761722594
    7
    		2023-05-07 00:00:00.000-8582.317248462273424.444474132
    8
    		2023-05-08 00:00:00.000-220.031078416273204.413395717
    9
    		2023-05-09 00:00:00.0001599.119411208274803.532806925
    10
    		2023-05-10 00:00:00.0003332.683819864278136.216626789
    11
    		2023-05-11 00:00:00.000-2511.846583213275624.370043575
    12
    		2023-05-12 00:00:00.00048168.232885116323792.602928691
    13
    		2023-05-13 00:00:00.00029899.110386309353691.713315001
    14
    		2023-05-14 00:00:00.00017946.721887192371638.435202193
    15
    		2023-05-15 00:00:00.00040931.591891487412570.02709368
    16
    		2023-05-16 00:00:00.00029281.67712534441851.70421902
    17
    		2023-05-17 00:00:00.000-400338.51390716441513.190311856
    18
    		2023-05-18 00:00:00.000-1985.70141410739527.48889775
    19
    		2023-05-19 00:00:00.000-36861.9463658452665.542531905
    20
    		2023-05-20 00:00:00.000-2116.690829568548.851702336
    ...
    747
    43KB
    443s