with
tab1 as ( SELECT
week,
count(DISTINCT tx_from) as users_per_week
FROM ( SELECT
date_trunc('week', day) as week,
tx_from,
count(*) as actvie_days
FROM ( SELECT
date_trunc('day', block_timestamp) as day,
TX_FROM
FROM
osmosis.core.fact_transactions
where
block_timestamp >= '2022-01-01'
GROUP BY 1,2
)
GROUP BY 1,2
)
WHERE
actvie_days >= 4 GROUP BY 1
)
SELECT avg(users_per_week),
max(users_per_week)
FROM
tab1