Campus Manager Queries

failingTerm.sql

SELECT 
  weighted.first_name,
  weighted.last_name,
  weighted.phone,
  weighted.username,
  weighted.term,
  weighted.course,
  weighted.city,
  ROUND(SUM(weighted.score), 0) as score
FROM (
  SELECT
    users.first_name,
    users.last_name,
    users.phone,
    users.username,
    terms.name as term,
    courses.name as course,
    locations.city,
    case
	  when courses_grades.checkpoint = 'true' then AVG(users_grades.score) * 0.7
	  when (
	    SELECT COUNT(*)
	    FROM users as u
        JOIN courses_registrations ON courses_registrations.registrations = u._id
        JOIN courses ON courses.yosql_id = courses_registrations.courses_yosql_id
        JOIN terms ON terms._id = courses.term
        JOIN users_grades on u.yosql_id = users_grades.users_yosql_id
        JOIN courses_grades on courses.yosql_id = courses_grades.courses_yosql_id
		  AND courses_grades.name = users_grades.name
		  AND users_grades.courseID = courses._id 
        WHERE DATE('now') between terms.start_date AND terms.end_date
        AND users_grades.score != ''
	    AND courses_grades.checkpoint = 'true'
	    AND u.username = users.username
        GROUP BY u.username
	  ) > 0 then AVG(users_grades.score) * 0.3
	  else AVG(users_grades.score)
      end as score
  FROM users
  JOIN courses_registrations ON courses_registrations.registrations = users._id
  JOIN courses ON courses.yosql_id = courses_registrations.courses_yosql_id
  JOIN terms ON terms._id = courses.term
  JOIN locations on locations._id = courses.location
  JOIN users_grades on users.yosql_id = users_grades.users_yosql_id
  JOIN courses_grades on courses.yosql_id = courses_grades.courses_yosql_id
    AND courses_grades.name = users_grades.name
    AND users_grades.courseID = courses._id 
  WHERE DATE('now') between terms.start_date AND terms.end_date
  AND users_grades.score != ''
  GROUP BY users.username, courses_grades.checkpoint
) as weighted
WHERE score < 70
GROUP BY weighted.username;

recentAbsences.sql

select 
  users.first_name,
  users.last_name,
  users.phone,
  users.username,
  courses.name as course,
  terms.name as term,
  locations.city,
  course_dates.date as absent,
  case cast (strftime('%w', course_dates.date) as integer)
      when 0 then 'Sunday'
      when 1 then 'Monday'
      when 2 then 'Tuesday'
      when 3 then 'Wednesday'
      when 4 then 'Thursday'
      when 5 then 'Friday'
      else 'Saturday'
  end as day
from users
join courses_registrations on courses_registrations.registrations = users._id
join courses on courses.yosql_id = courses_registrations.courses_yosql_id
join course_dates on courses._id = course_dates.course_id
join terms on terms._id = courses.term
join locations on courses.location = locations._id
AND course_dates.date between DATE('now', '-8 days') AND DATE('now', '-1 days')
EXCEPT
  select
    users.first_name,
    users.last_name,
    users.phone,
    users.username,
    courses.name,
    terms.name,
    locations.city,
    strftime('%Y-%m-%d', users_attendance.attendance),
    case cast (strftime('%w', users_attendance.attendance) as integer)
        when 0 then 'Sunday'
        when 1 then 'Monday'
        when 2 then 'Tuesday'
        when 3 then 'Wednesday'
        when 4 then 'Thursday'
        when 5 then 'Friday'
        else 'Saturday'
    end
  from users_attendance
  join users on users.yosql_id = users_attendance.users_yosql_id
  join courses_registrations on courses_registrations.registrations = users._id
  join courses on courses.yosql_id = courses_registrations.courses_yosql_id
  join course_dates on courses._id = course_dates.course_id
  join terms on terms._id = courses.term
  join locations on courses.location = locations._id
  where users_attendance.users_yosql_id = users.yosql_id;