WITH

User_tx_count_daily AS (

SELECT date_trunc('day', block_timestamp) as date, tx_from as user, count(DISTINCT tx_id) as daily_tx_count,

CASE

WHEN daily_tx_count > 0 THEN 1

ELSE 0

END as user_present

FROM osmosis.core.fact_transactions

WHERE tx_status = 'SUCCEEDED'

GROUP BY 1,2

),

daily_running_tx_count as (

SELECT date, user, sum(user_present) OVER (PARTITION BY user ORDER BY date rows between 3 preceding and 3 following) as weekly_activity_window

FROM User_tx_count_daily

),

daily_active_user_register as (

SELECT date, user

FROM daily_running_tx_count

WHERE weekly_activity_window > 3

),

daily_active_user_count as (

SELECT date, count(distinct user) as DAU_count

FROM daily_active_user_register

GROUP BY 1

SELECT *

FROM daily_active_user_count