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",