weekly_metrics AS (

SELECT

DATE_TRUNC('week', block_timestamp) AS week_start,

SUM(tx_fee) AS total_fees,

AVG(tx_fee) AS average_fee,

COUNT(DISTINCT from_address) AS active_users,

COUNT(DISTINCT tx_hash) AS transactions

FROM kaia.core.fact_transactions

WHERE tx_succeeded = 'TRUE'

GROUP BY DATE_TRUNC('week', block_timestamp)

),

new_user_metrics AS (

SELECT

DATE_TRUNC('week', first_tx_date) AS week_start,

COUNT(user_id) AS new_users

FROM (

SELECT

from_address AS user_id,

MIN(block_timestamp) AS first_tx_date

FROM kaia.core.fact_transactions

WHERE tx_succeeded = 'TRUE'

GROUP BY from_address

) AS initial_transactions

GROUP BY DATE_TRUNC('week', first_tx_date)

SELECT

CONCAT(DATE_TRUNC('week', wm.week_start)::text, ' 📅') AS "Week 📅",

'Kaia 🔗' AS "Blockchain 🔗",

CONCAT(wm.active_users::text, ' 👥') AS "Active Users 👥",

CONCAT(num.new_users::text, ' 🆕') AS "New Users 🆕",

CONCAT((SUM(num.new_users) OVER (ORDER BY wm.week_start) - num.new_users)::text, ' 🔄') AS "Previous Users 🔄",

CONCAT((SUM(num.new_users) OVER (ORDER BY wm.week_start))::text, ' 📈') AS "All-time Users 📈",