SELECT

service_id,

name,

COUNT(*) as total_checkpoints,

COUNT(DISTINCT epoch) as epochs_active,

DATEDIFF('day', MIN(block_timestamp), MAX(block_timestamp)) + 1 as service_lifespan,

(COUNT(*)::FLOAT / (DATEDIFF('day', MIN(block_timestamp), MAX(block_timestamp)) + 1)) * 100 as reliability_score

FROM crosschain.olas.ez_service_checkpoints

GROUP BY 1, 2

SELECT *

FROM service_stats

ORDER BY reliability_score DESC;