select

nft_collection

,split(nft_collection, '.') as collection

,collection[2] as NFT

,sum(price) as Total_Sales

,count(distinct tx_id) as Number_Trades

from flow.core.ez_nft_sales

where nft_collection ilike ''

group by 1, 2, 3

),

perc as (

select

date_trunc('day',block_timestamp) as date

,nft_collection

,percentile_cont(.1) within group (order by price asc) as Floor

,percentile_cont(1) within group (order by price asc) as Ceiling

,median(price) as Median_Price

,avg(price) as Average_Price

from flow.core.ez_nft_sales

where nft_collection ilike '' and block_timestamp > current_date

group by 1, 2

order by 1 asc

select

sales.nft as "NFT Collection"

,sales.total_sales as "Total Sales"

,sales.Number_Trades as "Amount of Trades"

,perc.Floor

,perc.Ceiling

,perc.Median_Price as "Median Price"

,perc.Average_price as "Average Price"

from sales

join perc on perc.nft_collection = sales.NFT_collection