with flow_price as
(
SELECT
date_trunc('day', timestamp) as date,
avg(price_usd) as flow_price
from flow.core.fact_prices
where token = 'Flow'
and timestamp >= '2022-05-04'
and timestamp < '2022-05-17'
group by 1
)
SELECT
date_trunc('day', block_timestamp) as date,
nft_collection,
count(*) as no_of_txs,
sum(case when currency = 'A.1654653399040a61.FlowToken' then price*flow_price
else price end) as volume_usd
from flow.core.fact_nft_sales s
left join flow_price p
on date_trunc('day', block_timestamp) = date
where block_timestamp >= '2022-05-04'
and block_timestamp < '2022-05-17'
group by 1,2
order by 1, 4 desc