Beacon Real-Time Data: Monitoring Liquid Restaking ETH Total Value locked
This document explains the process of utilizing ZettaBlock's Beacon real-time data to monitor the ETH Total Value locked of an address
that is part of a staking or re-staking protocol.
Find the query here.
The SQL query below is designed to track and aggregate Ethereum deposit and withdrawal transactions, to get ETH TVL for a specific address
. The query calculates the net flow of Ether (ETH) for this address by analyzing traces
associated with ETH
transfers on the Ethereum blockchain and Beacon withdrawals
.
Run the below query on 'database' setting for fastest results.
Feel free to change the
t.from_address
andaddress
in the following query to get results for a differentwallet
associated to a Liquid Staking (LST) or Liquid Restaking (LRT) protocol.
-- The following SQL tracks one of many addresses associated with Renzo ETH deposits, not all of them.
WITH deposit_value as (
select
SUM(value/POW(10,18)) as amount,
block_date as date
FROM ethereum_mainnet.traces as t
WHERE t.from_address = '0x093f6c270ac22ec240f0c6fd7414ea774ca8d3e5' -- Renzo Protocol EigenLayer: Eigen Pod Implementation address
AND t.to_address = '0x00000000219ab540356cbb839cbe05303d7705fa' -- beacon deposit address
AND substr(t.input, 1,10) = '0x22895118'
GROUP By 2
),
withdraw_value as (
select
SUM(amount/POW(10,9)) as amount,
block_date as date
from beacon_mainnet.withdrawals
WHERE address = '0x093f6c270ac22ec240f0c6fd7414ea774ca8d3e5' -- Renzo Protocol EigenLayer: Eigen Pod Implementation address
GROUP BY 2
),
agg_by_date as (
select
date,
-amount as amount
FROM withdraw_value
UNION ALL
select
date,
amount
FROM deposit_value
)
select
DISTINCT
coalesce(agg.date) as date,
SUM(agg.amount) OVER (ORDER BY agg.date) as net_flow
FROM agg_by_date as agg
Creating Real-Time API
Once you run the query, you can create the GraphQL endpoint with one click:
And that's it! Your API is set to refresh in real-time.
Updated 6 months ago