cypherEth miners sellers
Updated 2022-09-28
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 all_miners as (select
miner,
count(*) as n_blocks_mined
from ethereum.core.fact_blocks
where block_timestamp <= '2022-9-14'
group by miner),
miners_10 as (select
*
from all_miners
where n_blocks_mined >= 10),
total_weth_sell_volume as (select
date_trunc('day', block_timestamp) as date,
sum(amount_in_usd) as total_usd_volume
from ethereum.core.ez_dex_swaps
where token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
and date >= '2022-7-1'
group by date
),
miners_weth_sell_volume as (select
date_trunc('day', block_timestamp) as date,
sum(amount_in_usd) as miners_usd_volume
from ethereum.core.ez_dex_swaps
where token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
and origin_from_address in (select miner from all_miners)
and date >= '2022-7-1'
group by date ),
comb as (select * from total_weth_sell_volume
left join miners_weth_sell_volume using (date))
select
*,
(miners_usd_volume/total_usd_volume)*100 as percentage_miners_dex_sellers
Run a query to Download Data