SELECT block_timestamp ,

buyer

FROM flow.core.ez_nft_sales

WHERE nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

AND tx_succeeded = 'TRUE'

AND block_timestamp::date >= '2022-11-24' -- since Thanksgiving to now

),

non_Purchase_allday AS (

SELECT a.buyer,

COUNT(DISTINCT tx_id) AS buy_count

FROM flow.core.ez_nft_sales a

LEFT JOIN allday b

ON a.buyer = b.buyer

WHERE a.buyer IN (SELECT buyer FROM allday )

AND tx_succeeded = 'TRUE'

AND nft_collection != 'A.e4cf4bdc1751c65d.AllDay'

GROUP BY 1

SELECT CASE WHEN buy_count = 1 THEN 'Purchase One time'

WHEN buy_count between 1 and 10 THEN 'Purchase 1~10 time '

WHEN buy_count between 10 and 100 THEN 'Purchase 10~100 time '

WHEN buy_count between 100 and 200 THEN 'Purchase 100~200 time '

WHEN buy_count between 200 and 500 THEN 'Purchase 200~500 time '

ELSE 'More Then 500 time' END AS Purchase_frequency,

COUNT(DISTINCT buyer ) AS buyers

FROM non_Purchase_allday

GROUP BY 1