date_trunc('day', timestamp) as date,

avg(price_usd) as flow_price

from flow.core.fact_prices

where token = 'Flow'

group by 1

-- order by timestamp desc

)

date_trunc('day', block_timestamp) as date,

nft_collection,

currency,

sum(price) as volume,

case when currency = 'A.1654653399040a61.FlowToken' then sum(price*flow_price)

else sum(price) end as volume_usd,

count(*) as no_of_txs,

from flow.core.fact_nft_sales s

left join flow_price p

on date_trunc('day', block_timestamp) = date

where block_timestamp >= '2022-05-09'

group by 1,2,3

order by 1,5 DESC