Query editor
WITH txs AS (SELECT block_time, block_date, "from" AS wallet, CAST(value AS DOUBLE) / 1e18 AS value_ethFROM ink.transactionsWHERE success = TRUE),first_activity AS (SELECT wallet, MIN(block_date) AS first_dateFROM txsGROUP BY 1),active_30d AS (SELECT DISTINCT walletFROM txsWHERE block_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY),new_users_30d AS (SELECT COUNT(*) AS cntFROM first_activityWHERE first_date >= CURRENT_DATE - INTERVAL '30' DAY),returning_users_30d AS (SELECT COUNT(*) AS cntFROM active_30d a JOIN first_activity f ON a.wallet = f.walletWHERE f.first_date < CURRENT_DATE - INTERVAL '30' DAY)SELECT (SELECT COUNT(DISTINCT wallet) FROM txs) AS total_unique_users,(SELECT COUNT(DISTINCT wallet)FROM txsWHERE block_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY) AS active_users_30d,(SELECT cnt FROM new_users_30d) AS new_users_30d,(SELECT cnt FROM returning_users_30d) AS returning_users_30d,(SELECT COUNT(*) FROM txs) AS total_transactions,(SELECT SUM(value_eth) FROM txs) AS transaction_volume_eth