WITH txs AS (
SELECT
block_timestamp::date AS date
, from_address AS user
, count(DISTINCT tx_hash) AS daily_txs
, dateadd(day, -1 * dense_rank() over (partition by user order by date), date) AS seq_start
FROM ethereum.core.fact_transactions
WHERE block_timestamp > current_date - 30
AND from_address IN ('0xae2fc483527b8ef99eb5d9b44875f005ba1fae13'
, '0xac4210424cc85e0a0cc4dcb65363dd3a1bb861f9'
, '0x6d97e9b4868e930f6006e8e8387acd5d63153204')
GROUP BY 1, 2
),
streaks AS (
SELECT
user
, min(date) as start_date
, max(date) as end_date
, datediff(day, start_date, end_date) + 1 AS seq_days
from txs
GROUP BY user, seq_start
)
SELECT
count(DISTINCT user) AS count_of_users_active_for_more_than_5_consecutive_days
FROM streaks
WHERE seq_days >= 5