pleasehiremeOpenSea Average ETH Trade Size per Hour
    Updated 2022-07-06
    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