Examples

Here are example queries to get you started. Feel free to copy these and change what you need.

Latest Student Feature Values

Specifying <STUDENT_ID> returns all the latest feature values for the student and the date they were calculated.

Note: Due to the different data types, the value will be in one of the text, int, float or bool columns.

SELECT
  event_ts AS score_date
  , institution_person_id
  , civ_continuation_feature_id
  , civ_continuation_feature_name
  , civ_continuation_feature_description
  , text_value
  , int_value
  , float_value
  , bool_value
FROM data_mart.fact_continuation_feature_latest
  INNER JOIN data_mart.dim_continuation_feature USING (dim_continuation_feature_id, institution_id)
  INNER JOIN data_mart.dim_person USING (dim_person_id, institution_id)
  INNER JOIN data_mart.dim_date USING (dim_date_id, institution_id)
WHERE
  institution_person_id = '<STUDENT_ID>'
ORDER BY civ_continuation_feature_id;

Student Continuation Predictions

This query fetches the latest continuation prediction score, the institution ID, and the date the score was generated.

SELECT
 scores.event_ts AS score_date
 , person.institution_person_id
 , scores.civ_career
 , scores.prediction
FROM data_mart.fact_continuation_scores scores
 INNER JOIN data_mart.dim_person person USING (dim_person_id, institution_id)
ORDER BY person.institution_person_id, scores.event_ts DESC;

Single Student Ranked Features

important

Individual feature ranked (IFR) functionality is not included with standard warehouse deployments. If you would like to incorporate IFR, please inquire with Sales.

This query returns the Civitas model features, ranked by importance for the given student.

Specifying <STUDENT_ID> returns the individual feature ranked (IFR) features for the student, ranked by importance. The most important feature has a rank of 1.

Note: This query returns only features for which an IFR value was calculated.

SELECT
  feature_value.event_ts AS score_date
  , person.institution_person_id
  , feature.civ_continuation_feature_id
  , feature.civ_continuation_feature_name
  , feature.civ_continuation_feature_description
  , feature_value.text_value
  , feature_value.int_value
  , feature_value.float_value
  , feature_value.bool_value
  , ifr.feature_rank -- Most important will be ranked 1
FROM data_mart.fact_continuation_feature_latest feature_value
  INNER JOIN data_mart.dim_continuation_feature feature USING (dim_continuation_feature_id, institution_id)
  INNER JOIN data_mart.dim_person person USING (dim_person_id, institution_id)
  INNER JOIN data_mart.fact_continuation_if ifr USING (event_ts, dim_continuation_feature_id, dim_person_id, dim_date_id, institution_id)
WHERE
  person.institution_person_id = '<STUDENT_ID>'
ORDER BY feature.civ_continuation_feature_id;

Student Completion Predictions

Specifying '<STUDENT_ID>' returns the latest 4-year completion prediction for the student. Enrollment flag and actual completion outcome can also be obtained by joining with other tables in the data warehouse.

-- Find the terms a student has an active enrollment in for each of their careers (academic levels).
WITH student_enrollment AS (
  SELECT DISTINCT
    enrollment.raw_person_id
    , enrollment.civ_career_id AS civ_career
    , section.civ_term_id
    , enrollment.civ_is_enrolled
  FROM data_mart.fact_enroll enroll
    INNER JOIN data_mart.dim_enrollment enrollment USING (dim_enrollment_id)
    INNER JOIN data_mart.dim_section section USING (dim_section_id)
  WHERE
    enrollment.dim_enrollment_id != 'null_row'
    AND section.dim_section_id != 'null_row'
    AND enrollment.civ_is_enrolled = 1
)

-- Find the earliest term the student has graduated in for each of their careers (academic levels).
, earliest_completion_term AS (
  SELECT
    person.raw_person_id
    , term.civ_career_id AS civ_career
    , MIN(term.civ_career_term_id) AS completion_career_term_id
  FROM data_mart.fact_graduation graduation
    INNER JOIN data_mart.dim_person person USING (dim_person_id)
    INNER JOIN data_mart.dim_career_term term USING (dim_career_term_id)
  WHERE
    graduation.civ_is_award_conferred = 1
  GROUP BY person.raw_person_id, term.civ_career_id
)

SELECT
  scores.event_ts AS score_date
  , scores.civ_term_id AS score_term
  , scores.civ_career
  , person.institution_person_id
  , scores.prediction
  , COALESCE(student_enrollment.civ_is_enrolled, 0) AS civ_is_enrolled
  , CASE
      WHEN earliest_completion_term.completion_career_term_id <= completion.civ_end_term_sequence_no
       THEN 1
      ELSE 0
    END AS civ_is_completed
FROM data_mart.fact_completion_scores_latest scores
  INNER JOIN data_mart.dim_person person USING (dim_person_id, institution_id)
  INNER JOIN data_mart.dim_completion_window completion USING (dim_completion_window_id, institution_id, civ_career)
  LEFT JOIN student_enrollment USING (raw_person_id, civ_career, civ_term_id)
  LEFT JOIN earliest_completion_term USING (raw_person_id, civ_career)
WHERE
  scores.window_length = 4 -- Adjust this for the graduation window you would like to use (4 year, 5 year, etc.). To see your configured windows: SELECT DISTINCT window_length FROM data_mart.dim_completion_window;
  AND person.institution_person_id = '<STUDENT_ID>';

Latest Student Feature Values#

Student Continuation Predictions#

Single Student Ranked Features#

important

Student Completion Predictions#

Latest Student Feature Values

Student Continuation Predictions

Single Student Ranked Features

Student Completion Predictions