SELECT

tx_from AS user_id,

COUNT(tx_id) AS tx_count,

SUM(gas_used * fee / 1e6) AS total_fees, -- Assuming fees are in micro-units

MAX(block_timestamp) AS last_transaction

FROM

axelar.core.fact_transactions

WHERE

block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP())

AND tx_succeeded = TRUE

GROUP BY

tx_from

),

daily_active_users AS (

SELECT

DATE(last_transaction) AS date,

COUNT(user_id) AS active_user_count,

SUM(tx_count) AS total_transactions,

SUM(total_fees) AS total_fees_paid

FROM

active_users

GROUP BY

DATE(last_transaction)

date,

active_user_count,

total_transactions,

total_fees_paid

daily_active_users

ORDER BY

date;