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

),

monthly_activity AS (

SELECT

origin_from_address AS player,

project_name,

DATE_TRUNC('month', block_timestamp) AS activity_month,

COUNT(*) AS monthly_txs

FROM avalanche.core.fact_event_logs x

JOIN avalanche.core.dim_labels y

ON x.contract_address = y.address

WHERE label_type = 'games' and origin_from_address in (select distinct player from categorized_players where player_category='Power Player')

GROUP BY 1, 2, 3

),

daily_activity AS (

SELECT