SELECT

from_address

, DATE_TRUNC('week', block_timestamp) AS activity_week

, DATE_TRUNC('quarter', block_timestamp) AS activity_quarter

FROM kaia.core.fact_transactions

),

wau AS (

SELECT

activity_week

, COUNT(DISTINCT from_address) AS weekly_active_users

FROM user_activity

GROUP BY 1

),

qau AS (

SELECT

activity_quarter

, COUNT(DISTINCT from_address) AS quarterly_active_users

FROM user_activity

GROUP BY 1

SELECT

w.activity_week AS "Week"

, w.weekly_active_users AS "Weekly Users"

, q.quarterly_active_users AS "Quarterly Users"

, (w.weekly_active_users::float / q.quarterly_active_users) * 100 AS WAU_QAU

, ROUND(WAU_QAU, 2)||'%' AS "WAU - QAU Rate (%)"

FROM wau w

LEFT JOIN qau q

ON DATE_TRUNC('quarter', w.activity_week) = q.activity_quarter

ORDER BY 1