ThatGuyWhat makes a Top Shots moment valuable? (Part I) 3. daily
    Updated 2022-07-24
    WITH
    prices as (
    SELECT
    TIMESTAMP::date as date,
    TOKEN_CONTRACT,
    avg(PRICE_USD) AS price
    FROM
    flow.core.fact_prices
    WHERE
    symbol = 'FLOW'
    GROUP BY 1,2
    ORDER BY 1 DESC
    )
    , raw as (
    SELECT
    f.BLOCK_TIMESTAMP::date as date,
    f.TX_ID,
    f.NFT_ID,
    f.BUYER,
    f.SELLER,
    CASE
    WHEN f.currency = 'A.1654653399040a61.FlowToken' THEN f.price * p.price
    ELSE f.price * 1
    END as price_usd,
    m.PLAY_TYPE,
    m.MOMENT_DATE
    FROM flow.core.fact_nft_sales f
    LEFT JOIN flow.core.dim_topshot_metadata m ON m.NFT_ID = f.NFT_ID
    LEFT JOIN prices p ON p.date = date AND p.TOKEN_CONTRACT = f.CURRENCY
    WHERE f.NFT_COLLECTION = 'A.0b2a3299cc857e29.TopShot' AND m.PLAY_TYPE IS NOT NULL
    )

    SELECT
    date,
    play_type as "Play Type",