SELECT DISTINCT from_address, MIN(DATE_TRUNC('week', block_timestamp)) AS first_tx_week

FROM Kaia.core.fact_transactions

GROUP BY from_address

HAVING MIN(DATE_TRUNC('week', block_timestamp)) >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '6 months'

),

dapp_new_users AS (

SELECT DISTINCT t.from_address, DATE_TRUNC('week', t.block_timestamp) AS first_dapp_week

FROM Kaia.core.fact_transactions t

JOIN Kaia.core.dim_labels d ON t.to_address = d.address

WHERE d.label_type = 'dapp'

AND t.from_address IN (SELECT from_address FROM kaia_new_users)

),

game_new_users AS (

SELECT DISTINCT t.from_address, DATE_TRUNC('week', t.block_timestamp) AS first_game_week

FROM Kaia.core.fact_transactions t

JOIN Kaia.core.dim_labels d ON t.to_address = d.address

WHERE d.label_type = 'games'

AND t.from_address IN (SELECT from_address FROM kaia_new_users)

SELECT

k.first_tx_week AS "Week",

COUNT(DISTINCT k.from_address) AS "New Kaia Users",

COUNT(DISTINCT d.from_address) AS "New Dapp Users",

COUNT(DISTINCT g.from_address) AS "New Game Users"

FROM kaia_new_users k

LEFT JOIN dapp_new_users d ON k.from_address = d.from_address AND k.first_tx_week = d.first_dapp_week

LEFT JOIN game_new_users g ON k.from_address = g.from_address AND k.first_tx_week = g.first_game_week

GROUP BY k.first_tx_week

ORDER BY k.first_tx_week;