The tricky part is a selecting only the more recent row and handling the case where the animal has always been alone or has has a roommate at some point.
* Currently alone, look for the most recent record
NumRoommates = 0 AND StartDate = MAX(StartDate)
* First record for this animal
MIN(StartDate)
* Last Roommate
MAX(EndDate) ... WHERE NumRoomates <> 0
putting it together, I think something like this (not tested)
SELECT
RoommateInfo.Id,
CASE WHEN LastRoommate IS NULL THEN FirstStart ELSE LastRoommate END AS AloneSince
TIMESTAMPDIFF(SQL_TSI_DAY, NOW(), CASE WHEN LastRoommate IS NULL THEN FirstStart ELSE LastRoommate END) AS DaysAlone
FROM RoommateInfo
INNER JOIN
(SELECT Id, MIN(StartDate) AS FirstStart, MAX(StartDate) AS LastStart From RoommateInfo GROUP BY Id) Dates ON RoommateInfo.Id = Dates.Id
LEFT OUTER JOIN
(SELECT Id, MAX(EndDate) AS LastRoommate FROM RoommateInfo WHERE NumRoommates <> 0 GROUP BY Id) LastR ON RoommateInfo.Id = LastR.Id
WHERE
RoommateInfo.NumRoommates = 0 AND StartDate = LastStartDate |