boomer77LIDO - staking eth
Updated 2022-06-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with staked as (select date_trunc('day', block_timestamp) as dt, count(distinct tx_hash) as tx_count,
count(distinct from_address) as address_count,
sum(eth_value) as eth_staked,
SUM(eth_staked) OVER(ORDER BY dt asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS eth_staked_cumulative
from ethereum.core.fact_transactions
where to_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' and eth_value > 0
group by 1),
price as (select date_trunc('day', hour) as dt, avg(price) as ETH_price
from ethereum.core.fact_hourly_token_prices
where symbol = 'WETH'
group by 1),
token_balance as (select balance_date, sum(non_adjusted_balance/1e18) as stETH_balance
from ethereum.erc20_balances
where contract_address = lower('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84') and date(balance_date) >= CURRENT_DATE - 92
group by 1)
select *, b.ETH_price::int as eth_usd, c.steth_balance::int as steth_bal
from staked a
left join price b on a.dt = b.dt
left join token_balance c on a.dt = c.balance_date
where a.dt >= current_date - 91
Run a query to Download Data