Canvas Data 2 Query Review

NickChevalierUT
Community Participant

Confession: I am not a developer or an SQL expert, but I can view certain kinds of code, sort of learn it, and make adjustments as needed. Otherwise, I rely heavily on ChatGPT, particularly when it comes to SQL. 😁

That said, I was wondering if someone more experienced with SQL than I might be able to please look over the SQL query down below that our team has come up with and let me know if we’re on the right track?

We are trying to verify that this query accurately reports on:

  • Student last name
  • Student first name
  • Student SIS user ID
  • Term name
  • Term start date
  • SIS course ID
  • Course title
  • Teacher name of the course in question
  • Submission type (from the latest submission in the course in question)
  • Submission date/time (from the latest submission in the course in question)
  • Assignment title (from the latest submission in the course in question)
  • Enrollment status of the student in question in the course in question
WITH LatestSubmissions AS (
    SELECT
        SPLIT_PART(u.name, ' ', 2) AS last_name, 
        SPLIT_PART(u.name, ' ', 1) AS first_name,
        p.sis_user_id AS user_id,
        et.name AS term_name,
        TO_CHAR(et.start_at - INTERVAL '4 hours', 'MM-DD-YYYY HH:MI:SS AM "ET"') AS term_start_date, -- Subtract 4 hours and format as requested
        c.course_code,
        c.name AS course_name,
        MAX(teachers.teacher_name) AS teacher_name, -- Aggregate the teacher's name
        MAX(s.submission_type) AS submission_type,
        TO_CHAR(s.submitted_at - INTERVAL '4 hours', 'MM-DD-YYYY HH:MI:SS AM "ET"') AS submitted_at, -- Subtract 4 hours and format as requested
        MAX(a.title) AS assignment_title,
        e.workflow_state AS course_enrollment_status, -- Rename the column
        ROW_NUMBER() OVER (PARTITION BY u.id, c.id ORDER BY COALESCE(s.submitted_at, '0001-01-01'::timestamp) DESC) AS row_num
    FROM
        public.users u
    JOIN
        public.enrollments e ON u.id = e.user_id
    JOIN
        public.courses c ON e.course_id = c.id
    JOIN
        public.enrollment_terms et ON c.enrollment_term_id = et.id
    JOIN
        (SELECT enroll.course_id, users.name AS teacher_name
         FROM public.enrollments AS enroll
         JOIN public.users AS users ON enroll.user_id = users.id
         WHERE enroll.type = 'TeacherEnrollment') AS teachers ON c.id = teachers.course_id
    JOIN
        public.pseudonyms p ON u.id = p.user_id
    JOIN
        public.submissions s ON e.course_id = s.course_id AND u.id = s.user_id
    JOIN
        public.assignments a ON s.assignment_id = a.id
    WHERE
        e.type = 'StudentEnrollment'
        AND et.sis_source_id = 'SP_2024'
        AND s.submission_type IS NOT NULL -- Exclude null submission types
        AND s.submitted_at IS NOT NULL -- Exclude null submission times
    GROUP BY
        u.id, c.id, p.sis_user_id, et.name, et.start_at, s.submitted_at, e.workflow_state -- Include enrollment status in the GROUP BY clause
)
SELECT
    last_name,
    first_name,
    user_id,
    term_name,
    term_start_date,
    course_code,
    course_name,
    teacher_name,
    submission_type,
    submitted_at,
    assignment_title,
    course_enrollment_status -- Include enrollment status with the new name
FROM
    LatestSubmissions
WHERE
    row_num = 1
ORDER BY
    last_name, course_code ASC;

Please let me know if any other information is needed.

Thank you!

Labels (4)