service_id,

name,

DATE_TRUNC('month', block_timestamp) as month,

COUNT(*) as active_days,

(COUNT(*)::FLOAT / DATE_PART('days', DATE_TRUNC('month', block_timestamp))) * 100 as monthly_uptime

FROM crosschain.olas.ez_service_checkpoints

GROUP BY 1, 2, 3

ORDER BY 1, 3;

SELECT

service_id,

name,

DATE_TRUNC('month', block_timestamp) AS month,

COUNT(DISTINCT DATE(block_timestamp)) AS active_days,

(COUNT(DISTINCT DATE(block_timestamp))::FLOAT / DATE_PART('days', DATE_TRUNC('month', block_timestamp))) * 100 AS monthly_uptime

FROM crosschain.olas.ez_service_checkpoints

GROUP BY 1, 2, 3

ORDER BY 1, 3;