Analytics, engineeredlike software.

1-- FTIC Cohort Retention Analysis View
2-- Tracks student retention across 4 years by federal cohort
3
4WITH cohort_base AS (
5  SELECT DISTINCT
6    ost.sttr_student AS studentid,
7    LEFT(sal.sta_fed_cohort_group, 2)::integer AS cohortterm,
8    pnc.gender,
9    xie.ipeds_ethnicity AS raceethnicity,
10    pnc.first_name AS firstname,
11    pnc.last_name AS lastname
12  FROM dbo.ods_student_terms ost
13  JOIN dbo.spt_student_acad_levels sal
14    ON sal.sta_student = ost.sttr_student
15  JOIN dbo.spt_person_non_corp pnc
16    ON pnc.id = ost.sttr_student
17  JOIN dbo.x_ipeds_ethnicity xie
18    ON xie.id = ost.sttr_student
19  WHERE sal.sta_fed_cohort_group IN (
20    '21FF', '21FP', '22FF', '22FP',
21    '23FF', '23FP', '24FF', '24FP'
22  )
23),
24
25returns_by_term AS (
26  SELECT sttr_student, sttr_term
27  FROM dbo.ods_student_terms
28  WHERE sttr_term IN (
29    '2022FA', '2023FA', '2024FA', '2025FA'
30  )
31  AND sttr_current_status IN ('R', 'T')
32  AND x_sttr_active_cred > 0
33  GROUP BY sttr_student, sttr_term
34)
35
36SELECT
37  c.studentid,
38  c.cohortterm,
39  CONCAT('20', c.cohortterm, '-', '20', c.cohortterm + 1)
40    AS academicyear,
41  c.gender,
42  c.raceethnicity,
43  CASE c.cohortterm
44    WHEN 21 THEN CASE WHEN r22.sttr_student IS NOT NULL
45      THEN 1 ELSE 0 END
46    WHEN 22 THEN CASE WHEN r23.sttr_student IS NOT NULL
47      THEN 1 ELSE 0 END
48    WHEN 23 THEN CASE WHEN r24.sttr_student IS NOT NULL
49      THEN 1 ELSE 0 END
50    WHEN 24 THEN CASE WHEN r25.sttr_student IS NOT NULL
51      THEN 1 ELSE 0 END
52  END AS oneyearretention,
53  CASE c.cohortterm
54    WHEN 21 THEN CASE WHEN r24.sttr_student IS NOT NULL
55      THEN 1 ELSE 0 END
56    WHEN 22 THEN CASE WHEN r25.sttr_student IS NOT NULL
57      THEN 1 ELSE 0 END
58  END AS threeyearretention,
59  CASE WHEN c.cohortterm = 21
60    AND r25.sttr_student IS NOT NULL
61    THEN 1 ELSE 0
62  END AS fouryearretention
63FROM cohort_base c
64LEFT JOIN returns_by_term r22
65  ON r22.sttr_student = c.studentid AND r22.sttr_term = '2022FA'
66LEFT JOIN returns_by_term r23
67  ON r23.sttr_student = c.studentid AND r23.sttr_term = '2023FA'
68LEFT JOIN returns_by_term r24
69  ON r24.sttr_student = c.studentid AND r24.sttr_term = '2024FA'
70LEFT JOIN returns_by_term r25
71  ON r25.sttr_student = c.studentid AND r25.sttr_term = '2025FA'
72ORDER BY c.cohortterm, c.lastname, c.firstname;

1-- Current Employees Roster View
2-- Comprehensive HR data with salary, demographics, and org structure
3
4WITH base AS (
5  SELECT
6    BTRIM(sp.ppwg_hrp_id::text) AS key_hrp,
7    sp.ppwg_base_et,
8    sp.ppwg_start_date,
9    sp.ppwg_end_date,
10    sp.ppwg_annualized_amt::numeric AS annualized_amt
11  FROM dbo.spt_perposwg sp
12  WHERE sp.ppwg_base_et::text = ANY (
13    ARRAY['REGU'::text, 'PARS'::text]
14  )
15),
16
17ranked_with_salary AS (
18  SELECT
19    b.key_hrp,
20    b.annualized_amt,
21    b.ppwg_start_date,
22    b.ppwg_end_date,
23    ROW_NUMBER() OVER (
24      PARTITION BY b.key_hrp
25      ORDER BY
26        (b.ppwg_end_date IS NULL) DESC,
27        b.ppwg_start_date DESC,
28        b.annualized_amt DESC
29    ) AS rn
30  FROM base b
31  WHERE b.annualized_amt IS NOT NULL
32    AND b.annualized_amt > 0::numeric
33),
34
35ranked_data AS (
36  SELECT
37    hrper.hrper_id,
38    person.last_name,
39    person.first_name,
40    hrper.hrp_effect_employ_date AS hired_date,
41    CASE
42      WHEN hrper.position_desc::text ~~* '%President%'
43        AND hrper.position_desc::text !~~* '%VP%'
44        THEN 'President'::text
45      WHEN hrper.position_desc::text ~* '^Prof[,.]?\s*(Bus|Business)\s*Admin'
46        THEN 'Professor of Business Administration'::text
47      -- ... 75+ additional CASE conditions ...
48      ELSE INITCAP(hrper.position_desc::text)
49    END AS position_desc_standardized,
50    CASE person.gender
51      WHEN 'M' THEN 'Male'
52      WHEN 'F' THEN 'Female'
53      ELSE 'Not Specified'
54    END AS gender,
55    CASE
56      WHEN person.age >= 79 THEN 'Silent Generation'
57      WHEN person.age >= 60 THEN 'Baby Boomer'
58      WHEN person.age >= 44 THEN 'Generation X'
59      WHEN person.age >= 28 THEN 'Millennial'
60      WHEN person.age >= 12 THEN 'Generation Z'
61      ELSE 'Generation Alpha'
62    END AS generation_label,
63    COALESCE(perstat.perstat_former_service_years, 0::numeric)
64      + COALESCE(hrper.hrp_service_years, 0::numeric)
65      AS total_service_years,
66    pc.salary,
67    ROW_NUMBER() OVER (
68      PARTITION BY hrper.hrper_id
69      ORDER BY
70        CASE WHEN perstat.perstat_former_service_years IS NOT NULL
71          THEN 0 ELSE 1 END
72    ) AS rn
73  FROM dbo.ods_hrper hrper
74  JOIN dbo.ods_person person ON hrper.hrper_id = person.id
75  LEFT JOIN dbo.ods_depts d ON d.depts_id = hrper.hrp_pri_dept_sort
76  LEFT JOIN dbo.spt_perstat perstat ON hrper.hrper_id = perstat.perstat_hrp_id
77  LEFT JOIN ranked_with_salary pc ON pc.key_hrp = hrper.hrper_id
78  WHERE (hrper.hrp_current_status <> ALL (ARRAY['ST','TE','TM']))
79    AND (hrper.hrp_effect_term_date > CURRENT_DATE
80      OR hrper.hrp_effect_term_date IS NULL)
81)
82
83SELECT * FROM ranked_data
84WHERE rn = 1
85ORDER BY last_name, first_name;

1* a3f1c20 (HEAD -> main, origin/main) Merge pull request: FTIC retention view
2|\
3| * 9d02b7e test: reconcile retention counts against census snapshot
4| * 4be81aa feat: fall-to-fall retention CTE (claude-paired, reviewed)
5|/
6* 8b2e4d9 refactor: IPEDS race/ethnicity rollup view
7* 1c9a07f docs: headcount vs. FTE definitions in decisions.md
8* 77c3e15 feat(model): bridge table for multi-value fields
9* 0f4d2a8 ci: block merges when the legacy comparison fails
10* 3e9b6c1 chore: column catalog regenerated (schema only, no rows)
11
12repo/
13├── schema/          column catalogs, never data
14├── views/           one CTE, one job
15├── models/          PBIP / TMDL semantic models
16├── tests/           comparison against the legacy system
17└── docs/decisions.md   the why, not the what