SELECT

DATE_TRUNC('day', block_timestamp) AS Date,

COUNT(DISTINCT FROM_ADDRESS) AS USERS,

COUNT(DISTINCT TX_HASH) AS TXN

FROM kaia.core.fact_transactions

WHERE block_timestamp::DATE >= '2024-01-01'

GROUP BY 1

),

TOTAL AS (

SELECT

COUNT(DISTINCT FROM_ADDRESS) AS TOTAL_USERS,

COUNT(DISTINCT TX_HASH) AS TOTAL_TXN

FROM kaia.core.fact_transactions

WHERE block_timestamp::DATE >= '2024-01-01'

),

AVERAGE_STATS AS (

SELECT

AVG(USERS) AS DAILY_AVG_USERS

FROM USERS_2024

SELECT

(SELECT TOTAL_USERS FROM TOTAL) AS TOTAL_USERS,

(SELECT TOTAL_TXN FROM TOTAL) AS TOTAL_TXNS,

(SELECT DAILY_AVG_USERS FROM AVERAGE_STATS) AS DAILY_AVG_USERS,

TOTAL_TXNS / TOTAL_USERS AS AVG_TXNS_PER_USER