with price as ( select RECORDED_HOUR::date as date, avg(close) as avg_price from flow.core.fact_hourly_prices where token = 'Flow' and RECORDED_HOUR::date >= '2023-01-01' group by 1) , market as ( select date(block_timestamp) as date,nft_collection, tx_id, nft_id, buyer, seller, case when currency in ('A.1654653399040a61.FlowToken','A.ead892083b3e2c6c.FlowUtilityToken') then price*avg_price else price end as volume from flow.core.ez_nft_sales a join price b on a.block_timestamp::date = b.date where block_timestamp::date >= '2023-01-01' and marketplace in ('A.4eb8a10cb9f87357.NFTStorefront','A.4eb8a10cb9f87357.NFTStorefrontV2') ) , buyers as ( select buyer, CONTRACT_NAME, count(DISTINCT(nft_collection)) as total_collection, count(DISTINCT(nft_id)) as nfts, count(DISTINCT(tx_id)) as sales, count(DISTINCT(seller)) as sellers, count(DISTINCT(buyer)) as buyers, sum(volume) as usd_volume, avg(volume) as avg_price, max(volume) as max_price, median(volume) as median_price from market a join flow.core.dim_contract_labels b on a.nft_collection = b.event_contract where contract_name != 'Basketballs' group by 1,2) , sellers as ( select seller, CONTRACT_NAME, count(DISTINCT(nft_collection)) as total_collection, count(DISTINCT(nft_id)) as nfts, count(DISTINCT(tx_id)) as sales, count(DISTINCT(seller)) as sellers, count(DISTINCT(buyer)) as buyers, sum(volume) as usd_volume, avg(volume) as avg_price, max(volume) as max_price, median(volume) as median_price from market a join flow.core.dim_contract_labels b on a.nft_collection = b.event_contract where contract_name != 'Basketballs' group by 1,2) select 'Buyers' as type, count(DISTINCT(buyer)) as user, case when usd_volume <= 10 then 'Below $10' when usd_volume <= 100 then '$10-$100' when usd_volume <= 1000 then '$100-$1K' when usd_volume <= 10000 then '$1K-$10K' when usd_Volume <= 100000 then '$10K-$100K' when usd_volume > 100000 then '+$100K' end as cat from buyers group by 1,3 UNION select 'Sellers' as type, count(DISTINCT(seller)) as user, case when usd_volume <= 10 then 'Below $10' when usd_volume <= 100 then '$10-$100' when usd_volume <= 1000 then '$100-$1K' when usd_volume <= 10000 then '$1K-$10K' when usd_Volume <= 100000 then '$10K-$100K' when usd_volume > 100000 then '+$100K' end as cat from sellers group by 1,3

Flowverse: Growth and Achievements in 2023