Examples

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

Student Features with Average

This query returns the Civitas model features for students, with population averages.

Specifying <STU_CODE> returns the latest features for a student along with the average for the population, which lets you determine if that student is under- or over-performing relative to the entire population.

Note: This provides averages only for those features that have Int or Float values.

with average_int_value as (
select
week_nbr_of_month
, civ_continuation_feature_id
, avg(int_value) as avg_int_value
from
data_mart.fact_continuation_feature
inner join data_mart.dim_continuation_feature using (dim_continuation_feature_id, institution_id)
inner join data_mart.dim_date using (dim_date_id, institution_id)
where
int_value is not null
group by week_nbr_of_month, civ_continuation_feature_id
), average_float_value as (
select
week_nbr_of_month
, civ_continuation_feature_id
, avg(float_value) as avg_float_value
from
data_mart.fact_continuation_feature
inner join data_mart.dim_continuation_feature using (dim_continuation_feature_id, institution_id)
inner join data_mart.dim_date using (dim_date_id, institution_id)
where
float_value is not null
group by week_nbr_of_month, civ_continuation_feature_id
)
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
, avg_int_value
, float_value
, avg_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)
left join average_int_value using (week_nbr_of_month, civ_continuation_feature_id)
left join average_float_value using (week_nbr_of_month, civ_continuation_feature_id)
where
institution_person_id = '<STU_CODE>'
order by institution_person_id, civ_continuation_feature_id

Student Features

Specifying <STU_CODE> 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 = '<STU_CODE>'
order by civ_continuation_feature_id;

Student Predictions

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

select
event_ts as score_date
, institution_person_id
, prediction
from
data_mart.fact_continuation_scores
inner join data_mart.dim_person using (dim_person_id, institution_id)

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 <STU_CODE> 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
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
, feature_rank -- Most important will be ranked 1
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)
inner join data_mart.fact_continuation_ifr using (event_ts, dim_continuation_feature_id, dim_person_id, dim_date_id, institution_id)
where
institution_person_id = '<STU_CODE>'
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.
```sql
select
event_ts as score_date
, institution_person_id
, prediction
from
data_mart.fact_continuation_scores
inner join data_mart.dim_person using (dim_person_id, institution_id)

Student Completion Predictions

This query fetches the latest completion prediction for a 4-year completion time window for all students who started in term Fall-2019. Enrollment flag and actual completion outcome can also be obtained by joining with other tables in the data warehouse.

with student_enrollment as (
select distinct
raw_person_id
, civ_career_id as civ_career
, civ_term_id
, civ_is_enrolled
from
data_mart.fact_enroll
inner join data_mart.dim_enrollment using (dim_enrollment_id)
inner join data_mart.dim_section using (dim_section_id)
where dim_enrollment.dim_enrollment_id != 'null_row'
and dim_section.dim_section_id != 'null_row'
and dim_enrollment.civ_is_enrolled = 1
),
earliest_completion_term as (
select
raw_person_id
, civ_career_id as civ_career
, min(civ_career_term_id) as completion_career_term_id
from
data_mart.fact_graduation
inner join data_mart.dim_person using (dim_person_id)
inner join data_mart.dim_career_term using (dim_career_term_id)
where civ_is_award_conferred = 1
group by dim_person.raw_person_id, dim_career_term.civ_career_id
)
select
event_ts as score_date
, civ_career
, civ_term_id
, institution_person_id
, prediction
, case when civ_is_enrolled = 1
then 1
else 0
end as civ_is_enrolled
, case when completion_career_term_id <= dim_completion_window.civ_end_term_sequence_no
then 1
else 0
end as civ_is_completed
from
data_mart.fact_completion_scores_latest
inner join data_mart.dim_person using (dim_person_id, institution_id)
inner join data_mart.dim_completion_window 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
fact_completion_scores_latest.civ_start_term_id = 'Fall-2019'
and fact_completion_scores_latest.window_length = 4
Edit this page