SELECT

COUNT(TX_FROM) AS new_users_count,

SUM(new_users_txn_count) AS new_users_txn_count

FROM (

SELECT

TX_FROM,

MIN(BLOCK_TIMESTAMP)::date AS min_date,

COUNT(tx_id) AS new_users_txn_count

FROM axelar.core.fact_transactions

GROUP BY TX_FROM

HAVING MIN(BLOCK_TIMESTAMP)::date >= (current_date - INTERVAL '6 months')

)

),

active_users AS (

SELECT

COUNT(tx_id) AS txn_count,

COUNT(DISTINCT tx_from) AS active_users_count,

SUM(fee) / 1e6 AS paid_fees

FROM axelar.core.fact_transactions

WHERE BLOCK_TIMESTAMP::date >= (current_date - INTERVAL '6 months')

AND BLOCK_TIMESTAMP::date < current_date

SELECT

new_users.new_users_count,

active_users.active_users_count,

active_users.txn_count,

active_users.paid_fees,

active_users.txn_count::float / active_users.active_users_count AS avg_txn_per_user,

active_users.paid_fees / active_users.txn_count AS avg_fees_per_txn,

new_users.new_users_txn_count

FROM new_users

JOIN active_users ON true;