The only advice I have to give is to wrap it up in a stored procedure and pass a parameter to replace the "9". That way you can use it for any category you want to see.
The inner select does indeed return the most frequently occurring Category ID, but then you say that you only want to return results from the outer query if that Category ID is equal to 9.
