SELECT

origin_from_address AS player,

project_name,

COUNT(*) AS total_txs

FROM avalanche.core.fact_event_logs x

JOIN avalanche.core.dim_labels y

ON x.contract_address = y.address

WHERE label_type = 'games'

GROUP BY 1, 2

),

categorized_players AS (

SELECT

player,

project_name,

total_txs,

CASE

WHEN total_txs > 100 THEN 'Power Player'

ELSE 'Casual Gamer'

END AS player_category

FROM player_activity

SELECT

player_category,

COUNT(DISTINCT player) AS num_players,

SUM(total_txs) AS total_txs,

avg(total_txs) as avg_txs

FROM categorized_players

GROUP BY player_category

ORDER BY total_txs DESC