mutual-following.sql

SELECT StrangerId, COUNT(MutualFollowing) AS MutualFollowingCount
FROM (
	SELECT
		CASE WHEN f.following_user = mf.friendId THEN f.user ELSE f.following_user END AS StrangerId,
		CASE WHEN f.user = mf.friendId THEN f.user ELSE f.following_user END AS MutualFollowing
	FROM (
		SELECT 
			CASE WHEN user = 1 THEN following_user 
			ELSE user 
		END AS friendId 
		FROM following 
		WHERE user = 1 OR following_user = 1
	) AS mf
	INNER JOIN following f
	ON (user != 1 AND following_user = mf.friendId) 
	OR (user = mf.friendId AND following_user != 1)
) AS totals
GROUP BY StrangerId
ORDER BY MutualFollowingCount DESC;