WITH eviction_metrics AS (

SELECT

DATE_TRUNC('day', block_timestamp) as date,

COUNT(*) as daily_evictions,

COUNT(DISTINCT service_id) as unique_services_evicted,

COUNT(DISTINCT owner_address) as unique_owners_affected,

AVG(service_inactivity) as avg_inactivity_period,

MAX(service_inactivity) as max_inactivity_period

FROM crosschain.olas.ez_service_evictions

GROUP BY 1

),

running_totals AS (

SELECT

date,

daily_evictions,

unique_services_evicted,

unique_owners_affected,

avg_inactivity_period,

max_inactivity_period,

SUM(daily_evictions) OVER (ORDER BY date) as cumulative_evictions,

SUM(unique_services_evicted) OVER (ORDER BY date) as cumulative_unique_services

FROM eviction_metrics

SELECT

date,

daily_evictions,

unique_services_evicted,

unique_owners_affected,

avg_inactivity_period,

max_inactivity_period,

cumulative_evictions,

cumulative_unique_services

FROM running_totals

ORDER BY date DESC