WITH dau_list as (
SELECT
tx_from as user,
date_trunc('Week', block_timestamp) as week,
count(DISTINCT block_timestamp::date) as active_day
FROM osmosis.core.fact_transactions
WHERE block_timestamp > '2022-01-01'
GROUP BY 1,2
HAVING active_day > 3
)
SELECT
week,
count(DISTINCT user) as "Weekly active users"
FROM dau_list
GROUP BY 1