pouya_22Gain or Lose - stETH gain or lose Distribution
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 base as (select distinct(origin_from_address) as staker, sum(amount) as amount_staked
from ethereum.core.ez_eth_transfers
where eth_to_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
group by 1),
in_table as (select tx_id, amount_in
from ethereum.dex_swaps
where direction = 'IN'
and token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'),
out_table as (select tx_id, amount_out
from ethereum.dex_swaps
where direction = 'OUT'
and token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'),
combo as (select a.tx_id, amount_in, amount_out
from in_table a
join out_table b
on a.tx_id = b.tx_id),
base2 as (select origin_address,
sum(amount_in) as ETH_sent,
sum(amount_out) as stETH_received
from combo a
join ethereum.udm_events b
where a.tx_id = b.tx_id
and origin_address in (select staker from base)
group by 1)
select
stETH_received - ETH_sent as gain_loss,
case
when gain_loss < 0 then 'Loss'
when gain_loss = 0 then 'Equal'
when gain_loss > 0 then 'Gain'
end as differences,
Run a query to Download Data