adambalaLost or Gained by stETH/ETH Peg
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 a as ( select
block_timestamp,
tx_id,
from_address as user,
case when token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then 'WETH' else 'stETH' end as token_in,
amount_out as swap_in,
amount_usd as swap_in_usd
from ethereum.dex_swaps
where direction = 'OUT'
and pool_address = '0xdc24316b9ae028f1497c275eb9192a3ea0f67022' ),
b as ( select
block_timestamp,
tx_id,
from_address as user,
case when token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then 'WETH' else 'stETH' end as token_out,
amount_in as swap_out,
amount_usd as swap_out_usd
from ethereum.dex_swaps
where direction = 'IN'
and pool_address = '0xdc24316b9ae028f1497c275eb9192a3ea0f67022' ),
c as (select a.block_timestamp,a.tx_id,a.user,a.swap_in,a.swap_in_usd,b.swap_out,b.swap_out_usd,a.token_in,token_out,(SWAP_OUT_USD-SWAP_IN_USD) as diff,(SWAP_OUT-SWAP_IN) as diff_eth
from a inner join b on a.tx_id=b.tx_id
)
select
case when TOKEN_IN='WETH' then 'WETH to stETH' else 'stETH to WETH' end as swap ,
case
when diff between 0 and 100 then '0-100 gain'when diff between 100 and 500 then '100-500 gain'when diff between 500 and 1000 then '500-1000 gain'when diff >1000 then '>1000 gain'
when diff between 0 and -100 then '0-100 lose'when diff between -100 and -500 then '100-500 lose'when diff between -500 and -1000 then '500-1000 lose'when diff <-1000 then '>1000 lose'end as difference,
count(distinct TX_ID) as tx ,
count(distinct USER) as USERs,
BLOCK_TIMESTAMP::date as date from c group by 1,2,5
Run a query to Download Data