-- forked from Flipppppper / area chart bug @ https://flipsidecrypto.xyz/Flipppppper/q/collection-information-EJr9vP

-- forked from 2cfd1a35-ecb8-4569-88f4-20c2bff57250

top_collections as (

select

project_name as "Collection",

count(distinct tx_hash) as "Number of Sales",

sum(price_usd) as "Total Sales ($)"

from

ethereum.core.ez_nft_sales

where

block_timestamp::date >= '2022-10-19' -- blur launch

and project_name is not NULL

group by

project_name

order by

"Total Sales ($)" desc,

"Number of Sales" desc

limit

15

)

BLOCK_TIMESTAMP::date as date,

'cryptopunks' as collection,

platform_name,

count(distinct tx_hash) as tx_count,

avg(tx_count) OVER (

ORDER BY DATE ROWS BETWEEN 7 PRECEDING AND CURRENT ROW

) as "Rolling Avg 7d: tx_count",

sum(price) as total_sales,