carsonbrownTop Volume by Month Launch
    Updated 2022-11-21
    WITH mints AS (
    WITH mint_txs AS (
    SELECT DATE_TRUNC('MONTH', mints.BLOCK_TIMESTAMP) AS launch_month, mints.BLOCK_TIMESTAMP, mints.NFT_ADDRESS AS addr, mints.PROJECT_NAME AS name, trans.ORIGIN_FUNCTION_SIGNATURE AS SIG
    FROM ethereum.core.ez_nft_mints mints
    JOIN ethereum.core.fact_transactions trans
    ON mints.tx_hash = trans.tx_hash
    ),

    grouped AS (
    SELECT addr, name, min(launch_month) AS launch_month
    FROM mint_txs
    GROUP BY 1,2
    )

    SELECT grouped.*, common.sig
    FROM grouped
    JOIN (
    SELECT addr, sig
    FROM (
    SELECT addr, sig, count(*) AS counts
    FROM mint_txs
    GROUP BY addr, sig
    )
    QUALIFY RANK() OVER (PARTITION BY addr ORDER BY counts DESC) = 1
    ) common
    ON grouped.addr = common.addr
    ),

    opensea_volume AS (
    SELECT NFT_ADDRESS AS addr, PROJECT_NAME AS name, count(*) AS lifetime_volume, sum(PRICE_USD) AS lifetime_trade_value
    FROM ethereum.core.ez_nft_sales
    WHERE PLATFORM_NAME = 'opensea'
    GROUP BY 1,2
    ),

    ranked AS (
    Run a query to Download Data