h4wkSummary of Lido's ETH HODL or Sell
Updated 2022-06-12
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
›
⌄
-- Q11. Identify accounts that have staked ETH with Lido when the price of ETH was much higher or much lower than it is now.
-- Have they held or sold their stETH?
with eth_stake as (
select date_trunc('hour', block_timestamp) as date,
tx_hash,
event_inputs:amount/pow(10,18) as stake_amount,
price as eth_stake_price,
origin_from_address as address,
null as sell_amount,
null as sell_price
from ethereum.core.fact_event_logs join (select hour, price from ethereum.core.fact_hourly_token_prices where symbol = 'WETH') on hour = date_trunc('hour', block_timestamp)
where contract_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
and event_name = 'Submitted'
and origin_function_signature in ('0xa1903eab')
),
sell_steth as (
select date_trunc('hour', block_timestamp) as date,
tx_hash,
amount_in,
origin_from_address as address
from ethereum.core.ez_dex_swaps
where token_in = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
),
steth_sold as (
select date,
tx_hash,
null as stake_amount,
null as eth_stake_price,
address,
amount_in as sell_amount,
price as sell_price
from sell_steth left join (select hour, price from ethereum.core.fact_hourly_token_prices where SYMBOL = 'WETH') on hour = date
where address in (select address from eth_stake)
and amount_in > 0
)
Run a query to Download Data