Student Feature Queries

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;

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;

Top and Bottom Percentile Section LMS Features Values

Identifies students' posting frequency, login activity, and academic performance relative to their section peers using z-score percentiles to flag at-risk behaviors. Provides engagement metrics including bottom/top 20% posting frequency and bottom quartile grades.

WITH feature_values_pivot AS (
SELECT *
FROM
(SELECT
fcfl.dim_person_id
, fcfl.civ_career_id
, dcf.civ_continuation_feature_id
, fcfl.float_value
FROM data_mart.fact_continuation_feature_latest fcfl
INNER JOIN data_mart.dim_continuation_feature dcf ON
fcfl.dim_continuation_feature_id = dcf.dim_continuation_feature_id
)
PIVOT (MAX(float_value) FOR civ_continuation_feature_id
-- Add any other feature you would like to include to the list below
-- bring it into the final query via the fvp CTE.
IN (
'avg_cnt_post_sum'
, 'avg_relative_avg_cnt_attendance_sum'
, 'zscore_cnt_post_sum_section_avg'
, 'avg_term_grade_percentage_cum'
, 'avg_relative_avg_section_grade_percentage_cum'
)
)
)
SELECT
dp.institution_person_id
, ds.raw_section_id
, ds.raw_course_id
, ds.raw_title
, fvp.avg_cnt_post_sum -- Posting Frequency Status
, fvp.avg_relative_avg_cnt_attendance_sum -- Login Frequency Status
, ds.raw_term_id -- Term Code
-- This is based on z-score percentiles for a normal distribution:
-- https://www.pindling.org/Math/Learning/Statistics/z_scores_table.htm
, ds.raw_start_dt
, ds.raw_end_dt
-- Class grade is in bottom quartile
, CASE
-- 20th percentile
WHEN fvp.zscore_cnt_post_sum_section_avg::FLOAT <= -0.842
THEN 1
ELSE 0
-- Posting Frequency Relative to Section (Bottom 20%,)
END AS posting_is_bottom_20_percent
, CASE
-- 80th percentile
WHEN fvp.zscore_cnt_post_sum_section_avg::FLOAT >= 0.842
THEN 1
ELSE 0
-- Posting frequency relative to section (Top 20%)
END AS posting_is_top_20_percent
, CASE
-- 25th percentile
WHEN fvp.avg_relative_avg_section_grade_percentage_cum::FLOAT
<= -0.674
THEN 1
ELSE 0
END AS grade_is_bottom_quartile
FROM data_mart.dim_person dp
INNER JOIN data_mart.fact_enroll fe ON
dp.dim_person_id = fe.dim_person_id
INNER JOIN data_mart.dim_enrollment de ON
fe.dim_enrollment_id = de.dim_enrollment_id
INNER JOIN data_mart.dim_section ds ON
fe.dim_section_id = ds.dim_section_id
INNER JOIN data_mart.dim_course dc ON
ds.raw_course_id = dc.raw_course_id
INNER JOIN feature_values_pivot fvp ON
dp.dim_person_id = fvp.dim_person_id
AND de.civ_career_id = fvp.civ_career_id
INNER JOIN data_mart.dim_career_term dct ON
de.raw_career_id = dct.raw_career_id
AND ds.raw_term_id = dct.raw_term_id
WHERE CURRENT_DATE >= dp.raw_eff_ts
AND CURRENT_DATE < dp.raw_eff_ts_end
AND dct.is_active = 1 -- Remove this line to include all terms
ORDER BY
dp.institution_person_id ASC
, ds.raw_section_id ASC
, ds.raw_course_id ASC
, ds.raw_term_id DESC;

Student Feature Peer Comparison

This query lets you compare each student’s value for a selected metric against the average of other active students in the same group.

By default: • Metric: GPA (civ_gpa_at) • Grouping: First by student type (civ_career_id: Graduate, Undergraduate, etc.), then by course section (raw_section_id)

Change the metric: You can replace civ_gpa_at in the query with any other available numeric metric.

Examples: • avg_cnt_attendance_sum → Average LMS activity days • avg_cnt_post_sum → Average discussion board posts • avg_relative_avg_cnt_attendance_sum → LMS activity relative to peers • avg_term_grade_percentage_cum → Average grade • avg_relative_avg_section_grade_percentage_cum → Grade relative to section average

To see all available features with numeric values, run:

SELECT DISTINCT
civ_continuation_feature_id
, civ_continuation_feature_name
, civ_continuation_feature_category
, civ_continuation_feature_description
FROM data_mart.dim_continuation_feature
ORDER BY civ_continuation_feature_id;

Change the grouping:

The first grouping level is always student type (civ_career_id). The second level can be changed to: • Section (raw_section_id) • Course (raw_course_id) • Course title (raw_title) • Major code (raw_major_id_mr)

Just comment/uncomment the desired line in the query so that only one ends with AS group_key.

Understanding results:

For each student, you’ll see: • Their selected metric • The chosen grouping • The group average • Difference from group average • Z-score (how many standard deviations away from the group average they are)

If there is no variation in the group, the z-score will be NULL.

Sorting: By default, results are ordered by student ID. To rank by best or worst performance: • Use ORDER BY zscore_from_group DESC → Best first • Use ORDER BY zscore_from_group ASC → Worst first (Remember to comment out the other ORDER BY lines.)

WITH feature_values_float AS (
SELECT
dim_person_id
, civ_career_id
, civ_gpa_at
FROM (
SELECT
fcfl.dim_person_id
, fcfl.civ_career_id
, dcf.civ_continuation_feature_id
, fcfl.float_value
FROM data_mart.fact_continuation_feature_latest fcfl
INNER JOIN data_mart.dim_continuation_feature dcf ON
fcfl.dim_continuation_feature_id
= dcf.dim_continuation_feature_id
) src
PIVOT (MAX(float_value) FOR civ_continuation_feature_id IN ('civ_gpa_at'))
)
, feature_values_text AS (
SELECT
dim_person_id
, civ_career_id
, raw_major_id_mr
FROM (
SELECT
fcfl.dim_person_id
, fcfl.civ_career_id
, dcf.civ_continuation_feature_id
, fcfl.text_value
FROM data_mart.fact_continuation_feature_latest fcfl
INNER JOIN data_mart.dim_continuation_feature dcf ON
fcfl.dim_continuation_feature_id
= dcf.dim_continuation_feature_id
) src
PIVOT (
MAX(text_value) FOR civ_continuation_feature_id IN ('raw_major_id_mr')
)
)
, feature_values_pivot AS (
SELECT
fvf.dim_person_id
, fvf.civ_career_id
, fvf.civ_gpa_at
, fvt.raw_major_id_mr
FROM feature_values_float fvf
LEFT JOIN feature_values_text fvt ON
fvf.dim_person_id = fvt.dim_person_id
AND fvf.civ_career_id = fvt.civ_career_id
)
SELECT DISTINCT
dp.institution_person_id
, fvp.civ_career_id
-- Comment the following line
-- and comment out your chosen partition
, ds.raw_section_id AS group_key
--, ds.raw_course_id AS group_key
--, ds.raw_title AS group_key
--, fvp.raw_major_id_mr AS group_key
, CAST(ROUND(fvp.civ_gpa_at, 3) AS DECIMAL(10, 3)) AS civ_gpa_at
, CAST(
ROUND(AVG(fvp.civ_gpa_at)
OVER (PARTITION BY fvp.civ_career_id, group_key), 3) AS DECIMAL(
10, 3
)
) AS group_avg
, CAST(
ROUND(fvp.civ_gpa_at - AVG(fvp.civ_gpa_at)
OVER (PARTITION BY group_key), 3) AS DECIMAL(10, 3)
) AS diff_from_group_avg
, CAST(ROUND(
CASE
WHEN
STDDEV_SAMP(fvp.civ_gpa_at)
OVER (PARTITION BY fvp.civ_career_id, group_key)
= 0
THEN NULL
ELSE
(
fvp.civ_gpa_at - AVG(fvp.civ_gpa_at)
OVER (PARTITION BY fvp.civ_career_id, group_key)
)
/ STDDEV_SAMP(fvp.civ_gpa_at)
OVER (PARTITION BY fvp.civ_career_id, group_key)
END, 3
) AS DECIMAL(10, 3)) AS zscore_from_group
FROM data_mart.dim_person dp
INNER JOIN data_mart.fact_enroll fe ON
dp.dim_person_id = fe.dim_person_id
INNER JOIN data_mart.dim_enrollment de ON
fe.dim_enrollment_id = de.dim_enrollment_id
INNER JOIN data_mart.dim_section ds ON
fe.dim_section_id = ds.dim_section_id
INNER JOIN data_mart.dim_course dc ON
ds.raw_course_id = dc.raw_course_id
INNER JOIN feature_values_pivot fvp ON
dp.dim_person_id = fvp.dim_person_id
AND de.civ_career_id = fvp.civ_career_id
INNER JOIN data_mart.dim_career_term dct ON
de.raw_career_id = dct.raw_career_id
AND ds.raw_term_id = dct.raw_term_id
WHERE
CURRENT_DATE >= dp.raw_eff_ts
AND CURRENT_DATE < dp.raw_eff_ts_end
AND dct.is_active = 1
ORDER BY dp.institution_person_id ASC;
--ORDER BY zscore_from_group DESC;
--ORDER BY zscore_from_group ASC;
Edit this page