-- Get eviction data

SELECT

se.service_id,

se.block_timestamp as eviction_timestamp,

se.service_inactivity,

-- Look up donations before eviction

(

SELECT COUNT(*)

FROM crosschain.olas.ez_service_donations sd

WHERE sd.service_id = se.service_id

AND sd.block_timestamp < se.block_timestamp

) as donations_before_eviction,

-- Look up checkpoints before eviction

(

SELECT COUNT(*)

FROM crosschain.olas.ez_service_checkpoints sc

WHERE sc.service_id = se.service_id

AND sc.block_timestamp < se.block_timestamp

) as checkpoints_before_eviction

FROM crosschain.olas.ez_service_evictions se

SELECT

DATE_TRUNC('month', eviction_timestamp) as month,

COUNT(DISTINCT service_id) as services_evicted,

AVG(service_inactivity) as avg_inactivity,

AVG(donations_before_eviction) as avg_donations_before_eviction,

AVG(checkpoints_before_eviction) as avg_checkpoints_before_eviction,

CORR(service_inactivity, donations_before_eviction) as inactivity_donation_correlation,

CORR(service_inactivity, checkpoints_before_eviction) as inactivity_checkpoint_correlation

FROM eviction_impact

GROUP BY 1

ORDER BY 1;