freemartianStakers Gain or lose
Updated 2022-06-15
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 eth as (
select price, hour::date as TIME
from ethereum_core.fact_hourly_token_prices
where symbol = 'WETH'
and hour > CURRENT_DATE - 90
),
steth as (
select price, hour::date as TIME
from ethereum_core.fact_hourly_token_prices
where symbol = 'stETH'
and hour > CURRENT_DATE - 90
),
daily_stake as (
select sum(event_inputs:value)/pow(10, 18) as daily_amount, date_trunc('day', block_timestamp::date) as TIME
from ethereum_core.fact_event_logs
where contract_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
and block_timestamp > CURRENT_DATE - 90
and tx_status = 'SUCCESS'
group by TIME
),
usd_prices as (
select sum(daily_amount) as daily, avg(steth.price) as daily_steth, avg(eth.price) as daily_eth, d.TIME as date
from daily_stake d
inner join eth on eth.TIME = d.TIME
inner join steth on steth.TIME = d.TIME
group by d.TIME
)
select date ,(u.daily_eth - u.daily_steth) as "Peg Interest or Lose"
from usd_prices u
order by date