SocioCryptotimezone user segmentation copy
    Updated 2023-05-02
    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,
    hours,
    avg(n_purchase)
    FROM nft_buyers
    GROUP BY 1,2


    Run a query to Download Data