SELECT DISTINCT from_address, MIN(DATE_TRUNC('month', block_timestamp)) AS first_tx_month

FROM Kaia.core.fact_transactions

GROUP BY from_address

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

),

dapp_new_users AS (

SELECT DISTINCT t.from_address

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

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

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

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

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

ROUND(100.0 * COUNT(DISTINCT d.from_address) / COUNT(DISTINCT k.from_address), 2) AS "Percentage of New Users Using Dapps (%)",

ROUND(100.0 * COUNT(DISTINCT g.from_address) / COUNT(DISTINCT k.from_address), 2) AS "Percentage of New Users Using Games (%)"

FROM kaia_new_users k

LEFT JOIN dapp_new_users d ON k.from_address = d.from_address

LEFT JOIN game_new_users g ON k.from_address = g.from_address;