Enrollment and Rostering

SELECT
  site_id,
  academic_year,
  grade_level_id,
  user_id,
  section_id,
  course_id,
  student_id,
  entry_date,
  leave_date
FROM matviews.ss_cube
LIMIT 10

-- Get all 7th and 8th graders enrolled & rostered in 2017
SELECT
  student_id,
  local_student_id,
  last_name,
  first_name
FROM students
WHERE student_id IN (
  SELECT student_id
  FROM matviews.ss_cube
  WHERE academic_year = 2017
    AND grade_level_id IN (
      SELECT grade_level_id
      FROM grade_levels
      WHERE system_key IN ('grade_7', 'grade_8')
    )
)
LIMIT 200

-- Get a distinct count of students enrolled & rostered today by course
WITH course_counts AS (
  SELECT
    course_id,
    COUNT(DISTINCT student_id) AS stu_count
  FROM matviews.ss_cube
  WHERE academic_year = 2017
    AND current_date BETWEEN entry_date AND leave_date
  GROUP BY course_id
)
SELECT
  courses.course_id,
  courses.long_name,
  course_counts.stu_count
FROM courses
JOIN course_counts USING (course_id)
LIMIT 100

-- Get class roster for students
SELECT
  sites.site_id,
  sites.site_name,
  users.user_id,
  standard_person_name_id(
    users.user_id,
    users.last_name,
    users.first_name,
    users.middle_name
  ) AS teacher_name,
  courses.course_id,
  courses.long_name AS course_name,
  departments.department_name AS department_name,
  sections.section_id,
  COALESCE(
    sections.section_name,
    sections.local_section_id,
    sections.section_id :: text
  ) AS local_section_id,
  get_section_timeblocks(sections.section_id) AS period,
  students.student_id,
  students.local_student_id,
  standard_person_name(
    students.last_name,
    students.first_name,
    students.middle_name
  ) AS student_name,
  grade_levels.short_name AS student_grade_level
FROM matviews.ss_cube
JOIN sites ON sites.site_id = ss_cube.site_id
JOIN users ON users.user_id = ss_cube.user_id
JOIN courses ON courses.course_id = ss_cube.course_id
JOIN departments ON departments.department_id = courses.department_id
JOIN sections ON sections.section_id = ss_cube.section_id
JOIN students ON students.student_id = ss_cube.student_id
JOIN grade_levels ON grade_levels.grade_level_id = ss_cube.grade_level_id
WHERE ss_cube.academic_year = 2017
  AND current_date BETWEEN ss_cube.entry_date AND ss_cube.leave_date
ORDER BY sites.site_name,
  users.last_name,
  users.first_name,
  courses.long_name,
  sections.section_id,
  students.last_name,
  students.first_name
LIMIT 100