select

date(block_timestamp) as date,

player,

CASE

when block_timestamp >= '2022-09-09' and block_timestamp <= '2022-09-13' then 'Week 1'

when block_timestamp < '2022-09-09' then 'PreSeason'

when block_timestamp > '2022-09-13' then 'After Week 1'

end as Weeks,

sum(price) as "Sales Volume",

sum("Sales Volume") over(order by date) as "Total Sales Volume",

count(distinct buyer) as "# Buyers",

count(distinct seller) as "# Seller",

max(price) as "Maximum Price",

min(Price) as "Minimum Price",

median(price) as "Median Price",

PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Price) as "25%",

PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY Price) as "50%",

PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Price) as "75%",

avg(Price) as "Average",

count(distinct TX_ID) as "# Tx",

sum("# Tx") over(order by date) as "Total # Transactions"

from flow.core.ez_nft_sales s join flow.core.dim_allday_metadata a on s.NFT_ID = a.NFT_ID and s.NFT_COLLECTION = a.NFT_COLLECTION

where player != 'N/A'

and player ilike '{{PlayerName}}'--, 'Nick Chubb', 'Jonathan Taylor', 'D''Andre Swift', 'Leonard Fournette')

and TX_SUCCEEDED = 'TRUE'

and block_timestamp >= '2022-08-12'

group by 1,2,3

)

select

* from players_details