alirsQuixotic NFT Dashboard-part02
    Updated 2022-08-03
    with data_price as (
    select
    hour::date as date,
    avg(price) as price
    from
    ethereum.core.fact_hourly_token_prices
    where
    token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --wrapped ETH
    and
    hour::date >= CURRENT_DATE - 90
    group by 1
    ),
    base2 as(select
    ns.BLOCK_TIMESTAMP::date as day,
    ns.ORIGIN_FROM_ADDRESS,tr.STATUS,
    ns.ORIGIN_TO_ADDRESS,ns.PLATFORM_NAME,ns.PRICE_USD,ns.TX_HASH
    ,tr.ETH_VALUE,tr.FROM_ADDRESS,tr.TX_HASH

    from
    optimism.core.ez_nft_sales ns join optimism.core.fact_transactions tr on ns.tx_hash=tr.tx_hash
    where day>=CURRENT_DATE-90)
    ,total as (select * from base2 join data_price b on base2.day = b.date)
    -- )
    select
    sum(eth_value*price) as total_volumes_in_usd,
    sum(eth_value) as total_volumes_in_ETH,
    count(*) as total_Number_of_sales,
    count(distinct from_address) as total_Number_of_unique_Buyers
    from
    total
    WHERE day>=CURRENT_DATE-90 and STATUS='SUCCESS'

    -- where date>=CURRENT_DATE-90
    -- group by date
    --limit 5
    Run a query to Download Data