-- perhaps a chart showing the top five projects by volume each week?

WITH

sales AS (

SELECT

block_timestamp,

block_height,

n.nft_id,

n.nft_collection,

case when nft_collection ='A.4eded0de73020ca5.CricketMoments' then '🏏Fancraze'

when nft_collection = 'A.0b2a3299cc857e29.TopShot' then '🏀NBA TopShot'

when nft_collection='A.329feb3ab062d289.UFC_NFT' then '🥊UFC Strike'

when nft_collection='A.e4cf4bdc1751c65d.AllDay' then '🏈NFL AllDay'

when nft_collection= 'A.87ca73a41bb50ad5.Golazos' then '⚽La Liga Golazos'

end as collection,

n.price,n.seller,n.buyer,

marketplace,

n.tx_id,currency

FROM flow.core.ez_nft_sales n

WHERE nft_collection in ('A.4eded0de73020ca5.CricketMoments','A.0b2a3299cc857e29.TopShot','A.329feb3ab062d289.UFC_NFT','A.e4cf4bdc1751c65d.AllDay','A.87ca73a41bb50ad5.Golazos')

),

prices as (

select

trunc(recorded_hour,'hour') as dates,

avg(close) as price_usd

from flow.core.fact_hourly_prices where token='Flow'

group by 1

),

flow_final_data as (

SELECT

trunc(block_timestamp,'week') AS date,

--collection,

count(distinct tx_id) AS sales,

--sum(transactions) over (partition by collection order by date) as cum_transactions,

sum(case when currency='A.1654653399040a61.FlowToken' then price*price_usd else price end) as volume_of_sales,