sales,

buyers,

sellers,

usd_volume

from (select date_trunc('day', block_timestamp) as day,

team,

count (distinct tx_id) as sales,

count (distinct buyer) as buyers,

count (distinct seller) as sellers,

count (distinct a.nft_id) as NFTs,

sum (case when currency = 'A.1654653399040a61.FlowToken' then price*usdprice else price end) as USD_Volume,

avg (case when currency = 'A.1654653399040a61.FlowToken' then price*usdprice else price end) as Average_USD_Volume

from flow.core.ez_nft_sales a join (select timestamp::date as day,

avg (price_usd) as usdprice

from flow.core.fact_prices

where symbol = 'FLOW'

and source = 'coinmarketcap'

group by 1) b on a.block_timestamp::Date = b.day

join flow.core.dim_allday_metadata c on a.nft_id = c.nft_id

where a.nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

and tx_succeeded = 'TRUE'

and block_timestamp>='2022-11-24'

and team in ('Buffalo Bills','Detroit Lions','New York Giants','Dallas Cowboys','New England Patriots','Minnesota Vikings')

group by 1,2)

where team != 'N/A'

group by 1, 2, 3, 4, 5, 6