public.student_common_demographics

This is a view that makes querying some common student fields easier.

Columns

SELECT
  student_id,
  is_gate,
  is_specialed,
  sed,
  gender,
  is_hispanic,
  primary_ethnicity,
  english_proficiency,
  is_title_i,
  ell_program_id
FROM public.student_common_demographics
LIMIT 10

Gotchas

  • student_common_demographics relies on current data only

Examples

-- Get special ed students primary ethnicity and english proficiency
SELECT
  students.student_id,
  students.last_name,
  students.first_name,
  eng_prof.code_translation AS stu_english_proficiency,
  eth.code_translation AS stu_primary_ethnicity
FROM student_common_demographics demo
JOIN students USING (student_id)
LEFT JOIN codes.english_proficiency eng_prof
  ON eng_prof.code_id = demo.english_proficiency
LEFT JOIN codes.ethnicity eth ON eth.code_id = demo.primary_ethnicity
WHERE is_specialed IS TRUE
LIMIT 20

public.race_ethnicity_combined

This is a view that returns the nation combined race/ethnicity.

American Indian or Alaska Native
Asian
Black or African American
Filipino
Hispanic
Native Hawaiian or Other Pacific Islander
Two or More Races
White

Columns

SELECT
  student_id,
  combined_race_ethnicity_code,
  combined_race_ethnicity
FROM race_ethnicity_combined
LIMIT 10

Examples

-- Get students combined race ethnicity
SELECT
  student_id,
  local_student_id,
  last_name,
  first_name,
  combined_race_ethnicity_code,
  combined_race_ethnicity
FROM students
JOIN race_ethnicity_combined USING (student_id)
LIMIT 10

Want some more examples? Send me some suggestions of the data you would like to see zach@illuminateed.com