select

service_id,

name,

count(distinct epoch) as epochs_active,

count(*) as total_days_active,

min(block_timestamp) as first_checkpoint,

max(block_timestamp) as last_checkpoint,

sum(reward) as total_rewards,

avg(reward) as avg_daily_reward

from crosschain.olas.ez_service_checkpoints

group by 1, 2

select

service_id,

name,

epochs_active,

total_days_active,

first_checkpoint,

last_checkpoint,

datediff('day', first_checkpoint, last_checkpoint) + 1 as total_possible_days,

(total_days_active::FLOAT / (DATEDIFF('day', first_checkpoint, last_checkpoint) + 1)) * 100 as consistency_percentage,

total_rewards,

avg_daily_reward,

total_rewards / NULLIF(epochs_active, 0) as avg_rewards_per_epoch

from service_metrics

order by total_days_active desc;