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
fcfl.event_ts AS score_date
, dp.institution_person_id
, dcf.civ_continuation_feature_id
, dcf.civ_continuation_feature_name
, dcf.civ_continuation_feature_description
, fcfl.text_value
, fcfl.int_value
, fcfl.float_value
, fcfl.bool_value
FROM data_mart.fact_continuation_feature_latest fcfl
INNER JOIN data_mart.dim_continuation_feature dcf USING (dim_continuation_feature_id, institution_id)
INNER JOIN data_mart.dim_person dp USING (dim_person_id, institution_id)
INNER JOIN data_mart.dim_date dd USING (dim_date_id, institution_id)
WHERE
dp.institution_person_id = '<STUDENT_ID>'
ORDER BY dcf.civ_continuation_feature_id;

Student Continuation Predictions

This query fetches all historical continuation prediction scores, the institution ID, and the date the score was generated for desired <STUDENT_ID>.

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.institution_person_id = '<STUDENT_ID>'
ORDER BY dp.institution_person_id, fcs.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
fcfl.event_ts AS score_date
, dp.institution_person_id
, dcf.civ_continuation_feature_id
, dcf.civ_continuation_feature_name
, dcf.civ_continuation_feature_description
, fcfl.text_value
, fcfl.int_value
, fcfl.float_value
, fcfl.bool_value
, fci.feature_rank -- Most important will be ranked 1
FROM data_mart.fact_continuation_feature_latest fcfl
INNER JOIN data_mart.dim_continuation_feature dcf USING (dim_continuation_feature_id, institution_id)
INNER JOIN data_mart.dim_person dp USING (dim_person_id, institution_id)
INNER JOIN data_mart.fact_continuation_ifr fci USING (event_ts, dim_continuation_feature_id, dim_person_id, dim_date_id, institution_id)
WHERE
dp.institution_person_id = '<STUDENT_ID>'
ORDER BY dcf.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
de.raw_person_id
, de.civ_career_id AS civ_career
, ds.civ_term_id
, de.civ_is_enrolled
FROM data_mart.fact_enroll fe
INNER JOIN data_mart.dim_enrollment de USING (dim_enrollment_id)
INNER JOIN data_mart.dim_section ds USING (dim_section_id)
WHERE
de.dim_enrollment_id != 'null_row'
AND ds.dim_section_id != 'null_row'
AND de.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
dp.raw_person_id
, dct.civ_career_id AS civ_career
, MIN(dct.civ_career_term_id) AS completion_career_term_id
FROM data_mart.fact_graduation fg
INNER JOIN data_mart.dim_person dp USING (dim_person_id)
INNER JOIN data_mart.dim_career_term dct USING (dim_career_term_id)
WHERE
fg.civ_is_award_conferred = 1
GROUP BY dp.raw_person_id, dct.civ_career_id
)
SELECT
fcsl.event_ts AS score_date
, fcsl.civ_term_id AS score_term
, fcsl.civ_career
, dp.institution_person_id
, fcsl.prediction
, COALESCE(se.civ_is_enrolled, 0) AS civ_is_enrolled
, CASE
WHEN ect.completion_career_term_id <= dcw.civ_end_term_sequence_no
THEN 1
ELSE 0
END AS civ_is_completed
FROM data_mart.fact_completion_scores_latest fcsl
INNER JOIN data_mart.dim_person dp USING (dim_person_id, institution_id)
INNER JOIN data_mart.dim_completion_window dcw USING (dim_completion_window_id, institution_id, civ_career)
LEFT JOIN student_enrollment se USING (raw_person_id, civ_career, civ_term_id)
LEFT JOIN earliest_completion_term ect USING (raw_person_id, civ_career)
WHERE
fcsl.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 dp.institution_person_id = '<STUDENT_ID>';

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;
Edit this page