SELECT DISTINCT from_address

FROM Kaia.core.fact_transactions

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

),

dapp_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.block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '6 months'

),

game_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.block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '6 months'

SELECT

COUNT(DISTINCT k.from_address) AS total_kaia_users,

COUNT(DISTINCT d.from_address) AS dapp_users,

COUNT(DISTINCT g.from_address) AS game_users,

ROUND(100.0 * COUNT(DISTINCT d.from_address) / COUNT(DISTINCT k.from_address), 2) AS dapp_users_percentage,

ROUND(100.0 * COUNT(DISTINCT g.from_address) / COUNT(DISTINCT k.from_address), 2) AS game_users_percentage

FROM kaia_users k

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

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