avg as (

SELECT

trunc(block_timestamp,'day') as days,

address_name,

count(distinct purchaser) as buyers,

count(distinct seller) as sellers,

sum(sales_amount) as volume,

count(distinct tx_id) as sales

from solana.core.fact_nft_sales x

join solana.core.dim_labels y on x.mint=y.address

where (address_name in ('Collectorz Club: The Collectorz','The Suites','Laidback Lions')

or y.address_name like '%Sports%' or y.address_name like '%sports%' or y.address_name like '%football%' or y.address_name like '%runner%' or y.address_name like '%basket%' or y.address_name like '%basketball%' or y.address_name like '%tennis%'

or y.address_name like '%Football%' or y.address_name like '%Runner%' or y.address_name like '%Basket%' or y.address_name like '%Basketball%' or y.address_name like '%Tennis%' or y.address_name like '%stepn%') and address_name not like '%Passports%'

and SUCCEEDED = 'TRUE' and sales_amount > 0

group by 1,2

)

select

trunc(block_timestamp,'{{granularity}}') as date,

case when y.address_name like '%solkitty%' then 'Solkitty NFT sports'

else y.address_name end as collection,

count(distinct tx_id) as "Number of sales",

avg(sales) as "Average sales per day",

count(distinct purchaser) as "Number of buyers",

count(distinct seller) as "Number of sellers",

avg(buyers) as "Average buyers per day",

avg(sellers) as "Average sellers per day",

sum(sales_amount) as "Volume in SOL",

avg(volume) as "Average volume per day",

avg(sales_amount) as "Average volume",

avg("Volume in SOL") over (partition by collection order by date rows between 6 preceding and current row) as "7MA",

avg("Volume in SOL") over (partition by collection order by date rows between 29 preceding and current row) as "30MA",

avg("Volume in SOL") over (partition by collection order by date rows between 59 preceding and current row) as "60MA"

from solana.core.fact_nft_sales x

join solana.core.dim_labels y on x.mint=y.address

join avg on y.address_name=avg.address_name and trunc(x.block_timestamp,'{{granularity}}') = trunc(avg.days,'{{granularity}}')