FROM flow.core.ez_nft_sales a JOIN flow.core.dim_contract_labels b

ON nft_collection = event_contract

WHERE CONTRACT_NAME like 'DimensionX')

select

date_trunc(day,BLOCK_TIMESTAMP) as DATE,

count(distinct tx_id) as Sales_Cnt,

count(distinct buyer) as Buyers_Cnt,

count(distinct seller) as Sellers_Cnt,

count(distinct nft_collection) as Collections_Cnt,

count(distinct marketplace) as Marketplaces_Cnt,

count(distinct nft_id) as Tokens_Cnt,

Sellers_Cnt/Buyers_Cnt as Avg_sellers_per_buyers,

sum(price) as Total_flow_Vol,

avg(price) as Avg_flow_Vol,

sum(Sales_Cnt) over (order by date) as cum_Sales_Cnt,

sum(Buyers_Cnt) over (order by date) as cum_Buyers_Cnt,

sum(Sellers_Cnt) over (order by date) as cum_Sellers_Cnt,

sum(Total_flow_Vol) over (order by date) as cum_Total_flow_Vol,

avg(Sales_Cnt) over (order by date) as Avg_Sales_Cnt,

avg(Buyers_Cnt) over (order by date) as Avg_Buyers_Cnt,

Total_flow_Vol/Sales_Cnt as avg_vol_per_sale,

Sales_Cnt/Buyers_Cnt as avg_sales_per_buyer,

Sales_Cnt/Sellers_Cnt as avg_sales_per_seller

from tab1

where date > current_date - 30

group by 1