modelname Instructor Analytics Dashboard

Simulated SQL queries for learning outcomes, completion, quiz difficulty, engagement, and revenue.

1. Course Enrollment Trends Over Time

SELECT
    DATE_TRUNC('week', e.enrolled_at) AS week,
    COUNT(*) AS enrollments
FROM enrollments e
WHERE e.course_id = $1
GROUP BY week
ORDER BY week;

Sample Results

2. Average Completion Rate by Course

SELECT
    c.id,
    c.title,
    COUNT(e.id) AS enrolled,
    COUNT(CASE WHEN e.status = 'completed' THEN 1 END) AS completed,
    ROUND(
        100.0 * COUNT(CASE WHEN e.status = 'completed' THEN 1 END)
        / NULLIF(COUNT(e.id), 0),
        1
    ) AS completion_rate
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY completion_rate DESC;

Sample Results

3. Quiz Difficulty Analysis

SELECT
    q.id,
    q.prompt,
    COUNT(qa.id) AS attempts,
    ROUND(
        AVG(CASE WHEN qa.is_correct THEN 1 ELSE 0 END) * 100,
        1
    ) AS pass_rate
FROM quiz_questions q
JOIN quiz_answers qa ON qa.question_id = q.id
GROUP BY q.id, q.prompt
ORDER BY pass_rate ASC
LIMIT 10;

Hardest Questions

4. Student Engagement Metrics

SELECT
    c.id,
    c.title,
    COUNT(DISTINCT la.user_id) AS active_students,
    ROUND(AVG(la.duration_seconds) / 60, 1) AS avg_minutes_per_session,
    COUNT(DISTINCT lp.lesson_id) AS lessons_completed,
    ROUND(AVG(qa.score), 1) AS average_quiz_score
FROM courses c
JOIN enrollments e ON e.course_id = c.id
JOIN learning_activity la ON la.user_id = e.student_id
LEFT JOIN lesson_progress lp ON lp.enrollment_id = e.id
LEFT JOIN quiz_attempts qa ON qa.enrollment_id = e.id
GROUP BY c.id, c.title
ORDER BY active_students DESC;

Sample Results

5. Revenue Breakdown

SELECT
    c.id,
    c.title,
    SUM(e.price_paid) AS revenue,
    COUNT(CASE WHEN e.price_paid > 0 THEN 1 END) AS paid_students,
    COUNT(CASE WHEN e.price_paid = 0 THEN 1 END) AS free_students,
    ROUND(AVG(e.price_paid), 2) AS avg_revenue_per_student
FROM courses c
JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY revenue DESC;