SELECT

e.signers[0] as user,

e.program_id,

date_trunc('hour', e.block_timestamp) as activity_hour,

count(*) as hourly_actions,

count(distinct date_trunc('day', e.block_timestamp)) as active_days

FROM eclipse.core.fact_events e

WHERE e.succeeded = TRUE

AND e.block_timestamp >= dateadd('day', -30, current_timestamp())

AND e.program_id IN (

'5hEa5j38yNJRM9vQA44Q6gXVj4Db8y3mWxkDtQeofKKs',

'RariUNM3vz1rwxPg8UJyRAN7rSKXxgd2ncS2ddCa4ZE',

'Rarim7DMoD45z1o25QWPsWvTdFSSEdxaxriwWZLLTic'

)

GROUP BY 1, 2, 3

SELECT

extract(hour from activity_hour) as hour_of_day,

program_id,

count(distinct user) as unique_users,

avg(hourly_actions) as avg_actions,

avg(active_days) as avg_active_days,

count(distinct case when hourly_actions > 5 then user end) * 100.0 /

count(distinct user) as high_activity_users_percent

FROM user_time_patterns

GROUP BY 1, 2

ORDER BY hour_of_day, unique_users DESC;

WITH base_events AS (

-- ابتدا داده‌های پایه را با فیلترهای اصلی می‌گیریم

SELECT