SELECT address, label_type

FROM Kaia.core.dim_labels

WHERE label_type IN ('games', 'dapp')

),

dapps AS (

SELECT

DATE_TRUNC('month', block_timestamp) AS month,

tx_hash,

from_address,

to_address

FROM Kaia.core.fact_transactions

WHERE to_address IN (SELECT address FROM game_addresses)

SELECT

d.month,

COUNT(DISTINCT CASE WHEN g.label_type = 'dapp' THEN d.tx_hash END) AS dapp_total_tx,

COUNT(DISTINCT CASE WHEN g.label_type = 'dapp' THEN d.from_address END) AS dapp_total_users,

COUNT(DISTINCT CASE WHEN g.label_type = 'games' THEN d.tx_hash END) AS games_total_tx,

COUNT(DISTINCT CASE WHEN g.label_type = 'games' THEN d.from_address END) AS games_total_users,

SUM(COUNT(DISTINCT CASE WHEN g.label_type = 'dapp' THEN d.tx_hash END)) OVER (ORDER BY d.month) AS dapp_cumulative_tx,

SUM(COUNT(DISTINCT CASE WHEN g.label_type = 'dapp' THEN d.from_address END)) OVER (ORDER BY d.month) AS dapp_cumulative_users,

SUM(COUNT(DISTINCT CASE WHEN g.label_type = 'games' THEN d.tx_hash END)) OVER (ORDER BY d.month) AS games_cumulative_tx,

SUM(COUNT(DISTINCT CASE WHEN g.label_type = 'games' THEN d.from_address END)) OVER (ORDER BY d.month) AS games_cumulative_users

FROM dapps d

JOIN game_addresses g ON d.to_address = g.address

GROUP BY d.month

ORDER BY d.month DESC;