Persistence Prediction Queries

Average Persistence Score by Origin

This query calculates the average persistence prediction score for Domestic, International, and Unknown students. It uses the latest prediction for each student and only includes students with active enrollments.

WITH latest_prediction_per_student AS (
-- Get the latest persistence prediction for each student
SELECT
dp.raw_person_id
, dp.institution_person_id
, dp.raw_country_of_origin
, fcs.prediction AS latest_prediction
, fcs.civ_career
FROM data_mart.fact_continuation_scores AS fcs
INNER JOIN data_mart.dim_person AS dp
ON fcs.dim_person_id = dp.dim_person_id
QUALIFY ROW_NUMBER() OVER (
PARTITION BY
dp.institution_person_id
ORDER BY
fcs.event_ts DESC
) = 1
)
, active_students AS (
-- Filter for students with active enrollments
SELECT DISTINCT
de.raw_person_id
, de.civ_career_id
FROM data_mart.dim_enrollment AS de
INNER JOIN data_mart.dim_section AS ds
ON de.raw_section_id = ds.raw_section_id
INNER JOIN data_mart.dim_career_term AS dct
ON ds.raw_term_id = dct.raw_term_id
WHERE
dct.is_active = 1
AND de.raw_career_id <> 'Not Available'
--AND de.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
AND de.civ_is_enrolled = 1
)
-- Classify students and calculate averages
SELECT
CASE
WHEN lpps.raw_country_of_origin IN (
'American', 'USA', 'United States', 'US', 'U.S.A.', 'U.S.', 'America'
) THEN 'Domestic'
WHEN lpps.raw_country_of_origin IN (
'Not Applicable'
, 'Not Available'
, 'Unknown'
, 'N/A'
, 'NA'
, 'None'
, 'Unspecified'
, ''
) OR lpps.raw_country_of_origin IS NULL THEN 'Unknown'
ELSE 'International'
END AS country_of_origin
, AVG(lpps.latest_prediction) AS avg_persistence_score
, COUNT(*) AS student_count
FROM latest_prediction_per_student AS lpps
INNER JOIN active_students AS acts
ON lpps.raw_person_id = acts.raw_person_id
AND lpps.civ_career = acts.civ_career_id
GROUP BY
country_of_origin
ORDER BY
country_of_origin;

Average Persistence or Persistence Score by GPA Band

This query calculates the average persistence prediction by GPA Band and categorizes the persistence likelihood into five levels: Very High, High, Moderate, Low, and Very Low.

It also provides the count of students in each GPA Band used for the aggregated predictions.

You can modify the GPA ranges in the gpa_data CASE statement as per your requirements.

-- Get the latest persistence prediction for each student
WITH latest_prediction_per_student AS (
SELECT
dp.raw_person_id
, dp.institution_person_id
, fcs.prediction AS latest_prediction
, de.civ_career_id
FROM
data_mart.fact_continuation_scores AS fcs
INNER JOIN
data_mart.dim_person AS dp
ON fcs.dim_person_id = dp.dim_person_id
INNER JOIN data_mart.dim_enrollment AS de
ON dp.raw_person_id = de.raw_person_id
WHERE de.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
QUALIFY
-- Keep only the latest prediction
ROW_NUMBER() OVER (
PARTITION BY
dp.institution_person_id
ORDER BY
fcs.event_ts DESC
) = 1
)
, gpa_data AS (
-- Map students to GPA bands based on their raw GPA values
SELECT
dp.institution_person_id
, CASE
WHEN fpg.raw_gpa >= 0.00 AND fpg.raw_gpa < 0.50 THEN '0.0-0.5'
WHEN fpg.raw_gpa >= 0.50 AND fpg.raw_gpa < 1.00 THEN '0.5-1.0'
WHEN fpg.raw_gpa >= 1.00 AND fpg.raw_gpa < 1.50 THEN '1.0-1.5'
WHEN fpg.raw_gpa >= 1.50 AND fpg.raw_gpa < 2.00 THEN '1.5-2.0'
WHEN fpg.raw_gpa >= 2.00 AND fpg.raw_gpa < 2.50 THEN '2.0-2.5'
WHEN fpg.raw_gpa >= 2.50 AND fpg.raw_gpa < 3.00 THEN '2.5-3.0'
WHEN fpg.raw_gpa >= 3.00 AND fpg.raw_gpa < 3.50 THEN '3.0-3.5'
WHEN fpg.raw_gpa >= 3.50 AND fpg.raw_gpa < 4.00 THEN '3.5-4.0'
WHEN fpg.raw_gpa >= 4.00 THEN '>= 4.0'
END AS gpa_band
FROM
data_mart.dim_person AS dp
INNER JOIN
data_mart.fact_provided_gpa AS fpg
ON dp.dim_person_id = fpg.dim_person_id
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY
dp.institution_person_id
ORDER BY
fpg.event_ts DESC
) = 1
)
, plan_stats AS (
-- Calculate average persistence prediction and student count per GPA band
SELECT
gd.gpa_band
, lpps.civ_career_id
, AVG(lpps.latest_prediction) AS avg_prediction_per_band -- Average persistence prediction
, COUNT(DISTINCT gd.institution_person_id) AS student_count -- Count of distinct students
FROM
latest_prediction_per_student AS lpps
INNER JOIN
gpa_data AS gd
ON lpps.institution_person_id = gd.institution_person_id
WHERE
lpps.raw_person_id IN (
-- Filter for active enrollments
SELECT DISTINCT de.raw_person_id
FROM
data_mart.dim_enrollment AS de
INNER JOIN
data_mart.dim_section AS ds
ON de.raw_section_id = ds.raw_section_id
INNER JOIN
data_mart.dim_career_term AS dct
ON ds.raw_term_id = dct.raw_term_id
WHERE
dct.is_active = 1 -- Only active terms
AND de.raw_career_id <> 'Not Available' -- Exclude invalid career IDs
AND de.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
AND de.civ_is_enrolled = 1 -- Only currently enrolled students
)
GROUP BY
gd.gpa_band, lpps.civ_career_id
ORDER BY
gd.gpa_band, lpps.civ_career_id
)
SELECT
ps.civ_career_id
, ps.gpa_band
, student_count
, TO_CHAR((ps.avg_prediction_per_band) * 100, 'FM999.00')
|| '%' AS avg_prediction
, CASE -- Categorize persistence likelihood
WHEN ps.avg_prediction_per_band >= 0.90 THEN 'Very High'
WHEN ps.avg_prediction_per_band >= 0.70 THEN 'High'
WHEN ps.avg_prediction_per_band >= 0.50 THEN 'Moderate'
WHEN ps.avg_prediction_per_band >= 0.20 THEN 'Low'
WHEN ps.avg_prediction_per_band < 0.20 THEN 'Very Low'
END AS persistence_likelihood
FROM
plan_stats AS ps
ORDER BY
ps.gpa_band DESC, ps.civ_career_id;

Average Persistence Score by Term

This query calculates the average persistence score for each term. It also includes the average score by course. Uncomment the WHERE clause filters to limit the results to specific campuses, institutions, departments, subjects, or catalog numbers as needed.

WITH score_per_student_per_term AS (
SELECT
dp.raw_person_id
, fcs.civ_career
, fcs.civ_term_id
, fcs.prediction
FROM data_mart.fact_continuation_scores fcs
INNER JOIN data_mart.dim_person dp ON
fcs.dim_person_id = dp.dim_person_id
WHERE fcs.event_ts = (
SELECT MAX(fcs1.event_ts)
FROM data_mart.fact_continuation_scores fcs1
INNER JOIN data_mart.dim_person dp1 ON
fcs1.dim_person_id = dp1.dim_person_id
WHERE dp.raw_person_id = dp1.raw_person_id
AND fcs.civ_career = fcs1.civ_career
AND fcs.civ_term_id = fcs1.civ_term_id
)
)
, average_score_per_course AS (
SELECT
ds.civ_course_id
, de.civ_career_id
, AVG(spspt.prediction) AS total_average_score
FROM data_mart.fact_enroll fe
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 score_per_student_per_term spspt ON
de.raw_person_id = spspt.raw_person_id
AND ds.civ_term_id = spspt.civ_term_id
AND de.civ_career_id = spspt.civ_career
GROUP BY
ds.civ_course_id
, de.civ_career_id
)
SELECT
ds.civ_course_id
, ds.raw_title AS course_title
, dct.civ_term_id
, ac.total_average_score AS all_time_average_score
, AVG(spspt.prediction) AS average_score_by_term
FROM data_mart.fact_enroll fe
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 data_mart.dim_career_term dct ON
ds.civ_term_id = dct.civ_term_id
AND de.civ_career_id = dct.civ_career_id
INNER JOIN score_per_student_per_term spspt ON
de.raw_person_id = spspt.raw_person_id
AND ds.civ_term_id = spspt.civ_term_id
AND de.civ_career_id = spspt.civ_career
INNER JOIN average_score_per_course ac ON
ds.civ_course_id = ac.civ_course_id
AND de.civ_career_id = ac.civ_career_id
WHERE 1 = 1
--AND dct.is_active = 1
--AND ds.raw_campus_id = '<CAMPUS_ID>'
--AND ds.raw_institution_id = '<INSTITUTION_ID>'
--AND ds.raw_department_id = '<DEPARTMENT_ID>'
--AND dc.raw_subject = '<SUBJECT>'
--AND dc.raw_catalog_nbr = '<CATALOG_NBR>'
GROUP BY
ds.civ_course_id
, ds.raw_title
, dct.civ_term_id
, ac.total_average_score
ORDER BY
ds.civ_course_id DESC
, dct.civ_term_id ASC;

Dropped Students with Low Persistence Scores

This query aims to provide a list of students who have recently dropped at least one course in the active term and have a low persistence score. We are considering low prediction scores with values below 0.5. Users are able to adjust the threshold as desired.

WITH latest_continuation_score AS (
SELECT
fcs.dim_person_id
, fcs.civ_career
, fcs.civ_term_id
, fcs.event_ts
, fcs.prediction
FROM data_mart.fact_continuation_scores fcs
WHERE fcs.event_ts = (
SELECT
MAX(fcs1.event_ts)
FROM data_mart.fact_continuation_scores fcs1
WHERE
fcs.dim_person_id = fcs1.dim_person_id
AND fcs.civ_career = fcs1.civ_career
AND fcs.civ_term_id = fcs1.civ_term_id
) -- This step ensure that the query returns the latest score for each student.
AND fcs.prediction < 0.5 -- Adjust persistence score threshold as desired here.
)
SELECT DISTINCT
dp.raw_person_id AS person_id
, ds.civ_term_id
, de.raw_drop_dt
, de.raw_drop_dt - de.raw_add_dt AS days_enrolled
, ds.civ_course_id
, ds.raw_title AS course_title
, lcs.prediction
FROM data_mart.fact_enroll fe
INNER JOIN data_mart.dim_person dp ON
dp.dim_person_id = fe.dim_person_id
INNER JOIN data_mart.dim_section ds ON
fe.dim_section_id = ds.dim_section_id
INNER JOIN data_mart.dim_enrollment de ON
fe.dim_enrollment_id = de.dim_enrollment_id
AND de.raw_person_id = dp.raw_person_id
INNER JOIN data_mart.dim_career_term dct ON
dct.civ_term_id = ds.civ_term_id
INNER JOIN latest_continuation_score lcs ON
lcs.dim_person_id = dp.dim_person_id
AND lcs.civ_term_id = ds.civ_term_id
WHERE
de.raw_drop_dt >= dct.civ_career_term_start_dt
AND de.raw_drop_dt <= dct.civ_career_term_end_dt
AND dct.is_active = 1
ORDER BY
de.raw_drop_dt DESC
, dp.institution_person_id;

High GPA Students with low persistence scores

-- This query identifies students who are currently enrolled (active) and have: High GPA (3.0 or higher) Low persistence score (below 50%)

WITH latest_continuation_score AS (
SELECT
dp.raw_person_id
, fcs.civ_career
, fcs.civ_term_id
, fcs.prediction
FROM data_mart.fact_continuation_scores fcs
INNER JOIN data_mart.dim_person dp ON
fcs.dim_person_id = dp.dim_person_id
WHERE fcs.event_ts = (
SELECT MAX(fcs1.event_ts)
FROM data_mart.fact_continuation_scores fcs1
INNER JOIN data_mart.dim_person dp1 ON
fcs1.dim_person_id = dp1.dim_person_id
WHERE
dp.raw_person_id = dp1.raw_person_id
AND fcs.civ_career = fcs1.civ_career
AND fcs.civ_term_id = fcs1.civ_term_id
)
AND fcs.prediction < 0.5
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY
dp.raw_person_id
, fcs.civ_career
, fcs.civ_term_id
ORDER BY
fcs.event_ts DESC
, fcs.dim_person_id DESC
) = 1
)
, cte_gpa AS (
SELECT
dp.raw_person_id
, AVG(fpg.raw_gpa) AS avg_gpa
FROM data_mart.fact_provided_gpa fpg
INNER JOIN data_mart.dim_person dp ON
fpg.dim_person_id = dp.dim_person_id
GROUP BY dp.raw_person_id, fpg.event_ts
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY
dp.raw_person_id
ORDER BY
fpg.event_ts DESC
) = 1
)
, active_students AS (
SELECT DISTINCT
de.raw_person_id
, de.civ_career_id
, de.institution_id
, dct.civ_term_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
ds.raw_term_id = dct.raw_term_id
AND de.civ_career_id = dct.civ_career_id
WHERE dct.is_active = 1 -- Active terms only
AND de.raw_career_id <> 'Not Available'
AND de.civ_is_enrolled = 1 -- Only enrolled students
)
SELECT
dp.raw_person_id
, lcs.civ_career
, lcs.civ_term_id
, lcs.prediction AS continuation_score
, ROUND(cte_gpa.avg_gpa, 2) AS avg_gpa
FROM latest_continuation_score lcs
INNER JOIN data_mart.dim_person dp ON
lcs.raw_person_id = dp.raw_person_id
AND dp.raw_eff_ts_end > CURRENT_DATE -- Current/active records only
INNER JOIN cte_gpa ON
dp.raw_person_id = cte_gpa.raw_person_id
INNER JOIN active_students acs ON
dp.raw_person_id = acs.raw_person_id
AND lcs.civ_career = acs.civ_career_id
AND dp.institution_id = acs.institution_id
AND lcs.civ_term_id = acs.civ_term_id
WHERE cte_gpa.avg_gpa >= 3.0
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY
dp.raw_person_id
ORDER BY dp.raw_eff_ts DESC
) = 1
ORDER BY
cte_gpa.avg_gpa DESC
, lcs.prediction ASC
, dp.raw_person_id ASC;

Percentile Binned Students by Persistence Score

This query categorizes active students into 10 percentile groups (deciles) based on their latest persistence score prediction.

Notes:

  • Percentiles are based only on currently active students.
  • The latest persistence score is used for each student.
-- Get the latest persistence prediction for each student
WITH latest_prediction_per_student AS (
SELECT
dp.raw_person_id
, dp.institution_person_id
, fcs.prediction AS latest_prediction
FROM data_mart.fact_continuation_scores AS fcs
INNER JOIN data_mart.dim_person AS dp
ON fcs.dim_person_id = dp.dim_person_id
QUALIFY ROW_NUMBER() OVER (
PARTITION BY
dp.institution_person_id
ORDER BY
fcs.event_ts DESC
) = 1
)
, active_students AS (
-- Filter for active enrollments
SELECT DISTINCT de.raw_person_id
FROM data_mart.dim_enrollment AS de
INNER JOIN data_mart.dim_section AS ds
ON de.raw_section_id = ds.raw_section_id
INNER JOIN data_mart.dim_career_term AS dct
ON ds.raw_term_id = dct.raw_term_id
WHERE
dct.is_active = 1
AND de.raw_career_id <> 'Not Available'
--AND de.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
AND de.civ_is_enrolled = 1
)
, percentile_binned_students AS (
-- Assign each student into one of 10 bins based on persistence score
SELECT
lpps.institution_person_id
, lpps.latest_prediction
, NTILE(10) OVER (
ORDER BY
lpps.latest_prediction
) AS percentile_group
FROM latest_prediction_per_student AS lpps
INNER JOIN active_students AS ast
ON lpps.raw_person_id = ast.raw_person_id
)
-- Final output with bin label
SELECT
pbs.institution_person_id
, TO_CHAR(pbs.latest_prediction * 100, 'FM999.00') || '%' AS student_persistence_score
, CASE percentile_group
WHEN 1 THEN '0-10th Percentile' -- Lowest scoring group
WHEN 2 THEN '10-20th Percentile'
WHEN 3 THEN '20-30th Percentile'
WHEN 4 THEN '30-40th Percentile'
WHEN 5 THEN '40-50th Percentile'
WHEN 6 THEN '50-60th Percentile'
WHEN 7 THEN '60-70th Percentile'
WHEN 8 THEN '70-80th Percentile'
WHEN 9 THEN '80-90th Percentile'
WHEN 10 THEN '90-100th Percentile' -- Highest scoring group
END AS percentile_bin
FROM percentile_binned_students AS pbs
ORDER BY
percentile_group, latest_prediction;

Student Persistence Predictions

This query fetches all historical persistence 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;

Student Persistence Prediction by Age

This query calculates the average prediction score of students across different age buckets and counts the number of students in each bucket, based on the most recent persistence score date.

WITH latest_continuation_score AS (
SELECT
fcs1.dim_person_id
, fcs1.civ_career
, fcs1.event_ts
, fcs1.prediction
, fcs1.dim_date_id
, fcs1.civ_term_id
FROM data_mart.fact_continuation_scores fcs1
WHERE fcs1.event_ts = (SELECT MAX(fcs2.event_ts)
FROM data_mart.fact_continuation_scores fcs2)
)
, student_ages AS (
SELECT DISTINCT
dp.institution_person_id
, dp.institution_id
, dp.dim_person_id
, DATEDIFF(YEAR, dp.raw_birth_dt::DATE, CURRENT_DATE)
- (CASE -- Account for birthdays that have not occurred yet this year
WHEN TO_CHAR(CURRENT_DATE, 'MMDD') < TO_CHAR(dp.raw_birth_dt::DATE, 'MMDD')
THEN 1 ELSE 0
END) AS age
FROM data_mart.dim_person dp
WHERE age IS NOT NULL
)
, age_buckets AS (
SELECT
sa.institution_person_id,
sa.dim_person_id,
sa.institution_id,
fcs.prediction,
CASE
WHEN sa.age <= 18 THEN '18_and_under'
WHEN sa.age = 19 THEN '19'
WHEN sa.age = 20 THEN '20'
WHEN sa.age = 21 THEN '21'
WHEN sa.age = 22 THEN '22'
WHEN sa.age = 23 THEN '23'
WHEN sa.age = 24 THEN '24'
WHEN sa.age BETWEEN 25 AND 29 THEN '25 to 29'
WHEN sa.age BETWEEN 30 AND 34 THEN '30 to 34'
WHEN sa.age BETWEEN 35 AND 39 THEN '35 to 39'
ELSE '40_and_over'
END AS age_bucket
FROM student_ages sa
INNER JOIN data_mart.fact_continuation_scores fcs ON
sa.dim_person_id = fcs.dim_person_id
AND sa.institution_id = fcs.institution_id
INNER JOIN latest_continuation_score lcs ON
lcs.dim_person_id = fcs.dim_person_id
)
SELECT
ab.age_bucket,
COUNT(DISTINCT ab.institution_person_id) AS student_count,
AVG(ab.prediction) AS avg_prediction
FROM age_buckets ab
GROUP BY ab.age_bucket
ORDER BY
CASE ab.age_bucket
WHEN '18_and_under' THEN 1
WHEN '19' THEN 2
WHEN '20' THEN 3
WHEN '21' THEN 4
WHEN '22' THEN 5
WHEN '23' THEN 6
WHEN '24' THEN 7
WHEN '25 to 29' THEN 8
WHEN '30 to 34' THEN 9
WHEN '35 to 39' THEN 10
WHEN '40_and_over' THEN 11
END;

Student Persistence Prediction by Location

This query calculates the Student Persistence Prediction by Location. We will calculate the Persistence Prediction score by city, state, state_flag and zip_code

You can specify an State in which will generate a flag that will specify if the student is In State or Out of State

-- This state is the state that will indicate if the student is 'In State' or 'Out of State'.
WITH institution_state_variable AS (
SELECT 'MA' AS institution_state_value
)
, latest_continuation_score AS (
SELECT
dp.dim_person_id
, dp.institution_person_id
, fcs.civ_term_id
, dp.primary_state
, dp.primary_city
, dp.primary_zip
, fcs.event_ts
, fcs.prediction
FROM data_mart.fact_continuation_scores fcs
INNER JOIN data_mart.dim_person dp
ON fcs.dim_person_id = dp.dim_person_id
WHERE fcs.event_ts = (
SELECT MAX(fcs.event_ts)
FROM data_mart.fact_continuation_scores fcs
WHERE fcs.dim_person_id = fcs.dim_person_id
)
)
, continuation_score_by_location AS (
SELECT
lcs.institution_person_id
, lcs.primary_state
, lcs.primary_city
, lcs.primary_zip
, CASE
WHEN lcs.primary_state = (SELECT institution_state_value FROM institution_state_variable) THEN 'In State'
ELSE 'Out of State'
END AS state_flag
, AVG(lcs.prediction) AS student_persistence_prediction_score
, AVG(lcs.prediction) OVER (PARTITION BY lcs.primary_state) AS avg_persistence_prediction_by_state
, AVG(lcs.prediction) OVER (PARTITION BY state_flag) AS avg_persistence_prediction_by_in_state
, AVG(lcs.prediction) OVER (PARTITION BY lcs.primary_city) AS avg_persistence_prediction_by_city
, AVG(lcs.prediction) OVER (PARTITION BY lcs.primary_zip) AS avg_persistence_prediction_by_zip
, CASE
WHEN lcs.prediction >= 0.90 THEN 'Very High'
WHEN lcs.prediction >= 0.70 THEN 'High'
WHEN lcs.prediction >= 0.50 THEN 'Moderate'
WHEN lcs.prediction >= 0.20 THEN 'Low'
ELSE 'Very Low'
END AS student_completion_likelihood_category
FROM data_mart.fact_enroll fe
INNER JOIN data_mart.dim_person dp ON
dp.dim_person_id = fe.dim_person_id
INNER JOIN data_mart.dim_section ds ON
fe.dim_section_id = ds.dim_section_id
INNER JOIN data_mart.dim_career_term dct ON
dct.civ_term_id = ds.civ_term_id
INNER JOIN latest_continuation_score lcs ON
lcs.dim_person_id = dp.dim_person_id
AND lcs.civ_term_id = ds.civ_term_id
WHERE
CURRENT_DATE >= dp.raw_eff_ts
AND CURRENT_DATE < dp.raw_eff_ts_end
-- AND dct.is_active = 1 -- Students from active term only
GROUP BY
lcs.institution_person_id
, lcs.primary_state
, lcs.primary_city
, lcs.primary_zip
, lcs.prediction
)
SELECT
institution_person_id
, primary_state
, state_flag
, primary_city
, primary_zip
, student_persistence_prediction_score
, student_completion_likelihood_category
, avg_persistence_prediction_by_state
, avg_persistence_prediction_by_city
, avg_persistence_prediction_by_zip
FROM continuation_score_by_location
-- WHERE
-- institution_person_id = '<DESIRED_STUDENT_ID>'
-- AND primary_state = '<DESIRED_STATE>'
-- AND state_flag = 'In State'
-- AND primary_zip = '<DESIRED_ZIP>'
-- AND primary_city = '<DESIRED_CITY>'
-- AND student_persistence_prediction_score >= '<PERSISTENCE_PREDICTION>'

Besides that you can change the final select statement and get more details about the persistence prediction scores of students from a specific city, state, zip_code or in_state group.

-- Persistence prediction by state
SELECT
primary_state
, COUNT(*)
, MAX(avg_persistence_prediction_by_state) as avg_persistence_prediction_by_state
FROM continuation_score_by_location
WHERE primary_state = '<DESIRED_STATE>'
GROUP BY primary_state;
-- Persistence prediction by In State or Out of State flag
SELECT
state_flag
, COUNT(*)
, MAX(avg_persistence_prediction_by_in_state) as avg_persistence_prediction_by_in_state_flag
FROM continuation_score_by_location
GROUP BY state_flag;
-- Persistence prediction by zip code
SELECT
primary_zip
, COUNT(*)
, MAX(avg_persistence_prediction_by_zip) as avg_persistence_prediction_by_zip
FROM continuation_score_by_location
WHERE primary_zip = '<DESIRED_ZIP>'
GROUP BY primary_zip;

Student Persistence Score Shift

This query returns the latest persistence score for each student and career (academic level), and calculates the one day and one week shift in this score so that the customer can identify students whose score has changed recently. This query can be adapted to calculate score shifts other than one day or week.

The query also displays the Civitas defined bucket for the student's latest persistence score using the default ranges for these buckets: Very Low, Low, Moderate, High, Very High

-- Find the latest persistence score for each student and career.
WITH latest_continuation_score AS (
SELECT
fcs.dim_person_id
, fcs.civ_career
, fcs.civ_term_id
, fcs.event_ts
, fcs.prediction
FROM data_mart.fact_continuation_scores fcs
WHERE
-- This step ensures that the query returns the latest score for each student.
fcs.event_ts = (SELECT MAX(fcs1.event_ts)
FROM data_mart.fact_continuation_scores fcs1
WHERE fcs.dim_person_id = fcs1.dim_person_id
AND fcs.civ_career = fcs1.civ_career
AND fcs.civ_term_id = fcs1.civ_term_id)
-- This step ensures that the query returns scores for currently enrolled students only.
-- Only currently enrolled students will have a score on the latest day.
AND fcs.event_ts = (SELECT MAX(fcs2.event_ts)
FROM data_mart.fact_continuation_scores fcs2)
)
, continuation_score_shift AS (
-- Change the '7 days' or '1 day' thresholds below to use a different window for the score shift.
SELECT
lcs.dim_person_id
, lcs.civ_career
, lcs.civ_term_id
, lcs.event_ts
, lcs.prediction AS current_prediction
, fcs_shift_7.prediction AS previous_prediction_7_days
, fcs_shift_1.prediction AS previous_prediction_1_day
FROM latest_continuation_score lcs
LEFT JOIN data_mart.fact_continuation_scores fcs_shift_7 ON
lcs.dim_person_id = fcs_shift_7.dim_person_id
AND lcs.civ_career = fcs_shift_7.civ_career
AND lcs.civ_term_id = fcs_shift_7.civ_term_id
AND (lcs.event_ts - INTERVAL '7 days') = fcs_shift_7.event_ts
LEFT JOIN data_mart.fact_continuation_scores fcs_shift_1 ON
lcs.dim_person_id = fcs_shift_1.dim_person_id
AND lcs.civ_career = fcs_shift_1.civ_career
AND lcs.civ_term_id = fcs_shift_1.civ_term_id
AND (lcs.event_ts - INTERVAL '1 day') = fcs_shift_1.event_ts
)
SELECT
dp.institution_person_id
, css.civ_career AS civitas_career_id
, css.civ_term_id
, css.event_ts::DATE AS event_date
-- Note, these are the default ranges. They may have been adjusted for your institution.
, CASE
WHEN css.current_prediction >= 0.90 THEN 'Very High'
WHEN css.current_prediction >= 0.70 THEN 'High'
WHEN css.current_prediction >= 0.50 THEN 'Moderate'
WHEN css.current_prediction >= 0.20 THEN 'Low'
WHEN css.current_prediction < 0.20 THEN 'Very Low'
ELSE NULL
END AS persistence_likelihood -- Persistence Likelihood (Very High, High, Moderate, Low, Very Low)
, css.current_prediction AS persistence_prediction_score -- Persistence Prediction Score %
, css.current_prediction - css.previous_prediction_7_days AS weekly_prediction_score_shift -- Weekly Change in Persistence Score
, css.current_prediction - css.previous_prediction_1_day AS daily_prediction_score_shift --Daily Change in Persistence Score
FROM data_mart.dim_person dp
INNER JOIN continuation_score_shift css ON
dp.dim_person_id = css.dim_person_id
WHERE
CURRENT_DATE >= dp.raw_eff_ts
AND CURRENT_DATE < dp.raw_eff_ts_end
Edit this page