BLOCK_TIMESTAMP::date as day,

case

when LABEL ilike '%collectorz club%' then 'Collectorz Club'

when LABEL ilike '%Suites%' then 'Suites'

when LABEL ilike '%laidback lions%' then 'Laidback Lions'

else null end as Sport_NFTs,

count(TX_ID) as sale_no,

count(distinct PURCHASER) as buyer_no,

sum(SALES_AMOUNT) as volume,

avg(SALES_AMOUNT) as average_volume,

sale_no/buyer_no as average_buyer,

avg(average_volume) OVER (ORDER BY day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,

sum(buyer_no) over (partition by Sport_NFTs order by day) as cum_buyers,

sum(sale_no) over (partition by Sport_NFTs order by day) as cum_sale,

sum(volume) over (partition by Sport_NFTs order by day) as cum_volume

from solana.core.fact_nft_sales s left outer join solana.core.dim_labels b on s.mint=b.address

where SUCCEEDED='TRUE'

group by 1,2 having Sport_NFTs is not null