Is this a imple Query?

[DaPi]

Select Street,
case 
when occ_houses = tot_houses then 'Occupied'
when occ_houses = 0 then 'Unoccupied'
else 'Partially Occupied'
end
from
(select Street,
 count(HouseNumber)            over (partition by Street) as tot_houses,
 sum(decode(Occupied,'Y',1,0)) over (partition by Street) as occ_houses)