with flowpricet as (
select TIMESTAMP::date as date, avg(price_usd) as flowprice
from flow.core.fact_prices
where symbol ='FLOW' and source ='coinmarketcap'
group by 1)
select nft_collection, marketplace,
count (distinct tx_id) as Sales_Count,
sum (case when currency ='A.1654653399040a61.FlowToken' then price*flowprice else price end) as Total_Sale_Volume
from flow.core.ez_nft_sales t1
join flowpricet t2 on t1.block_timestamp::date = t2.date
where tx_succeeded = 'TRUE'
and buyer is not null
group by marketplace, nft_collection
order by nft_collection DESC