LatornLatorn
Войти

SQL: SELF JOIN и поиск уникальных пар — Пары с несколькими общими проектами

Практическая задача по SQL на тему «SELF JOIN и поиск уникальных пар». В условии приведены схема таблиц, ограничения данных, имена столбцов результата и порядок сортировки. Напишите один SELECT или WITH-запрос для SQLite. Перед проверкой продумайте пустой результат, повторяющиеся строки и границы диапазонов, если они встречаются в условии. Проверяется результат запроса, поэтому допустима эквивалентная реализация. Сначала решите задачу самостоятельно; продолжение практики доступно в полном курсе продвинутого SQL.

Пары с несколькими общими проектами

Что уже дано

Уже заполнена project_members(project_id INTEGER NOT NULL,user_id INTEGER NOT NULL,PRIMARY KEY(project_id,user_id)). ID положительные INTEGER, каждая связь проекта и пользователя уникальна. Один пользователь может участвовать в нескольких проектах. До 1000 строк; NULL и повторных связей нет, пустая таблица допустима.

Что нужно сделать

Найдите пары пользователей, работавших вместе как минимум в двух различных проектах. Пары с единственным общим проектом не включайте. Для каждой пары user_id_1 < user_id_2 посчитайте число общих проектов shared_projects_count. Несколько общих проектов увеличивают счётчик, но не число строк этой пары.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 3 столбцов в указанном порядке и с этими именами: user_id_1, user_id_2, shared_projects_count. Все три столбца — INTEGER без NULL. Сортировка: shared_projects_count DESC, user_id_1 ASC, user_id_2 ASC. При отсутствии пар результат пустой. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO project_members VALUES (101,1),(101,2),(101,3),(102,1),(102,2),(103,3),(103,4);
Вывод
1|2|2
РешениеSQL · SQLite

Без регистрации. Гостевая проверка не сохраняет прогресс. Для сохранения перейдите к уроку.

Отправьте решение, чтобы увидеть результаты тестов.

Связанные задачи