h4wkstETH pegged ETH 3
Updated 2022-06-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
›
⌄
-- Q10. Of the stakers that have swapped ETH to stETH,
-- how much ETH did they gain or lose depending on the peg of stETH to ETH at the time of the swap?
with daily_steth_price as (
select date_trunc(day, hour) as date,
avg(price) as steth_price
from ethereum.core.fact_hourly_token_prices
where token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
group by date)
, daily_eth_price as (
select date_trunc(day, hour) as date,
avg(price) as eth_price
from ethereum.core.fact_hourly_token_prices
where symbol = 'WETH'
group by date)
, from_steth as (
select date_trunc(day, block_timestamp) as DATE
, sum(amount_in) as amount_from_stETH
, sum(amount_out) as amount_to_ETH
FROM ethereum.core.ez_dex_swaps
WHERE symbol_out = 'WETH' and token_in = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
and block_timestamp::date >= '2022-01-01' and block_timestamp::date < CURRENT_DATE
group by date
)
select e.date
, amount_from_stETH as from_stETH
, amount_to_eth as to_ETH
, amount_to_ETH - amount_from_steth as eth_gain
, (amount_to_ETH - amount_from_steth) / amount_from_steth * 100 as avg_diff_percentage
, eth_price
from from_steth e join daily_eth_price p on e.date = p.date
Run a query to Download Data