Utility Queries

Active Student Subquery

Use the subquery below to filter your results, returning only active or currently enrolled students for the current term.

WHERE dp.raw_person_id IN (
 SELECT DISTINCT
   de.raw_person_id
 FROM data_mart.dim_enrollment de
   INNER JOIN data_mart.dim_section ds ON
     de.raw_section_id = ds.raw_section_id
   INNER JOIN data_mart.dim_career_term dct ON
     dct.raw_term_id = ds.raw_term_id
 WHERE dct.is_active = 1
   AND de.raw_career_id <> 'Not Available'
   --AND e.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
   AND de.civ_is_enrolled = 1)

See example below for how you would use the subquery in the 'Student Continuation Predictions' example.

SELECT
 fcs.event_ts AS score_date
 , dp.institution_person_id
 , fcs.civ_career
 , fcs.prediction
FROM data_mart.fact_continuation_scores fcs
 INNER JOIN data_mart.dim_person dp USING (dim_person_id, institution_id)
WHERE dp.raw_person_id IN (
 SELECT DISTINCT
   de.raw_person_id
 FROM data_mart.dim_enrollment de
   INNER JOIN data_mart.dim_section ds ON
     de.raw_section_id = ds.raw_section_id
   INNER JOIN data_mart.dim_career_term dct ON
     dct.raw_term_id = ds.raw_term_id
 WHERE dct.is_active = 1
   AND de.raw_career_id <> 'Not Available'
   --AND e.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
   AND de.civ_is_enrolled = 1)
ORDER BY dp.institution_person_id, fcs.event_ts DESC;

Active Student Subquery#

Active Student Subquery