SELECT

from_address

, DATE_TRUNC('day', block_timestamp) AS activity_day

, DATE_TRUNC('quarter', block_timestamp) AS activity_quarter

FROM kaia.core.fact_transactions

),

dau AS (

SELECT

activity_day

, COUNT(DISTINCT from_address) AS daily_active_users

FROM user_activity

GROUP BY 1

SELECT

DATE_TRUNC('quarter', u.activity_day) AS "Quarter"

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

, COUNT(DISTINCT from_address) AS "Quarterly Users"

, ("Avg Daily Users(Quarter)" / "Quarterly Users") * 100 AS "AvgDU - QAU Ratio(%)"

FROM user_activity u

JOIN dau d ON d.activity_day = u.activity_day

GROUP BY 1

ORDER BY 1