Module sd.queries

# -*- coding: utf-8 -*-
"""
A module containing only SQL query strings.
"""

ALL_SEMESTER_DATA = """
SELECT s.student_id_fk
        , CASE WHEN s.gender = 'F' THEN 1 ELSE 0 END AS female
        ,CASE WHEN s.ethnicity IN ('Asian', 'Asian / Pacific Islander', 'Caucasian (non Hispanic)') THEN 1 ELSE 0 END as white_asian
        , s.first_course_datetime
        , YEAR(s.first_course_datetime) - 1992 AS first_course_year
        , CASE WHEN s.hsgpa > 4.0 THEN 4.0 ELSE s.hsgpa END AS hs_gpa
        , s.math_placement_score
        , m.changed_major
        , m.n_majors
        , m.semester_idx
        , c.avg_grade
        , c.cumulative_avg_grade
        , c.fraction_above_or_below_fulltime_credithours
        , c.total_semester_credit_hours
        , c.cumulative_credit_hours
        , first_degree.semester_idx AS graduated_semester_idx
        , CASE WHEN first_degree.student_id_fk IS NOT NULL THEN first_degree.semester_idx ELSE ls.last_semester END AS last_semester_enrolled
        , CASE WHEN m.semester_idx + 1 = first_degree.semester_idx THEN 1 ELSE 0 END as graduates_next_semester
        , CASE WHEN first_degree.student_id_fk IS NULL THEN 0 ELSE 1 END AS graduated
FROM un_students_1992_2013 AS s
JOIN un_majors_aggregates AS m
ON s.student_id_fk = m.student_id_fk
JOIN cumulative_course_agg_per_semester AS c
ON s.student_id_fk = c.student_id_fk
AND m.Term_Seq_Id = c.Term_Seq_Id
LEFT JOIN (
SELECT *
FROM (
        SELECT d.student_id_fk
        , d.term_seq_id
        , m.semester_idx
        , ROW_NUMBER() OVER (PARTITION BY d.student_id_fk ORDER BY d.term_seq_id) AS degree_idx
          FROM [msu_v1].[dbo].[un_degrees] as d
          JOIN un_majors_aggregates as m
          ON d.student_id_fk = m.student_id_fk
          AND d.term_seq_id = m.Term_Seq_Id
          WHERE primary_major = 'Y'
         ) AS first_degree
        WHERE degree_idx = 1
) AS first_degree
ON s.student_id_fk = first_degree.student_id_fk
LEFT JOIN (
        SELECT [student_id_fk]
      ,MAX([semester_idx]) AS last_semester
  FROM [msu_v1].[dbo].[un_majors_aggregates]
  GROUP BY student_id_fk
  ) as ls
  ON s.student_id_fk = ls.student_id_fk
  WHERE ls.last_semester <= 20
"""