pleasehiremeOpenSea Average ETH Trade Size per Hour
Updated 2022-07-06
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
›
⌄
with dat1 as (
select
date_trunc('hour', block_timestamp) as TimeH,
platform_name,
sum(case when nft_address = '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85' and currency_symbol in ('WETH','ETH') then price else 0 end) as ENS,
sum(case when nft_address = '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85' and currency_symbol in ('WETH','ETH') then 1 else 0 end) as ENS_TRADE_COUNT,
count(distinct buyer_address) as buyers,
count(distinct seller_address) as sellers,
count(seller_address) as trades,
count(distinct buyer_address)/count(distinct seller_address) as Buyer_v_Seller,
sum(case when currency_symbol IN ('WETH','ETH') then 1 end) as TOTAL_ETH_WETH_trades,
sum(case when currency_symbol IN ('WETH') then 1 end) as WETH_trades,
sum(case when currency_symbol IN ('WETH') then price end) as WETH_sales,
sum(case when currency_symbol IN ('ETH') then price end) as ETH_sales,
count(distinct case when currency_symbol = 'WETH' then seller_address end) as WETH_SELLERS,
count(distinct case when currency_symbol IN ('ETH', 'WETH') then seller_address end) as ALL_SELLERS,
sum(price_usd) as USD,
sum(price_usd)/count(buyer_address) as USD_per_trade,
sum(case when currency_symbol in ('WETH', 'ETH') then price end)/sum(case when currency_symbol in ('WETH','ETH') then 1 end) as ETH_per_trade
from ethereum.core.ez_nft_sales
where block_timestamp > '2021-01-01' and price_usd < 5000000 and platform_name in ('opensea') --and nft_address = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d'
group by 1, 2 order by 1 desc, 2
)
select substring(TimeH, 12, 8) as UTCHour, avg(ETH_per_trade) from dat1
group by 1
Run a query to Download Data