SELECT

from_address

, tx_hash

, DATE_TRUNC('day', block_timestamp) AS activity_day

, DATE_TRUNC('week', block_timestamp) AS activity_week

FROM kaia.core.fact_transactions

WHERE block_timestamp >= '2024-08-29'

),

dau AS (

SELECT

activity_day

, COUNT(DISTINCT from_address) AS daily_active_users

FROM user_activity

GROUP BY 1

SELECT

DATE_TRUNC('week', u.activity_day) AS "Week"

, AVG(daily_active_users) AS "Avg Daily Users(Week)"

, COUNT(DISTINCT from_address) AS "Weekly Users"

, COUNT(tx_hash) AS "Weekly Transactions"

, ("Avg Daily Users(Week)" / "Weekly Users") * 100 AS "AvgDU - WAU Ratio(%)"

FROM user_activity u

JOIN dau d ON d.activity_day = u.activity_day

GROUP BY 1

ORDER BY 1