CoinConverseUntitled Query
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_staked as (select date_trunc('hour', block_timestamp) as dt, tx_hash, origin_from_address, amount as amount_eth_staked, steth_staked_price, 'staked' as actions
from ethereum.core.ez_eth_transfers
left join (select hour, price as steth_staked_price from ethereum_core.fact_hourly_token_prices where symbol = 'stETH')
on hour = date_trunc('hour', block_timestamp)
where eth_to_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84'
and origin_function_signature in ('0x','0xa1903eab')
and block_timestamp::date >= '2022-01-01'),
steth_unstaked as (select date_trunc('hour', block_timestamp) as dt, tx_hash, amount_out as amount_eth_unstaked
from ethereum.core.ez_dex_swaps
where symbol_in = 'stETH'
and symbol_out = 'WETH'
and block_timestamp::date >= '2022-01-01'),
all_address AS (select distinct tx_hash as tx_hash_orig, origin_from_address
from ethereum.core.ez_eth_transfers
where block_timestamp::date >= '2022-01-01'
),
steth_sold as (select
dt,
tx_hash,
origin_from_address,
amount_eth_unstaked,
price as sell_price,
'sold' as actions
from steth_unstaked
left join all_address on tx_hash = tx_hash_orig
left join (select hour, price from ethereum_core.fact_hourly_token_prices where symbol in ('stETH')) on hour = dt
where origin_from_address in (select origin_from_address from eth_staked)
and amount_eth_unstaked > 0),
combined_actions as (select * from eth_staked
union all
select * from steth_sold),
Run a query to Download Data