-- forked from timezone user segmentation copy @ https://flipsidecrypto.xyz/edit/queries/af1a90b1-50aa-45f5-a59f-8ec4b9b4dd6d

WITH nft_buyers as(

SELECT buyer,

count(DISTINCT tx_id) n_purchase,

count(DISTINCT CASE when nft_collection = 'A.0b2a3299cc857e29.TopShot' then tx_id end) as NBA_TOP_SHOT,

count(DISTINCT CASE when nft_collection = 'A.e4cf4bdc1751c65d.AllDay' then tx_id end) as NFL_ALL_DAY,

count(DISTINCT CASE when nft_collection = 'A.4eded0de73020ca5.CricketMoments' then tx_id end) as Cricket_FanCraze,

count(DISTINCT CASE when nft_collection = 'A.87ca73a41bb50ad5.Golazos' then tx_id end) as La_Liga_Golazos,

CASE when NFL_ALL_DAY =0 and Cricket_FanCraze =0 and La_Liga_Golazos = 0 then 'NBA'

when NBA_TOP_SHOT=0 and Cricket_FanCraze =0 and La_Liga_Golazos = 0 then 'NFL'

when NBA_TOP_SHOT=0 and NFL_ALL_DAY =0 and La_Liga_Golazos = 0 then 'Cricket'

when NBA_TOP_SHOT=0 and NFL_ALL_DAY =0 and Cricket_FanCraze =0 then 'La Liga'

else 'Diverse Sport Users' end as catagory,

avg(hour(block_timestamp)) as hours

FROM flow.core.ez_nft_sales

WHERE block_timestamp::date >= current_date - 90

AND nft_collection in ('A.0b2a3299cc857e29.TopShot', 'A.e4cf4bdc1751c65d.AllDay',

'A.4eded0de73020ca5.CricketMoments', 'A.87ca73a41bb50ad5.Golazos')

GROUP BY 1

SELECT catagory,

count(DISTINCT buyer)

FROM nft_buyers

GROUP BY 1