SELECT

from_address

, DATE_TRUNC('day', block_timestamp) AS activity_day

, DATE_TRUNC('week', block_timestamp) AS activity_week

FROM swell.core.fact_transactions

WHERE from_address != '0xdeaddeaddeaddeaddeaddeaddeaddeaddead0001'

AND from_address != '0x339d413ccefd986b1b3647a9cfa9cbbe70a30749'

),

dau AS (

SELECT

activity_day

, COUNT(DISTINCT from_address) AS daily_active_users

FROM user_activity

GROUP BY 1

),

wau AS (

SELECT

activity_week

, COUNT(DISTINCT from_address) AS weekly_active_users

FROM user_activity

GROUP BY 1

SELECT

d.activity_day AS "Date"

, d.daily_active_users AS "Daily Users"

, w.weekly_active_users AS "Weekly Users"

, (d.daily_active_users::float / w.weekly_active_users) * 100 AS DAU_WAU

, ROUND(DAU_WAU, 2)||'%' AS "DAU - WAU Rate (%)"

FROM dau d

LEFT JOIN wau w

ON DATE_TRUNC('week', d.activity_day) = w.activity_week

ORDER BY 1