MostafaHolder and Seller of ETH Stakers by Lower Price
Updated 2022-06-15Copy Reference Fork
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 data_staked as (
select
block_timestamp::date as date,
origin_address,
amount
from
ethereum.udm_events
where
event_type = 'native_eth'
and event_name = 'transfer'
and to_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
and block_timestamp::date >= CURRENT_DATE - 730 and block_timestamp::date < CURRENT_DATE - 2
),
eth_price as (
select date(hour) as date, avg(price ) as eth_price
from ethereum.core.fact_hourly_token_prices
where date(hour) >= '2020-01-01' and symbol = 'WETH'
group by 1
), data_all as (
select a.date, origin_address, amount, eth_price
from data_staked a join eth_price b on a.date = b.date
), data_all_label as (
select origin_address, case when 1128 > eth_price then 'below' else 'above' end as label from data_all
), data_address as (
select distinct origin_address as address from data_all_label where label = 'above'
), total_sold as (
select 'eth' as label, count(*) as total from ethereum.erc20_balances where user_address in (select address from data_address)
and balance_date::date = CURRENT_DATE -2 and contract_address = LOWER('0xae7ab96520de3a18e5e111b5eaab095312d7fe84') -- steth contract
and balance > 0
), total_all as (
select 'eth' as label, count(distinct origin_address) as total from data_all_label where label = 'above'
), total_hold as (
select a.total- b.total as total from total_all a join total_sold b on a.label = b.label
)
select 'Holding stETH' as labeling, total from total_sold
union all
Run a query to Download Data