queries.sql

-- Все взаимные контакты пользователя:
SELECT
	contact_user_id AS user_contact_id
FROM
	user_contacts uc
WHERE
	uc.user_id = 1 
	AND EXISTS (
		SELECT
			* 
		FROM
			users 
		WHERE
			uc.contact_user_id = users.id 
			AND EXISTS (
				SELECT * FROM user_contacts WHERE users.id = user_contacts.user_id AND contact_user_id = uc.user_id
			) 
	)

-- Взаимные контакты пользователя у которых больше пяти контактов
SELECT
	contact_user_id AS user_contact_id
FROM
	user_contacts uc
WHERE
	uc.user_id = 1 
	AND EXISTS (
		SELECT
			* 
		FROM
			users 
		WHERE
			uc.contact_user_id = users.id 
			AND EXISTS (
				SELECT * FROM user_contacts WHERE users.id = user_contacts.user_id AND contact_user_id = uc.user_id
			) 
			AND (SELECT COUNT(*) FROM user_contacts where user_id = users.id) > 5
	)

-- Пары контактов которые дружат друг с другом
SELECT
	user_id, contact_user_id
FROM
	user_contacts uc
WHERE
	EXISTS (
		SELECT
			* 
		FROM
			user_contacts
		WHERE
			user_contacts.contact_user_id = uc.user_id AND
			user_contacts.user_id = uc.contact_user_id
	)
ORDER BY user_id