elvis22/7 NFT Top Shots: What makes a Top Shots moment valuable? (Part I) : TS Volume
    Updated 2022-11-08
    WITH
    hourly_prices_t1 AS (
    SELECT date_trunc('hour',timestamp) as block_hour, token_contract, avg(price_usd) as price_usd
    FROM flow.core.fact_prices
    WHERE token_contract = 'A.1654653399040a61.FlowToken'
    GROUP BY 1,2
    ),
    hourly_prices AS (
    SELECT *
    FROM (SELECT DISTINCT block_hour FROM hourly_prices_t1) T CROSS JOIN (
    SELECT key AS token_contract, value[0] AS price_usd
    FROM table(flatten(input => parse_json('{
    "A.b19436aae4d94622.FiatToken":[1],
    "A.3c5959b568896393.FUSD":[1],
    "A.ead892083b3e2c6c.DapperUtilityCoin":[1]
    }')
    , outer => true))
    ) UNION (SELECT * FROM hourly_prices_t1)
    ),
    names_marketplace as (
    SELECT key AS tech_name, value[0] AS marketplace
    FROM table(flatten(input => parse_json('{
    "A.c1e4f4f4c4257510.TopShotMarketV3":["TopShotMarketV3"],
    "A.4eb8a10cb9f87357.NFTStorefront":["NFTStorefront"],
    "A.c1e4f4f4c4257510.Market":["TopShotMarketV2"]
    }')
    , outer => true))
    ),
    TopShot_Volume AS (
    SELECT date_trunc('day', block_timestamp) as date, N.marketplace, currency, sum(price*price_usd) as daily_USD_volume
    FROM flow.core.fact_nft_sales S LEFT JOIN hourly_prices P ON (date_trunc('hour',S.block_timestamp) = P.block_hour AND S.currency = P.token_contract) LEFT JOIN names_marketplace N ON S.marketplace = N.tech_name
    WHERE NFT_collection IN (SELECT NFT_collection FROM flow.core.dim_topshot_metadata)
    AND tx_succeeded = TRUE
    GROUP BY 1,2,3
    )

    Run a query to Download Data