sinahosseinzadehUntitled Query
    Updated 2022-08-08
    with pricet as (select hour::date as date,avg(price) as price12 from optimism.core.fact_hourly_token_prices where token_address ='0x4200000000000000000000000000000000000042'
    and hour >= '2022-07-01' and hour < '2022-08-01'
    group by 1),

    eth_price as (
    select hour::date as date, avg(price) as price12
    from ethereum.core.fact_hourly_token_prices
    where symbol = 'WETH'
    and date >= '2022-07-01' and date < '2022-08-01'
    group by 1),

    pricet2 as (select 'OP' as currency1,* from pricet union select 'ETH' as currency1,* from eth_price)


    select
    sum(price*t2.price12) as volume,
    count (distinct tx_hash) as Sales_Count,
    count (distinct NFT_Address) as NFTs_Projects_Count,
    count (distinct tokenid) as NFTs_Count,
    count (distinct seller_address) as Sellers,
    count (distinct buyer_address) as Buyers
    from optimism.core.ez_nft_sales t1 join pricet2 t2 on t2.date = t1.block_timestamp::Date and t1.currency_symbol = t2.currency1
    join pricet t3 on t1.block_timestamp::Date = t3.date
    where block_timestamp >= '2022-07-01' and block_timestamp < '2022-08-01'
    Run a query to Download Data