Практика SQL: участники и учебные сессии

Два самостоятельных задания в контексте MentorHub. Можно выполнять отдельно от основного проекта: первое — после знакомства с ограничениями и связями, второе — после JOIN и GROUP BY. Используйте PostgreSQL и обычный SQL, без ORM.

Ориентир — 25–40 минут на каждое задание. Сначала сформулируйте решение самостоятельно, затем откройте подсказку и проверьте граничные случаи.

1. Кто участвует в учебной цели

Навык: спроектировать связь many-to-many и защитить данные ограничениями БД.

У MentorHub есть пользователи и учебные цели. Несколько пользователей могут работать над одной целью; один пользователь может участвовать в нескольких целях.

Требования

• У пользователя есть числовой идентификатор и обязательный уникальный email. Для этого упражнения email сравниваются с учётом регистра; нормализация не требуется.
• У цели есть числовой идентификатор и обязательное название. Название должно содержать хотя бы один символ, отличный от обычного пробела.
• Участие связывает существующего пользователя с существующей целью. Для него хранятся момент вступления и роль: owner или member.
• Один пользователь не может вступить в одну цель дважды, даже с другой ролью.
• Цель может пока не иметь участников. Владельцев может быть несколько: ограничение «ровно один owner» в это задание не входит.
• При удалении цели её записи об участии удаляются автоматически. Удалить пользователя, пока он участвует хотя бы в одной цели, нельзя.

Что сделать

1. Нарисуйте связи и напишите CREATE TABLE для users, goals, goal_members. Идентификаторы можно задавать вручную.
2. Добавьте двух пользователей и две цели. Один пользователь участвует в обеих целях, второй — только в первой. Роли назначьте самостоятельно.
3. Напишите запрос: для переданного идентификатора пользователя вывести названия целей, роль и момент вступления. Отсортируйте по идентификатору цели.
4. Отдельными запросами проверьте ограничения. Каждый ошибочный запрос запускайте отдельно либо используйте savepoint: после ошибки PostgreSQL не продолжает обычные команды внутри той же транзакции до её отката.

Самопроверка

Что сдать: SQL создания таблиц, начальные данные, запрос списка целей и результаты проверок. Ограничения должны работать при прямом выполнении SQL, без проверок в Python.

Подсказка — после самостоятельной попытки

Связь many-to-many требует отдельной таблицы. Подумайте, какая пара столбцов должна быть уникальной и почему добавление роли в эту пару нарушит условие. Внешние ключи отвечают за существование связанных строк и поведение при удалении. CHECK не заменяет NOT NULL: проверьте оба ограничения.

Обсудить после решения: какие индексы создаются первичным и уникальным ключами, а какой дополнительный индекс может помочь находить участников по цели?

2. Кто выполнил недельный план

Навык: соединить таблицы, отфильтровать исходные строки и отобрать группы по агрегату.

В MentorHub записываются учебные сессии пользователей. Нужно найти тех, кто за неделю завершил не меньше двух сессий общей длительностью не меньше 120 минут.

Правила отчёта

• Неделя: с 2026-09-14 00:00:00+00 включительно до 2026-09-21 00:00:00+00 исключительно.
• Сессия относится к неделе по моменту начала started_at. Её длительность учитывается целиком.
• Учитываются только сессии со статусом completed.
• Выведите user_id, display_name, число подходящих сессий session_count и сумму минут total_minutes.
• Сортировка: сначала сумма минут по убыванию, при равенстве — идентификатор пользователя по возрастанию.
• Одинаковые имена принадлежат разным пользователям. В отчёте они не должны объединяться.

Данные для запуска

Этот набор независим от первого задания. Временные таблицы существуют только в текущем соединении; выполните подготовку и своё решение в одной сессии. Для повторного запуска подготовки откройте новое соединение.

CREATE TEMP TABLE practice_users (
    id integer PRIMARY KEY,
    display_name text NOT NULL
);

CREATE TEMP TABLE practice_sessions (
    id integer PRIMARY KEY,
    user_id integer NOT NULL REFERENCES practice_users(id),
    started_at timestamptz NOT NULL,
    duration_minutes integer NOT NULL CHECK (duration_minutes > 0),
    status text NOT NULL CHECK (status IN ('completed', 'cancelled'))
);

INSERT INTO practice_users (id, display_name) VALUES
    (1, 'Алекс'), (2, 'Мира'), (3, 'Алекс'),
    (4, 'Лев'), (5, 'Ника'), (6, 'Олег');

INSERT INTO practice_sessions
    (id, user_id, started_at, duration_minutes, status)
VALUES
    (1,  1, '2026-09-14 00:00:00+00', 60,  'completed'),
    (2,  1, '2026-09-16 18:00:00+00', 60,  'completed'),
    (3,  1, '2026-09-17 18:00:00+00', 90,  'cancelled'),
    (4,  1, '2026-09-21 00:00:00+00', 200, 'completed'),
    (5,  2, '2026-09-15 10:00:00+00', 80,  'completed'),
    (6,  2, '2026-09-20 23:59:59+00', 70,  'completed'),
    (7,  3, '2026-09-14 12:00:00+00', 30,  'completed'),
    (8,  3, '2026-09-18 12:00:00+00', 45,  'completed'),
    (9,  4, '2026-09-19 12:00:00+00', 180, 'completed'),
    (10, 5, '2026-09-13 23:59:59+00', 100, 'completed'),
    (11, 5, '2026-09-15 12:00:00+00', 60,  'completed'),
    (12, 5, '2026-09-19 12:00:00+00', 60,  'completed');

Ожидаемый результат

Что сдать: один SELECT-запрос и короткое объяснение, почему в отчёте нет пользователей 3, 4 и 6.

Самопроверка

• Начало недели входит в интервал, начало следующей недели — нет.
• Отменённая сессия не увеличивает ни количество, ни сумму.
• 120 минут достаточно, но одной длинной сессии недостаточно.
• Пользователи с одинаковыми именами остаются отдельными группами.
• Результат не зависит от текущей даты и часового пояса SQL-клиента.

Подсказка — после самостоятельной попытки

Сначала отберите строки по времени и статусу. Затем сгруппируйте по пользователю, посчитайте количество и сумму. WHERE проверяет отдельные строки, HAVING — полученные группы. Для границ используйте явные значения timestamptz с UTC-смещением, а не преобразование времени сессии в date.

Усложнение: верните всех пользователей, включая тех, у кого за неделю нет завершённых сессий. Для них покажите нулевые количество и сумму; отбор по недельному плану уберите. Объясните, как расположение условий по сессиям влияет на LEFT JOIN.

После выполнения

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