SELECT

'2024-03-01'::date as time

UNION ALL

SELECT

DATEADD(day, 1, time)

FROM

date_series

WHERE

time < date_trunc('day', current_timestamp())

),

all_users as (

select *

from

(

select block_timestamp as time, decoded_log['primaryAccount'] as account

from blast.core.fact_decoded_event_logs

where contract_address = lower('0x0b71cBBAd974B9DF8BDF6A83973B710AAa48e7ac')

and event_name = 'LogDepositCollateral'

)

),

unique_users as (

select date_trunc('day',time) as time, count(account) as new_users

from

(

select min(time) as time, account from all_users

group by account

)

group by 1

SELECT d.time, new_users,

AVG(new_users) OVER (ORDER BY d.time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days,

sum(new_users) over (order by d.time) as cumulative_users

FROM date_series d

LEFT JOIN unique_users u ON d.time = u.time