sports AS (

SELECT

CASE

WHEN address_name = 'collectorz club gen1' THEN 'Collectorz Club Gen1'

WHEN address_name = 'Collectorz Club: The Collectorz' THEN 'The Collectorz'

ELSE address_name

END AS collection,

block_timestamp::date AS date,

COUNT(DISTINCT tx_id) AS txn,

COUNT(DISTINCT purchaser) AS buyers,

COUNT(DISTINCT seller) AS sellers,

COUNT(DISTINCT mint) AS nfts,

SUM(sales_amount) AS volume,

AVG(sales_amount) AS price,

AVG(price) OVER(PARTITION BY collection ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as ma

FROM

solana.core.fact_nft_sales sales

JOIN

solana.core.dim_labels labels

ON

sales.mint = labels.address

WHERE

block_timestamp::date BETWEEN (CURRENT_DATE - {{range}}) AND (CURRENT_DATE - 1) AND succeeded = TRUE

AND address_name IN ('collectorz club gen1', 'Collectorz Club: The Collectorz', 'The Suites', 'Laidback Lions', 'Hockey Heroes')

GROUP BY

collection,

date

),

others AS (

SELECT

'Others' AS collection,

block_timestamp::date AS date,

COUNT(DISTINCT tx_id) AS txn,

COUNT(DISTINCT purchaser) AS buyers,

COUNT(DISTINCT seller) AS sellers,