select

service_id,

f.value::number as agent_id

from crosschain.olas.ez_service_registrations,

table(flatten(agent_ids)) f

),

unit_counts as (

select

ur.owner_address,

count(distinct ur.unit_id) as total_units,

count(distinct sa.service_id) as services_using_units

from crosschain.olas.ez_unit_registrations ur

left join service_agents sa

on ur.unit_id = sa.agent_id

group by 1

select

owner_address,

total_units,

services_using_units,

round(services_using_units/nullif(total_units,0),2) as unit_utilization_rate,

round(100.0 * services_using_units/nullif(total_units,0),2) as utilization_percentage,

case

when services_using_units = 0 then 'Unused'

when services_using_units < total_units then 'Partially Used'

else 'Fully Used'

end as usage_status

from unit_counts

where total_units > 0

order by total_units desc;