elvisNFTX Q1.2 22/6/12 Volume Tser for TOP9
    Updated 2022-06-12
    /*
    Q1. Which 3 NFT collections have the most total sales volume in the last 60 days on NFTX? These are transactions where someone redeems their NFTX tokens for a specific NFT.

    Hint: You can find these sales in ez_nft_sales. Visualize your findings.
    */
    WITH NFTX_Proj_Vol AS (
    SELECT project_name, sum(price) as volume_nftx_token, sum(price_usd) AS volume_usd, sum(platform_fee_usd) AS platform_fees_usd, sum(creator_fee_usd) as creator_fees_usd,
    sum(total_fees_usd) AS total_fees_usd, sum(tx_fee_usd) AS Tx_fees_usd
    FROM ethereum.core.ez_nft_sales
    WHERE platform_name = 'nftx'
    AND BLOCK_TIMESTAMP > CURRENT_DATE-60
    AND event_type = 'redeem'
    GROUP BY 1
    ORDER BY 3 DESC
    ),
    TOP9_proj AS (
    SELECT project_name, rank() OVER (ORDER BY volume_usd DESC) AS vol_rank
    FROM NFTX_Proj_Vol
    ORDER BY volume_usd DESC
    LIMIT 9
    ),
    NFTX_TSer AS (
    SELECT date_trunc('day',block_timestamp) AS date, T.project_name, sum(price_usd) AS volume_usd
    FROM TOP9_proj AS T LEFT JOIN ethereum.core.ez_nft_sales AS S ON T.project_name = S.project_name
    WHERE platform_name = 'nftx'
    AND BLOCK_TIMESTAMP > CURRENT_DATE-60
    AND event_type = 'redeem'
    GROUP BY 1, 2
    ),
    NFTX_TSer2 AS (
    SELECT date, project_name, vol_rank, coalesce(volume_usd, 0) AS volume_usd
    FROM ((SELECT DISTINCT date FROM NFTX_TSer) CROSS JOIN TOP9_proj) NATURAL LEFT JOIN NFTX_TSer
    )

    SELECT *
    from NFTX_TSer2
    Run a query to Download Data