sinahosseinzadehUntitled Query
    Updated 2022-08-03
    WITH prices as (
    SELECT date_trunc('day', hour) as date, symbol, avg(price) as daily_avg_price
    FROM ethereum.core.fact_hourly_token_prices
    WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    GROUP BY 1, 2
    )
    , raw as (
    SELECT BLOCK_TIMESTAMP::date as date, tx_hash, BUYER_ADDRESS, CONCAT(NFT_ADDRESS, '-', TOKENID) as nft_id, PRICE, PRICE * daily_avg_price as price_usd
    FROM optimism.core.ez_nft_sales s INNER JOIN prices p ON date = p.date
    WHERE platform_name = 'quixotic' and EVENT_TYPE = 'sale' and CURRENCY_SYMBOL = 'ETH'
    )

    SELECT date, COUNT(DISTINCT tx_hash) as daily_sales, COUNT(DISTINCT buyer_address) as daily_buyers, COUNT(DISTINCT nft_id) as unique_nfts_sold,
    SUM(price) as daily_volume, SUM(price_usd) as daily_volume_usd,
    SUM(daily_sales) OVER (ORDER BY date ASC) as total_sales, SUM(daily_volume) OVER (ORDER BY date ASC) as total_volume,
    SUM(daily_volume_usd) OVER (ORDER BY date ASC) as total_volume_usd
    FROM raw
    GROUP BY 1
    ORDER BY 1 DESC
    Run a query to Download Data