Условие

Есть таблица enrollments (enroll_id, student_id, course, enroll_date, progress_pct, status).

Найдите курс с наибольшим числом повторных студентов. Повторный студент — это студент, у которого по данному курсу есть как минимум 2 завершённых обучения (status = 'completed').

При равенстве числа повторных:

выберите курс с большим количеством завершённых обучений в целом;
при новом равенстве — курс с названием, раньше по алфавиту.

PostgreSQL 15.5.

Решение

Подход (двойная агрегация)

Внутренний GROUP BY course, student_id + HAVING COUNT(*) >= 2 — отбирает пары (курс, студент-повторник).
Внешний GROUP BY course — считает число повторников на курсе.
К этой агрегации присоединяем общее число завершённых на курсе для тай-брейкера.
ORDER BY repeats DESC, total_completed DESC, course ASC LIMIT 1.

Реализация

WITH repeats_per_course AS (
    SELECT course, COUNT(*) AS repeat_students
    FROM (
        SELECT course, student_id
        FROM enrollments
        WHERE status = 'completed'
        GROUP BY course, student_id
        HAVING COUNT(*) >= 2
    ) sub
    GROUP BY course
),
total_per_course AS (
    SELECT course, COUNT(*) AS total_completed
    FROM enrollments
    WHERE status = 'completed'
    GROUP BY course
)
SELECT r.course
FROM repeats_per_course r
JOIN total_per_course t USING (course)
ORDER BY r.repeat_students DESC, t.total_completed DESC, r.course ASC
LIMIT 1;

Альтернатива через одну CTE с CASE

WITH base AS (
    SELECT
        course,
        student_id,
        COUNT(*) AS completes_by_user
    FROM enrollments
    WHERE status = 'completed'
    GROUP BY course, student_id
),
agg AS (
    SELECT
        course,
        SUM(CASE WHEN completes_by_user >= 2 THEN 1 ELSE 0 END) AS repeat_students,
        SUM(completes_by_user) AS total_completed
    FROM base
    GROUP BY course
)
SELECT course FROM agg
ORDER BY repeat_students DESC, total_completed DESC, course ASC
LIMIT 1;

Этот вариант сканирует enrollments один раз → быстрее.

Сложность

O(n) + два группирования. Помогает индекс по enrollments(status, course, student_id).

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

Считать COUNT(DISTINCT student_id). Это число уникальных студентов, а не повторников. Нужно фильтровать тех, у кого 2+ завершений.
HAVING COUNT(*) > 2. «Как минимум 2» = >= 2, а не > 2. Распространённая опечатка.
Курсы без повторников выпадают. Запрос вернёт только курсы с хотя бы одним повторником. Если задача ставится «топ-1» — это нормально. Но если бы спросили «список всех курсов с числом повторников», нужен LEFT JOIN от полного списка курсов.
Тай-брейкеры в ORDER BY важны. Без них «при равенстве» — порядок не определён, и автотест может упасть на пограничных кейсах.

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

WITH base AS (
    SELECT course, student_id, COUNT(*) AS cnt
    FROM enrollments
    WHERE status = 'completed'
    GROUP BY course, student_id
)
SELECT course
FROM base
GROUP BY course
ORDER BY
    SUM(CASE WHEN cnt >= 2 THEN 1 ELSE 0 END) DESC,
    SUM(cnt) DESC,
    course ASC
LIMIT 1;

SQL: курс с максимальным числом повторных студентов