select TIMESTAMP::date as day, avg (price_usd) as flowprice

from flow.core.fact_prices

where symbol ='FLOW' and source ='coinmarketcap'

group by 1),

table1 as (

select date_trunc (week, block_timestamp::date) as week,

tx_id,

buyer,

seller,

t1.nft_id,

play_type,

player,

team,

t3.moment_stats_full:metadata:primaryPosition as Position,

case when t1.currency ='A.1654653399040a61.FlowToken' then t1.price * t2.flowprice

else t1.price end as nftprice

from flow.core.ez_nft_sales t1 join flowpricet t2 on t1.block_timestamp::date = t2.day

join flow.core.dim_topshot_metadata t3 on t1.nft_id = t3.nft_id

where t1.nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and block_timestamp > CURRENT_DATE - 91 and block_timestamp < CURRENT_DATE

and tx_succeeded = 'TRUE')

select player,

count (distinct tx_id) as TX_Count,

count (distinct buyer) as Buyers_Count,

sum (nftprice) as Total_Volume,

avg (nftprice) as AVG_Volume

from table1

group by 1

order by 4 desc

limit 9