with flow_price as ( select date(timestamp) as date, avg(price_usd) as flow_price from flow.core.fact_prices where token = 'FLOW' group by 1) , allday as ( select date(block_timestamp) as date, tx_id, a.nft_id, buyer, seller, price, currency from flow.core.ez_nft_sales a join flow.core.dim_allday_metadata b on a.nft_id = b.nft_id where nflallday_id is not null) , topshot as ( select date(block_timestamp) as date, tx_id, a.nft_id, buyer, seller, price, currency from flow.core.ez_nft_sales a join flow.core.dim_topshot_metadata b on a.nft_id = b.nft_id where NBATOPSHOT_ID is not null) , golazos as ( select date(block_timestamp) as date, tx_id, a.nft_id, buyer, seller, price, currency from flow.core.ez_nft_sales a join flow.core.dim_moment_metadata b on a.nft_id = b.nft_id where b.NFT_COLLECTION = 'A.87ca73a41bb50ad5.Golazos') , allday_usd as ( select a.date, 'AllDay' as collection, tx_id, nft_id, buyer, seller, case when currency ilike '%flow%' then price*flow_price else price end as volume from allday a left outer join flow_price b on a.date = b.date) , topshot_usd as ( select a.date, 'Topshot' as collection, tx_id, nft_id, buyer, seller, case when currency ilike '%flow%' then price*flow_price else price end as volume from topshot a left outer join flow_price b on a.date = b.date) , golazos_usd as ( select a.date, 'Golazos' as collection, tx_id, nft_id, buyer, seller, case when currency ilike '%flow%' then price*flow_price else price end as volume from golazos a left outer join flow_price b on a.date = b.date) , all_day_sales as ( select date, collection,buyer, count(DISTINCT(tx_id)) as total_sales, count(DISTINCT(buyer)) as buyers, count(DISTINCT(nft_id)) as total_nft, sum(volume) as total_volume, avg(volume) as avg_volume, max(volume) as max_volume, median(volume) as median, min(volume) as min_volume, rank() over (order by date asc) as n_days from allday_usd group by 1,2,3 qualify n_days <= 8) , topshot_sales as ( select date, collection,buyer, count(DISTINCT(tx_id)) as total_sales, count(DISTINCT(buyer)) as buyers, count(DISTINCT(nft_id)) as total_nft, sum(volume) as total_volume, avg(volume) as avg_volume, max(volume) as max_volume, median(volume) as median, min(volume) as min_volume, rank() over (order by date asc) as n_days from topshot_usd group by 1,2,3 qualify n_days <= 8) , golazos_sales as ( select date, collection,buyer, count(DISTINCT(tx_id)) as total_sales, count(DISTINCT(buyer)) as buyers, count(DISTINCT(nft_id)) as total_nft, sum(volume) as total_volume, avg(volume) as avg_volume, max(volume) as max_volume, median(volume) as median, min(volume) as min_volume, rank() over (order by date asc) as n_days from golazos_usd group by 1,2,3 qualify n_days <= 8) , fifa_sales as ( select date(block_timestamp) as date, 'Fifa Collect' as collection, purchaser as buyer, count(DISTINCT(TX_GROUP_ID)) as total_sales, count(DISTINCT(purchaser)) as buyers, count(DISTINCT(NFT_ASSET_ID)) as total_nft, sum(TOTAL_SALES_AMOUNT_USD) as total_volume, avg(TOTAL_SALES_AMOUNT_USD) as avg_volume, max(TOTAL_SALES_AMOUNT_USD) as max_volume, median(TOTAL_SALES_AMOUNT_USD) as median, min(TOTAL_SALES_AMOUNT_USD) as min_volume, rank() over (order by date asc) as n_days from algorand.nft.ez_nft_sales_fifa where sale_type = 'secondary' group by 1,2,3 qualify n_days <= 8 ) , fifa_buyer as ( select collection, buyer, sum(total_sales) as total_tx, sum(buyers) as total_buyers, sum(total_volume) as volume, avg(avg_volume) as avg_volumes from fifa_sales group by 1,2 order by 5 desc ) , golazos_buyer as ( select collection, buyer,sum(total_sales) as total_tx, sum(buyers) as total_buyers, sum(total_volume) as volume, avg(avg_volume) as avg_volumes from golazos_sales group by 1,2 order by 5 desc ) , topshot_buyer as ( select collection, buyer, sum(total_sales) as total_tx, sum(buyers) as total_buyers, sum(total_volume) as volume, avg(avg_volume) as avg_volumes from topshot_sales group by 1,2 order by 5 desc ) , allday_buyer as ( select collection,buyer, sum(total_sales) as total_tx, sum(buyers) as total_buyers, sum(total_volume) as volume, avg(avg_volume) as avg_volumes from all_day_sales group by 1,2 order by 5 desc ) , final as (select * from allday_buyer UNION select * from topshot_buyer UNION select * from golazos_buyer UNION select * from fifa_buyer) select collection, count(DISTINCT(buyer)) as user, case when volume <= 1 then 'Up to 1$' when volume <= 5 then '2-5$' when volume <= 10 then '6-10$' when volume <= 25 then '11-25$' when volume <= 50 then '26-50$' when volume <= 100 then '51-100$' when volume <= 250 then '101-250$' when volume <= 500 then '251-500$' when volume <= 1000 then '501-1000$' when volume > 1000 then '+1000$' end as type from final group by 1,3

LaLiga Golazos