Objective

Write a SQL query to calculate the re-enrollment rate of students based on their consecutive term enrollments. The re-enrollment rate is defined as the percentage of students who have enrolled in two or more consecutive terms.

Additional information

• Consecutive terms are identified by increasing term_ids without any gaps.

• The result should be rounded to two decimal places.

• Output the re-enrollment rate as a percentage.

• The enrollments table schema:

  Column	Type
  student_id	INT
  term_id	INT
  course_id	VARCHAR

WITH consecutive_terms AS (
    SELECT
        student_id,
        term_id,
        term_id - ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY term_id) AS grp
    FROM enrollments
)
SELECT
    ROUND(
        CAST(
            COUNT(DISTINCT CASE WHEN consecutive_count >= 2 THEN student_id END)::float
            / COUNT(DISTINCT student_id)::float * 100
        AS numeric),
    2) AS re_enrollment_rate
FROM (
    SELECT student_id, grp, COUNT(*) AS consecutive_count
    FROM consecutive_terms
    GROUP BY student_id, grp
) subquery;

Explanation

Step 1: Identify consecutive term groups (CTE)

For each student, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY term_id) assigns sequential integers to their terms. Subtracting this row number from the term_id produces the same value (grp) for all consecutive terms, and a different value whenever there is a gap.

term_id - ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY term_id) AS grp

Step 2: Count the size of each consecutive group

The inner subquery groups by student_id and grp, counting rows per group. A consecutive_count >= 2 means the student enrolled in at least two consecutive terms.

SELECT student_id, grp, COUNT(*) AS consecutive_count
FROM consecutive_terms
GROUP BY student_id, grp

Step 3: Calculate the re-enrollment rate

The outer query counts distinct students with at least one group of two or more consecutive terms, divides by the total number of distinct students, multiplies by 100, and rounds to two decimal places.

ROUND(
    CAST(
        COUNT(DISTINCT CASE WHEN consecutive_count >= 2 THEN student_id END)::float
        / COUNT(DISTINCT student_id)::float * 100
    AS numeric),
2) AS re_enrollment_rate