SELECT

from_address

, DATE_TRUNC('day', block_timestamp) AS activity_day

, DATE_TRUNC('month', block_timestamp) AS activity_month

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

SELECT

DATE_TRUNC('month', u.activity_day) AS "Month"

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

, COUNT(DISTINCT from_address) AS "Monthly Users"

, ("Avg Daily Users(Month)" / "Monthly Users") * 100 AS "AvgDU - MAU Ratio(%)"

FROM user_activity u

JOIN dau d ON d.activity_day = u.activity_day

GROUP BY 1

ORDER BY 1