kaibladeOptimism NFT Sales Stats
    Updated 2022-09-15
    WITH raw_nft_sales AS
    (
    SELECT *
    FROM optimism.core.ez_nft_sales
    WHERE block_timestamp::date >= CURRENT_DATE - INTERVAL '2 month'
    ),

    eth_price AS
    (
    SELECT price,
    (CASE
    WHEN symbol='WETH' THEN 'ETH'
    END) AS symbol_eth
    FROM optimism.core.fact_hourly_token_prices
    WHERE symbol = 'WETH'
    ORDER BY hour DESC
    LIMIT 1
    ),

    finalized_data AS
    (SELECT sales.block_timestamp, sales.tx_hash, sales.buyer_address, sales.price * prices.price AS "Sale Price (USD)"
    FROM raw_nft_sales sales
    JOIN eth_price prices
    ON sales.currency_symbol = prices.symbol_eth),

    daily_sales AS(
    SELECT DATE_TRUNC('day', block_timestamp) AS days,
    COUNT(tx_hash) AS sales_count
    FROM finalized_data

    GROUP BY days
    ORDER BY days DESC
    ),

    daily_volume AS(
    SELECT DATE_TRUNC('day', block_timestamp) AS days,
    Run a query to Download Data