WITH unit_owners AS (

SELECT DISTINCT owner_address as unit_owner

FROM crosschain.olas.ez_unit_registrations

),

service_owners AS (

SELECT DISTINCT owner_address as service_owner

FROM crosschain.olas.ez_service_registrations

),

all_owners AS (

SELECT unit_owner as owner_address FROM unit_owners

UNION

SELECT service_owner FROM service_owners

),

owner_categories AS (

SELECT

ao.owner_address,

CASE

WHEN uo.unit_owner IS NOT NULL AND so.service_owner IS NOT NULL THEN 'Both Units & Services'

WHEN uo.unit_owner IS NOT NULL THEN 'Units Only'

ELSE 'Services Only'

END as owner_type

FROM all_owners ao

LEFT JOIN unit_owners uo ON ao.owner_address = uo.unit_owner

LEFT JOIN service_owners so ON ao.owner_address = so.service_owner

SELECT

owner_type,

COUNT(*) as number_of_owners,

ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage

FROM owner_categories

GROUP BY 1

ORDER BY 2 DESC;