select

timestamp::date as date,

avg (price_usd) as average_flow_price

from flow.core.fact_prices

where symbol like 'FLOW'

and source like 'coinmarketcap'

group by date

select

count(DISTINCT tx_id) as sales,

count(DISTINCT buyer ) as unique_buyers,

count(DISTINCT seller ) as unique_sellers,

sum(case

when currency = 'A.1654653399040a61.FlowToken' then price * fp.average_flow_price

else s.price

end) as total_usd_amount,

avg(case

when currency = 'A.1654653399040a61.FlowToken' then price * fp.average_flow_price

else s.price

end) as average_usd,

nft_collection

from flow.core.ez_nft_sales s join flow_price_tb fp on s.block_timestamp::date = fp.date

where nft_collection like '%{{project}}'

and tx_succeeded = 'TRUE'

group by nft_collection