SELECT Date_trunc('day',timestamp) AS Dt , Avg(PRICE_USD) AS FLOW_PRICE

FROM flow.core.fact_prices

WHERE Symbol = 'FLOW'

group by 1 ),

nftsales_t1 AS (

SELECT Date_trunc('day',block_timestamp) AS DATE, RIGHT(NFT_COLLECTION,LEN(NFT_COLLECTION) - 19) AS Collection, PRICE , Currency

FROM flow.core.fact_nft_sales

WHERE TX_SUCCEEDED = 'TRUE'

),

nftsales_t2 AS (

SELECT Date, Collection, Price, currency, Flow_Price

FROM nftsales_t1

JOIN Flow_price

ON nftsales_t1.date = Flow_price.dt ) ,

nftsales_t3 AS (

SELECT Date, Collection,

CASE

WHEN currency='A.1654653399040a61.FlowToken' THEN Price*Flow_Price

ELSE Price

END AS PRICE_USD

FROM nftsales_t2 )

Select Collection AS top_collections, Sum(PRICE_USD)

FROM nftsales_t3

GROUP BY 1

ORDER BY 2 DESC