select *

from solana.core.dim_labels join solana.core.fact_nft_sales on mint = address

where

block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' and

label_type = 'nft' and

address_name = '{{collection_name}}'

),

info as (

select date_trunc('{{interval}}', block_timestamp) as date,

-- periodly

sum(sales_amount) as sales_volume,

avg(sales_amount) as avg_sales_price,

count(distinct tx_id) as sales_count,

count(distinct purchaser) as unique_buyer,

count(distinct seller) as unique_seller,

-- MA(7)

avg(sales_volume) over (order by date rows between 6 preceding and 0 following) as ma_7_sales_volume,

avg(avg_sales_price) over (order by date rows between 6 preceding and 0 following) as ma_7_avg_price,

avg(sales_count) over (order by date rows between 6 preceding and 0 following) as ma_7_sales_count,

avg(unique_buyer) over (order by date rows between 6 preceding and 0 following) as ma_7_unique_buyer,

avg(unique_seller) over (order by date rows between 6 preceding and 0 following) as ma_7_unique_seller,

-- Cumulative

sum(sales_volume) over (order by date) as cumulative_sales_volume,

sum(sales_count) over (order by date) as cumulative_sales_count,

sum(unique_buyer) over (order by date) as cumulative_unique_buyer,

sum(unique_seller) over (order by date) as cumulative_unique_seller,

-- total

(select sum(sales_amount) from base) as total_volume,

(select count(distinct tx_id) from base) as total_count,

(select count(distinct purchaser) from base) as total_buyer,

(select count(distinct seller) from base) as total_seller

from base

group by 1

),

avg_info as (