cypherstETH to ETH swaps rate
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
35
36
›
⌄
with steth_part as (select
block_timestamp,
tx_id,
amount_usd as steth_amount_usd
from ethereum.udm_events
where from_address = lower('0x0000000000000000000000000000000000000000')
and contract_address = lower('0xae7ab96520de3a18e5e111b5eaab095312d7fe84')),
eth_part as (select
tx_id,
amount_usd as eth_amount_usd
from ethereum.udm_events
where to_address = lower('0xae7ab96520de3a18e5e111b5eaab095312d7fe84' )
and to_address_name = 'stETH'),
eth_price as (select
date_trunc('day', hour) as date,
avg(price) as eth_price
from ethereum.core.fact_hourly_token_prices
where token_address is null
group by date),
temp as (select * from steth_part
left join eth_part using (tx_id)),
sum_difference as (select
block_timestamp,
iff(steth_amount_usd is null, 0, steth_amount_usd - eth_amount_usd) as difference
from temp),
daily_sum_difference as (select
date_trunc('day', block_timestamp) as date,
sum(difference) as total_usd_lost,
avg(difference) as avg_usd_lost,
median(difference) as median_usd_lost
Run a query to Download Data