1# Annual analysis of course enrollments, completions, and certificates

SELECT
    EXTRACT(YEAR FROM e.enroll_date) AS Year,
    COUNT(DISTINCT e.user_id) AS Total_Students,
    COUNT(DISTINCT e.enrollment_id) AS Total_Enrollments,
    COUNT(DISTINCT CASE WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id END) AS Completed_Enrollments,
    COUNT(DISTINCT cert.certificate_id) AS Total_Certificates,
    ROUND(
        COUNT(DISTINCT CASE WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id END)::DECIMAL
        / NULLIF(COUNT(DISTINCT e.enrollment_id), 0) * 100, 2) AS Completion_Rate_Percentage,
    ROUND(AVG(e.progress_percentage), 2) AS Avg_Progress_Percentage
FROM enrollment e
LEFT JOIN certificate cert
    ON cert.enrollment_id = e.enrollment_id
GROUP BY EXTRACT(YEAR FROM e.enroll_date)
ORDER BY Year;

Solution Relational Algebra

• E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
• CERT(certificate_id, enrollment_id, issue_date, certificate_code, status)

JOIN on all tables

J1 ← E ⟕ E.enrollment_id = CERT.enrollment_id CERT

Projection with date transformation

E' ← π enrollment_id, user_id, completion_status, progress_percentage, certificate_id, YEAR(enroll_date) → Year (J1)

Grouping and aggregate calculations

G ← Year γ COUNT(DISTINCT user_id) → Total_Students, COUNT(DISTINCT enrollment_id) → Total_Enrollments, COUNT(DISTINCT [completion_status = 'COMPLETED'] enrollment_id) → Completed_Enrollments, COUNT(DISTINCT certificate_id) → Total_Certificates, AVG(progress_percentage) → Avg_Progress_Percentage (E')

Final projection and percentage calculation

P ← π Year, Total_Students, Total_Enrollments, Completed_Enrollments, Total_Certificates, (Completed_Enrollments / Total_Enrollments) * 100 → Completion_Rate_Percentage, Avg_Progress_Percentage (G)

Chronological ordering by year R_final ← τ Year (P)

2# Monthly analysis of instructor activity by courses, students, modules, and lessons

SELECT
    EXTRACT(YEAR FROM e.enroll_date) AS Year,
    EXTRACT(MONTH FROM e.enroll_date) AS Month,
    COUNT(DISTINCT i.id) AS Total_Instructors,
    COUNT(DISTINCT c.course_id) AS Total_Courses,
    COUNT(DISTINCT e.user_id) AS Total_Students,
    COUNT(DISTINCT m.module_id) AS Total_Modules,
    COUNT(DISTINCT l.lesson_id) AS Total_Lessons,
    ROUND(AVG(c.price), 2) AS Avg_Course_Price
FROM enrollment e
JOIN course c
    ON c.course_id = e.course_id
JOIN instructors i
    ON i.id = c.instructor_id
LEFT JOIN module m
    ON m.course_id = c.course_id
LEFT JOIN lesson l
    ON l.module_id = m.module_id
GROUP BY
    EXTRACT(YEAR FROM e.enroll_date),
    EXTRACT(MONTH FROM e.enroll_date)
ORDER BY
    Year,
    Month;

Solution Relational Algebra

• E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
• C(course_id, name, price, status, instructor_id)
• I(id)
• M(module_id, course_id, title, description)
• L(lesson_id, module_id, title, material)

JOIN on all tables:

J1 ← E ⋈ E.course_id = C.course_id C

J2 ← J1 ⋈ C.instructor_id = I.id I

J3 ← J2 ⟕ C.course_id = M.course_id M

J4 ← J3 ⟕ M.module_id = L.module_id L

Projection with instructor full name

F1 ← π user_id, course_id, id, module_id, lesson_id, price, YEAR(enroll_date) → Year, MONTH(enroll_date) → Month (J4)

Grouping and aggregate calculations

G ← Year, Month γ COUNT(DISTINCT id) → Total_Instructors, COUNT(DISTINCT course_id) → Total_Courses, COUNT(DISTINCT user_id) → Total_Students, COUNT(DISTINCT module_id) → Total_Modules, COUNT(DISTINCT lesson_id) → Total_Lessons, AVG(price) → Avg_Course_Price (F1)

Ordering by number of students and courses

R_final ← τ Year, Month (G)

3# Annual subscription and revenue analysis by subscription plan

SELECT
    EXTRACT(YEAR FROM us.start_date) AS Year,
    sp.name AS Plan_Name,
    COUNT(DISTINCT us.subscription_id) AS Total_Subscriptions,
    COUNT(DISTINCT us.user_id) AS Total_Users,
    COUNT(DISTINCT p.payment_id) AS Total_Payments,
    ROUND(SUM(COALESCE(p.amount, 0)), 2) AS Total_Revenue,
    ROUND(AVG(COALESCE(p.amount, 0)), 2) AS Avg_Payment_Amount
FROM user_subscription us
JOIN subscription_plan sp
    ON sp.plan_id = us.plan_id
LEFT JOIN payment p
    ON p.subscription_id = us.subscription_id
GROUP BY
    EXTRACT(YEAR FROM us.start_date),
    sp.name
ORDER BY
    Year,
    Total_Revenue DESC;

Solution Relational Algebra

• US(subscription_id, user_id, plan_id, start_date, end_date, status)
• SP(plan_id, name, price, duration_months, description, access_type)
• P(payment_id, user_id, subscription_id, amount)

JOIN on all tables

J1 ← US ⋈ US.plan_id = SP.plan_id SP

J2 ← J1 ⋈ US.subscription_id = P.subscription_id P

Projection with date transformation

F1 ← π subscription_id, user_id, payment_id, amount, name, YEAR(start_date) → Year (J2)

Renaming attribute

F2 ← ρ Plan_Name/name (F1)

Grouping and aggregate calculations

G ← Year, Plan_Name γ COUNT(DISTINCT subscription_id) → Total_Subscriptions, COUNT(DISTINCT user_id) → Total_Users, COUNT(DISTINCT payment_id) → Total_Payments, SUM(amount) → Total_Revenue, AVG(amount) → Avg_Payment_Amount (F2)

Chronological ordering and descending revenue ordering

R_final ← τ Year, Total_Revenue DESC (G)

4# Yearly category analysis by attempts, students, average score, and pass rate

SELECT
    EXTRACT(YEAR FROM qa.attempt_date) AS Year,
    cat.name AS Category_Name,
    COUNT(DISTINCT qa.attempt_id) AS Total_Attempts,
    COUNT(DISTINCT qa.user_id) AS Total_Students,
    ROUND(AVG(qa.score), 2) AS Avg_Score,
    COUNT(
        DISTINCT CASE
            WHEN qa.score >= q.passing_score
            THEN qa.attempt_id
        END
    ) AS Passed_Attempts,
    ROUND(
        COUNT(
            DISTINCT CASE
                WHEN qa.score >= q.passing_score
                THEN qa.attempt_id
            END
        )::DECIMAL /
        NULLIF(COUNT(DISTINCT qa.attempt_id),0) * 100,
        2
    ) AS Pass_Rate_Percentage
FROM quiz_attempt qa
JOIN quiz q
    ON q.quiz_id = qa.quiz_id
JOIN lesson l
    ON l.lesson_id = q.lesson_id
JOIN module m
    ON m.module_id = l.module_id
JOIN course c
    ON c.course_id = m.course_id
JOIN course_category cc
    ON cc.course_id = c.course_id
JOIN category cat
    ON cat.category_id = cc.category_id
GROUP BY
    EXTRACT(YEAR FROM qa.attempt_date),
    cat.name
ORDER BY
    Year,
    Pass_Rate_Percentage DESC;

Solution Relational Algebra

• QA(attempt_id, score, attempt_date, user_id, quiz_id)
• Q(quiz_id, total_points, passing_score, lesson_id)
• L(lesson_id, module_id, title, material)
• M(module_id, course_id, title, description)
• C(course_id, name, price, status, instructor_id)
• CC(course_id, category_id)
• CAT(category_id, name, description)

JOIN on all tables:

J1 ← QA ⋈ QA.quiz_id = Q.quiz_id Q

J2 ← J1 ⋈ Q.lesson_id = L.lesson_id L

J3 ← J2 ⋈ L.module_id = M.module_id M

J4 ← J3 ⋈ M.course_id = C.course_id C

J5 ← J4 ⋈ C.course_id = CC.course_id CC

J6 ← J5 ⋈ CC.category_id = CAT.category_id CAT

Projection and renaming

F1 ← π attempt_id, user_id, score, passing_score, CAT.name → Category_Name, YEAR(attempt_date) → Year (J6)

Grouping and aggregate calculations

G ← Year, Category_Name γ

COUNT(DISTINCT attempt_id) → Total_Attempts,

COUNT(DISTINCT user_id) → Total_Students,

AVG(score) → Avg_Score,

COUNT(DISTINCT [score ≥ passing_score] attempt_id) → Passed_Attempts (F1)

Final projection and percentage calculation

P ← π Year, Category_Name, Total_Attempts, Total_Students, Avg_Score, Passed_Attempts,

(Passed_Attempts / Total_Attempts) * 100 → Pass_Rate_Percentage (G)

Chronological ordering by year

R_final ← τ Year, Pass_Rate_Percentage DESC (P)

5# Yearly analysis of overall platform activity and student success across enrollments, quiz attempts, completions, certificates, and course performance

This SQL query provides a comprehensive yearly analysis of the platform by combining multiple aspects of user activity, instructor engagement, and student performance. Active platform performance is evaluated through:

• Instructor activity based on courses with enrollments.
• Student participation through enrollments and quiz attempts
• Course completion status and issued certificates
• Student success is measured through progress percentage
• Quiz performance based on average scores and pass rate
• Course-level success calculated as average quiz score per course
• Overall yearly success calculated as the average performance across all courses

This query:

• Aggregates data year by year to observe platform growth and trends
• Combines multiple entities, including instructors, students, courses, enrollments, quizzes, and certificates
• Calculates both activity-based and performance-based metrics
• Derives course-level and overall yearly success indicators
• Provides a multi-dimensional analytical view of the platform

WITH course_year_performance AS (
    SELECT
        EXTRACT(YEAR FROM qa.attempt_date) AS year,
        c.course_id,
        ROUND(AVG(qa.score), 2) AS final_grade_per_course
    FROM quiz_attempt qa
    JOIN quiz q
        ON q.quiz_id = qa.quiz_id
    JOIN lesson l
        ON l.lesson_id = q.lesson_id
    JOIN module m
        ON m.module_id = l.module_id
    JOIN course c
        ON c.course_id = m.course_id
    GROUP BY
        EXTRACT(YEAR FROM qa.attempt_date),
        c.course_id
),
year_course_summary AS (
    SELECT
        year,
        ROUND(AVG(final_grade_per_course), 2) AS final_year_success
    FROM course_year_performance
    GROUP BY year
)
SELECT
    EXTRACT(YEAR FROM e.enroll_date) AS year,
    COUNT(DISTINCT c.instructor_id) AS total_instructor_activity,
    COUNT(DISTINCT e.user_id) AS total_students,
    COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
    COUNT(DISTINCT qa.attempt_id) AS total_attempts,
    COUNT(DISTINCT CASE
        WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
    END) AS completed_enrollments,
    COUNT(DISTINCT cert.certificate_id) AS total_certificates,
    ROUND(AVG(e.progress_percentage), 2) AS avg_student_success_within_courses,
    ROUND(AVG(qa.score), 2) AS avg_final_grade_per_course, ycs.final_year_success
FROM enrollment e
JOIN course c
    ON c.course_id = e.course_id
LEFT JOIN certificate cert
    ON cert.enrollment_id = e.enrollment_id
LEFT JOIN module m
    ON m.course_id = c.course_id
LEFT JOIN lesson l
    ON l.module_id = m.module_id
LEFT JOIN quiz q
    ON q.lesson_id = l.lesson_id
LEFT JOIN quiz_attempt qa
    ON qa.quiz_id = q.quiz_id
    AND qa.user_id = e.user_id
LEFT JOIN year_course_summary ycs
    ON ycs.year = EXTRACT(YEAR FROM e.enroll_date)
GROUP BY
    EXTRACT(YEAR FROM e.enroll_date), ycs.final_year_success
ORDER BY year;

Solution Relational Algebra

• E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
• C(course_id, name, price, status, instructor_id)
• CERT(certificate_id, enrollment_id, issue_date, certificate_code, status)
• M(module_id, course_id, title, description)
• L(lesson_id, module_id, title, material)
• Q(quiz_id, total_points, passing_score, lesson_id)
• QA(attempt_id, score, attempt_date, user_id, quiz_id)

JOIN on all tables:

J1 ← E ⋈ E.course_id = C.course_id C

J2 ← J1 ⟕ E.enrollment_id = CERT.enrollment_id CERT

J3 ← J2 ⟕ C.course_id = M.course_id M

J4 ← J3 ⟕ M.module_id = L.module_id L

J5 ← J4 ⟕ L.lesson_id = Q.lesson_id Q

J6 ← J5 ⟕ (Q.quiz_id = QA.quiz_id ∧ QA.user_id = E.user_id) QA

Projection with date transformation

F1 ← π enrollment_id, user_id, instructor_id, completion_status, progress_percentage, certificate_id, attempt_id, score, course_id, YEAR(enroll_date) → Year (J6)

Grouping and aggregate calculations

G ← Year γ

COUNT(DISTINCT instructor_id) → Total_Instructor_Activity,

COUNT(DISTINCT user_id) → Total_Students,

COUNT(DISTINCT enrollment_id) → Total_Enrollments,

COUNT(DISTINCT attempt_id) → Total_Attempts,

COUNT(DISTINCT [completion_status = 'COMPLETED'] enrollment_id) → Completed_Enrollments,

COUNT(DISTINCT certificate_id) → Total_Certificates,

AVG(progress_percentage) → Avg_Student_Success_Within_Courses,

AVG(score) → Avg_Final_Grade_Per_Course (F1)

Course-level yearly performance

CP1 ← QA ⋈ QA.quiz_id = Q.quiz_id Q

CP2 ← CP1 ⋈ Q.lesson_id = L.lesson_id L

CP3 ← CP2 ⋈ L.module_id = M.module_id M

CP4 ← CP3 ⋈ M.course_id = C.course_id C

CF ← π course_id, score, YEAR(attempt_date) → Year (CP4)

CG ← Year, course_id γ AVG(score) → Final_Grade_Per_Course (CF)

Final yearly course success Y ← Year γ AVG(Final_Grade_Per_Course) → Final_Year_Success (CG)

Final join and projection

R1 ← G ⋈ G.Year = Y.Year Y

P ← π Year, Total_Instructor_Activity, Total_Students, Total_Enrollments, Total_Attempts, Completed_Enrollments, Total_Certificates, Avg_Student_Success_Within_Courses, Avg_Final_Grade_Per_Course, Final_Year_Success (R1)

Chronological ordering by year

R_final ← τ Year (P)

6# Monthly analysis of course activity, completions, instructor involvement, and student success within a given year

This SQL query provides a month-by-month analysis of the platform within a selected year, combining course activity data with student performance indicators. The report evaluates what happens in each month through:

• The total number of course enrollments.
• The total number of completed courses
• The number of instructors whose courses had enrollments
• The average student score achieved through quiz attempts in active courses
• The average success at the course level
• The final average success at the month level across all active courses

This query:

• Groups platform activity month by month within a given year
• Combines enrollment, completion, instructor, and quiz performance data
• Calculates student success using average quiz score
• Derives course-level average success and final monthly average success
• Provides a broader analytical picture of platform activity and learning outcomes over time

WITH student_course_month_performance AS (
    SELECT
        EXTRACT(YEAR FROM qa.attempt_date) AS year,
        EXTRACT(MONTH FROM qa.attempt_date) AS month,
        c.course_id,
        qa.user_id,
        ROUND(AVG(qa.score), 2) AS avg_student_score
    FROM quiz_attempt qa
    JOIN quiz q
        ON q.quiz_id = qa.quiz_id
    JOIN lesson l
        ON l.lesson_id = q.lesson_id
    JOIN module m
        ON m.module_id = l.module_id
    JOIN course c
        ON c.course_id = m.course_id
    GROUP BY
        EXTRACT(YEAR FROM qa.attempt_date),
        EXTRACT(MONTH FROM qa.attempt_date),
        c.course_id,
        qa.user_id
),
course_month_performance AS (
    SELECT
        year,
        month,
        course_id,
        ROUND(AVG(avg_student_score), 2) AS avg_course_success
    FROM student_course_month_performance
    GROUP BY
        year,
        month,
        course_id
),
month_performance_summary AS (
    SELECT
        year,
        month,
        ROUND(AVG(avg_course_success), 2) AS final_month_success
    FROM course_month_performance
    GROUP BY
        year,
        month
)
SELECT
    EXTRACT(YEAR FROM e.enroll_date) AS year,
    EXTRACT(MONTH FROM e.enroll_date) AS month,
    COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
    COUNT(DISTINCT CASE
        WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
    END) AS completed_enrollments,
    COUNT(DISTINCT c.instructor_id) AS active_instructors,
    ROUND(AVG(scmp.avg_student_score), 2) AS avg_student_success,
    ROUND(AVG(cmp.avg_course_success), 2) AS avg_course_success,
    mps.final_month_success
FROM enrollment e
JOIN course c
    ON c.course_id = e.course_id
LEFT JOIN student_course_month_performance scmp
    ON scmp.year = EXTRACT(YEAR FROM e.enroll_date)
    AND scmp.month = EXTRACT(MONTH FROM e.enroll_date)
    AND scmp.course_id = e.course_id
    AND scmp.user_id = e.user_id
LEFT JOIN course_month_performance cmp
    ON cmp.year = EXTRACT(YEAR FROM e.enroll_date)
    AND cmp.month = EXTRACT(MONTH FROM e.enroll_date)
    AND cmp.course_id = e.course_id
LEFT JOIN month_performance_summary mps
    ON mps.year = EXTRACT(YEAR FROM e.enroll_date)
    AND mps.month = EXTRACT(MONTH FROM e.enroll_date)
GROUP BY
    EXTRACT(YEAR FROM e.enroll_date),
    EXTRACT(MONTH FROM e.enroll_date),
    mps.final_month_success
ORDER BY
    year,
    month;

Solution Relational Algebra

• E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
• C(course_id, name, price, status, instructor_id)
• Q(quiz_id, total_points, passing_score, lesson_id)
• QA(attempt_id, score, attempt_date, user_id, quiz_id)
• L(lesson_id, module_id, title, material)
• M(module_id, course_id, title, description)

JOIN on all tables:

J1 ← QA ⋈ QA.quiz_id = Q.quiz_id Q

J2 ← J1 ⋈ Q.lesson_id = L.lesson_id L

J3 ← J2 ⋈ L.module_id = M.module_id M

J4 ← J3 ⋈ M.course_id = C.course_id C

J5 ← E ⋈ E.course_id = C.course_id C

Projection with date transformation for student-course-month performance

F1 ← π QA.user_id, C.course_id, QA.score, YEAR(QA.attempt_date) → Year, MONTH(QA.attempt_date) → Month (J4)

Grouping and aggregate calculations at student-course-month level

G1 ← Year, Month, course_id, user_id γ AVG(score) → Avg_Student_Score (F1)

Grouping and aggregate calculations at course-month level

G2 ← Year, Month, course_id γ AVG(Avg_Student_Score) → Avg_Course_Success (G1)

Grouping and aggregate calculations at the month level G3 ← Year, Month γ AVG(Avg_Course_Success) → Final_Month_Success (G2)

Projection with date transformation for enrollment activity F2 ← π enrollment_id, user_id, course_id, instructor_id, completion_status, YEAR(enroll_date) → Year, MONTH(enroll_date) → Month (J5)

Grouping and aggregate calculations for monthly platform activity G4 ← Year, Month γ COUNT(DISTINCT enrollment_id) → Total_Enrollments, COUNT(DISTINCT [completion_status = 'COMPLETED'] enrollment_id) → Completed_Enrollments, COUNT(DISTINCT instructor_id) → Active_Instructors (F2)

Joining activity data with performance data R1 ← G4 ⋈ G4.Year = G3.Year ∧ G4.Month = G3.Month G3

Final projection

P ← π Year, Month, Total_Enrollments, Completed_Enrollments, Active_Instructors, Final_Month_Success (R1)

Chronological ordering by year

R_final ← τ Year, Month (P)