8 задач по SQL с решениями: JOIN, GROUP BY и оконные функции

Чтобы решить задачу по SQL, сначала определите, что означает одна строка результата и какие записи должны в неё попасть. Затем напишите запрос и сравните результат с ожидаемым. В этой подборке восемь упражнений используют одну базу: можно проследить, как JOIN, группировка и оконная функция меняют данные.

На Mentoring.digital подготовили учебные таблицы студентов и попыток выполнения работ. Данные вымышленные. Все запросы и показанные результаты проверены в SQLite; это не интерактивный тренажёр. При переносе в другую СУБД проверьте её синтаксис и типы.

Подготовьте таблицы и сначала сформулируйте ответ

Создайте отдельную пустую учебную базу SQLite и выполните блок ниже один раз. Не запускайте создание учебных таблиц в рабочей базе. Для оконной функции понадобится SQLite 3.25 или новее.

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE attempts (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  subject TEXT NOT NULL,
  score INTEGER CHECK (score BETWEEN 0 AND 100)
);
INSERT INTO students VALUES
  (1, 'Анна'), (2, 'Борис'), (3, 'Вера'),
  (4, 'Глеб'), (5, 'Дина');
INSERT INTO attempts VALUES
  (1, 1, 'SQL', 70), (2, 1, 'SQL', 90),
  (3, 1, 'Python', 80), (4, 2, 'SQL', 90),
  (5, 2, 'Python', NULL), (6, 3, 'SQL', 50),
  (7, 3, 'Python', 80), (8, 4, 'SQL', NULL);

У Анны несколько работ, у Бориса и Глеба есть непроверенные попытки, у Дины попыток нет. NULL в поле score означает «оценка ещё не выставлена», а не нулевой балл. Именно эти различия помогают обнаруживать ошибки запросов.

Перед просмотром каждого решения запишите ожидаемые строки самостоятельно. Для повторения базового синтаксиса можно построить план обучения SQL с наставником, а здесь сосредоточиться на практике и проверке результата.

Выборка, соединение и отсутствие связанных записей

1. Выбрать проверенные работы по SQL с результатом от 80

SELECT id, student_id, score
FROM attempts
WHERE subject = 'SQL' AND score >= 80
ORDER BY id;

Фильтр исключает и слабый результат, и непроверенные работы. NULL означает отсутствие оценки; сравнение с числом не делает такую строку подходящей.

2. Показать имена авторов оценённых работ

SELECT a.id, s.name, a.subject, a.score
FROM attempts AS a
JOIN students AS s ON s.id = a.student_id
WHERE a.score IS NOT NULL
ORDER BY a.id;

Соединение идёт по ключу студента. Несколько работ Анны дают несколько строк, это ожидаемая кратность, а не дубликат, который надо удалять через DISTINCT.

3. Найти студентов без единой попытки

SELECT s.id, s.name
FROM students AS s
WHERE NOT EXISTS (
  SELECT 1 FROM attempts AS a
  WHERE a.student_id = s.id
)
ORDER BY s.id;

Глеб не попадает в результат: попытка у него есть, хотя оценка пока отсутствует. Отсутствие строки и отсутствие значения внутри строки являются разными условиями.

Группировка и подсчёты без потери строк

4. Посчитать все попытки и только оценённые

SELECT s.name,
       COUNT(a.id) AS attempts_count,
       COUNT(a.score) AS graded_count
FROM students AS s
LEFT JOIN attempts AS a ON a.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

LEFT JOIN сохраняет Дину. COUNT(a.id) считает реальные попытки, а COUNT(a.score) только непустые оценки. COUNT(*) в этой группе дал бы Дине единицу из-за строки, сформированной внешним соединением.

5. Вычислить среднюю оценку каждого студента

SELECT s.name, ROUND(AVG(a.score), 1) AS average_score
FROM students AS s
LEFT JOIN attempts AS a ON a.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

AVG не включает NULL. У Глеба и Дины нет оценённых работ, поэтому среднее неизвестно, а не равно нулю. Подстановка нуля перед AVG изменила бы смысл показателя.

6. Оставить студентов минимум с двумя оценёнными работами

SELECT s.name, COUNT(a.score) AS graded_count
FROM students AS s
JOIN attempts AS a ON a.student_id = s.id
GROUP BY s.id, s.name
HAVING COUNT(a.score) >= 2
ORDER BY s.id;

WHERE фильтрует строки до группировки, а HAVING отбирает уже полученные группы. Борис исключён: две попытки не означают две выставленные оценки.

Оконная функция и доля успешных работ

7. Ранжировать лучшие результаты студентов по каждому предмету

WITH best AS (
  SELECT student_id, subject, MAX(score) AS score
  FROM attempts
  WHERE score IS NOT NULL
  GROUP BY student_id, subject
)
SELECT subject, student_id, score,
       DENSE_RANK() OVER (
         PARTITION BY subject ORDER BY score DESC
       ) AS place
FROM best
ORDER BY subject, place, student_id;

Сначала оставляем лучшую оценку студента по предмету, затем назначаем места. Равные оценки получают одинаковое место, следующее место идёт без пропуска. Дополнительный student_id в финальном ORDER BY делает порядок строк с равными местами устойчивым; в порядок самого ранжирования его добавлять не нужно.

8. Посчитать процент успешных работ среди оценённых

SELECT s.name,
       ROUND(
         100.0 * SUM(CASE WHEN a.score >= 80 THEN 1 ELSE 0 END)
         / NULLIF(COUNT(a.score), 0),
         1
       ) AS passed_percent
FROM students AS s
LEFT JOIN attempts AS a ON a.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

Для упражнения успехом считается оценка от 80. В знаменателе только оценённые работы. NULLIF не даёт делить на ноль, а 100.0 сохраняет дробную долю. У Бориса результат 100%, потому что единственная оценённая работа успешна; непроверенная попытка в знаменатель не входит.

Как проверять собственное решение

1. Сверяйте смысл строки. Одна работа, один студент и лучший результат студента по предмету являются разными единицами анализа.
2. Проверяйте крайние случаи. Добавьте студента без работ, работу с NULL и две равные лучшие оценки. Убедитесь, что ожидаемое поведение осталось понятным.
3. Разделяйте отсутствие данных и ноль. Ноль может быть настоящей оценкой. NULL сообщает о другом состоянии.
4. Смотрите на кратность JOIN. Если неожиданно выросла сумма, проверьте число строк до агрегации, а не прячьте проблему за DISTINCT.
5. Фиксируйте порядок. Если порядок важен для сравнения, задайте полный ORDER BY, включая разрешение равенств.

Есть несколько корректных способов решить одну задачу. Сравнивайте их сначала по результату и читаемости. Производительность требует отдельного измерения на подходящих данных и плане выполнения: эти маленькие таблицы не являются нагрузочным тестом.

Если запрос возвращает правдоподобное, но неверное число, принесите ментору по SQL минимальный набор данных, свой запрос и ожидаемые строки. Такой пример позволяет разбирать логику конкретно, без догадок о содержимом всей рабочей базы.