service_id,

name,

count(*) as total_checkpoints_met,

avg(reward) as avg_reward,

sum(reward) as total_rewards,

max(epoch) as latest_epoch,

min(epoch) as first_epoch,

max(epoch) - min(epoch) + 1 as epoch_span,

LEAST(COUNT(*) * 100.0 / NULLIF(MAX(epoch) - MIN(epoch) + 1, 0), 100) AS checkpoint_success_rate_percentage

from crosschain.olas.ez_service_checkpoints

group by 1, 2

order by total_checkpoints_met desc;