*

SELECT

date_trunc('day',block_timestamp) as day,

CASE WHEN block_timestamp BETWEEN '2022-11-19' AND '2022-11-26' THEN 'Thanksgiving Week' ELSE 'Other Days' end,

avg(price) as price,

sum(price) as volume,

count(DISTINCT buyer) as buyers,

count(DISTINCT SELLER) as sellers,

COUNT(DISTINCT tx_id) as sales

FROM flow.core.ez_nft_sales

LEFT outer JOIN flow.core.dim_allday_metadata

ON flow.core.dim_allday_metadata.nft_id = flow.core.ez_nft_sales.nft_id

WHERE flow.core.ez_nft_sales.nft_collection LIKE '%AllDay'

AND block_timestamp > CURRENT_DATE - 365

GROUP BY 1,2