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 and address in the following query to get results for a different wallet 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