SELECT

origin_from_address AS player,

project_name,

DATE_TRUNC('week', block_timestamp) as week,

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'

--AND block_timestamp >= DATEADD('day', -30, CURRENT_DATE) -- Last 30 days by default

GROUP BY 1, 2, 3

),

categorized_players AS (

SELECT

player,

project_name,

week,

total_txs,

CASE

WHEN total_txs > 100 THEN 'Power Player'

ELSE 'Casual Gamer'

END AS player_category

FROM player_activity

SELECT

week,

player_category,

COUNT(DISTINCT player) AS num_players,

SUM(total_txs) AS total_txs,

AVG(total_txs) as avg_txs_per_player

FROM categorized_players where week<trunc(current_date,'week')

GROUP BY week, player_category

ORDER BY week DESC, total_txs DESC