Условие

Задача 1. На Учи.ру дети решают задания. Доступ к заданиям платный, но учитель может включить функцию «Урок» на 1 час, и в это время задания становятся бесплатными.

Таблицы:

sessions(card_id, session_time, user_id, teacher_id) — задание и кто решал.
lessons(teacher_id, lesson_start, lesson_end) — периоды бесплатных уроков (lesson_end = lesson_start + 1ч).

Найти количество учеников, у которых ≥ 50% решённых заданий были во время функции «Урок».

Задача 2. Выделение сессий по логам:

Таблица events(user_id, event, timest). Сессия — события одного пользователя без перерыва > 30 минут. Нужно добавить колонку session_id.

Решение

Задача 1 — доля заданий во время «Урок»

WITH lessons_flag AS (
  SELECT s.user_id, s.card_id, s.session_time,
         CASE WHEN EXISTS (
           SELECT 1 FROM lessons l
           WHERE l.teacher_id = s.teacher_id
             AND s.session_time BETWEEN l.lesson_start AND l.lesson_end
         ) THEN 1 ELSE 0 END AS in_lesson
  FROM sessions s
),
user_share AS (
  SELECT user_id,
         AVG(in_lesson) AS lesson_share,
         COUNT(*)       AS total_sessions
  FROM lessons_flag
  GROUP BY user_id
)
SELECT COUNT(*) AS n_students
FROM user_share
WHERE lesson_share > 0.5;

Альтернатива через JOIN (вместо EXISTS) — обычно медленнее на больших данных, но читаемее.

Задача 2 — sessionization (gaps and islands)

WITH labeled AS (
  SELECT user_id, event, timest,
         CASE
           WHEN LAG(timest) OVER (PARTITION BY user_id ORDER BY timest) IS NULL
             OR EXTRACT(EPOCH FROM (timest - LAG(timest) OVER (PARTITION BY user_id ORDER BY timest))) > 1800
           THEN 1 ELSE 0
         END AS new_session
  FROM events
)
SELECT user_id, event, timest,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY timest
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM labeled
ORDER BY user_id, timest;

session_id — кумулятивная сумма флагов «новой сессии» в пределах пользователя.

Если нужен глобальный session_id (уникальный на всех пользователей), оберните (user_id, local_session_id) в DENSE_RANK:

SELECT *,
       DENSE_RANK() OVER (ORDER BY user_id, session_id) AS global_sid
FROM ...;

Подводные камни

«Урок» пересекается с заданием по teacher_id: должна быть привязка s.teacher_id = l.teacher_id. Иначе включат «урок» одного учителя для заданий другого.
Граница интервала: BETWEEN l.lesson_start AND l.lesson_end — закрытый. Если задание решено ровно в lesson_end, считается. Уточните.
30 минут sessionization: разница в timest = 30:00 ровно — это новая сессия или нет? Используйте > 1800, не >=, в зависимости от бизнес-определения.
PARTITION BY user_id обязателен. Без него — события разных пользователей перемешиваются, кумулятивный SUM(new_session) накапливается ошибочно.
Перформанс: EXISTS использует индекс (teacher_id, lesson_start). На больших данных — лучше JOIN с правильным индексом.
«50%» включает или нет: ≥ 50% или > 50%? В задаче — «больше 50%».

Эталонный ответ

(1) EXISTS для in-lesson флага → user-level share → WHERE share > 0.5.

(2) LAG для разницы со предыдущим event → флаг new_session при разрыве > 30мин → cumsum в окне PARTITION BY user_id.

Учи.ру — доля заданий во время Урок и выделение пользовательских сессий