ec_m AS ( SELECT ec.student_id, ec.entry_id, ec.date, ec.assess_window, ec.sy_start, ec.school_year, ec.age_group, m.measure, m.n, m.n_name FROM ec CROSS JOIN LATERAL (VALUES ('colors_rec', (SELECT COUNT(DISTINCT x.v)::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'colors_recognized') = 'array' THEN ec.data->'colors_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'colors_recognized') = 'string' THEN ec.data->'colors_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'colors_named') = 'array' THEN ec.data->'colors_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'colors_named') = 'string' THEN ec.data->'colors_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), 0), ('colors_name', (SELECT COUNT(DISTINCT x.v)::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'colors_named') = 'array' THEN ec.data->'colors_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'colors_named') = 'string' THEN ec.data->'colors_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), 0), ('shapes_rec', (SELECT COUNT(DISTINCT x.v)::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'shapes_recognized') = 'array' THEN ec.data->'shapes_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'shapes_recognized') = 'string' THEN ec.data->'shapes_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'shapes_named') = 'array' THEN ec.data->'shapes_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'shapes_named') = 'string' THEN ec.data->'shapes_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), 0), ('shapes_name', (SELECT COUNT(DISTINCT x.v)::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'shapes_named') = 'array' THEN ec.data->'shapes_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'shapes_named') = 'string' THEN ec.data->'shapes_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), 0), ('numbers_rec', (SELECT COUNT(DISTINCT x.v)::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'numbers_recognized') = 'array' THEN ec.data->'numbers_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'numbers_recognized') = 'string' THEN ec.data->'numbers_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'numbers_named') = 'array' THEN ec.data->'numbers_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'numbers_named') = 'string' THEN ec.data->'numbers_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), 0), ('numbers_name', (SELECT COUNT(DISTINCT x.v)::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'numbers_named') = 'array' THEN ec.data->'numbers_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'numbers_named') = 'string' THEN ec.data->'numbers_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), 0), ('count_rote', CASE WHEN btrim(ec.data->>'counting_rote') ~ '^[0-9]{1,6}$' THEN btrim(ec.data->>'counting_rote')::int ELSE 0 END, 0), ('count_one_to_one', CASE WHEN btrim(ec.data->>'counting_one_to_one') ~ '^[0-9]{1,6}$' THEN btrim(ec.data->>'counting_one_to_one')::int ELSE 0 END, 0), ('letters_rec', (SELECT COUNT(DISTINCT upper(x.v))::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'upper_recognized') = 'array' THEN ec.data->'upper_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'upper_recognized') = 'string' THEN ec.data->'upper_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'lower_recognized') = 'array' THEN ec.data->'lower_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'lower_recognized') = 'string' THEN ec.data->'lower_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'array' THEN ec.data->'upper_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'string' THEN ec.data->'upper_named' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'array' THEN ec.data->'lower_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'string' THEN ec.data->'lower_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), (SELECT COUNT(DISTINCT upper(x.v))::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'upper_recognized') = 'array' THEN ec.data->'upper_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'upper_recognized') = 'string' THEN ec.data->'upper_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'lower_recognized') = 'array' THEN ec.data->'lower_recognized' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'lower_recognized') = 'string' THEN ec.data->'lower_recognized' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'array' THEN ec.data->'upper_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'string' THEN ec.data->'upper_named' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'array' THEN ec.data->'lower_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'string' THEN ec.data->'lower_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> '' AND strpos(upper(COALESCE(ec.first_name, '')), upper(x.v)) > 0)), ('letters_name', (SELECT COUNT(DISTINCT upper(x.v))::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'array' THEN ec.data->'upper_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'string' THEN ec.data->'upper_named' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'array' THEN ec.data->'lower_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'string' THEN ec.data->'lower_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), (SELECT COUNT(DISTINCT upper(x.v))::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'array' THEN ec.data->'upper_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'upper_named') = 'string' THEN ec.data->'upper_named' #>> '{}' ELSE '' END, ',') e UNION ALL SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'array' THEN ec.data->'lower_named' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'lower_named') = 'string' THEN ec.data->'lower_named' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> '' AND strpos(upper(COALESCE(ec.first_name, '')), upper(x.v)) > 0)), ('letter_sounds', (SELECT COUNT(DISTINCT upper(x.v))::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'letter_sounds') = 'array' THEN ec.data->'letter_sounds' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'letter_sounds') = 'string' THEN ec.data->'letter_sounds' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> ''), (SELECT COUNT(DISTINCT upper(x.v))::int FROM (SELECT btrim(e) AS v FROM jsonb_array_elements_text(CASE WHEN jsonb_typeof(ec.data->'letter_sounds') = 'array' THEN ec.data->'letter_sounds' ELSE '[]'::jsonb END) e UNION ALL SELECT btrim(e) FROM regexp_split_to_table(CASE WHEN jsonb_typeof(ec.data->'letter_sounds') = 'string' THEN ec.data->'letter_sounds' #>> '{}' ELSE '' END, ',') e) x WHERE x.v <> '' AND strpos(upper(COALESCE(ec.first_name, '')), upper(x.v)) > 0)) ) AS m(measure, n, n_name) ), ec_x AS ( SELECT ec_m.*, x.min_n, x.label AS expected, x.in_name AS in_name, CASE WHEN x.min_n IS NULL THEN NULL ELSE (CASE WHEN x.in_name THEN ec_m.n_name ELSE ec_m.n END) >= x.min_n END AS met FROM ec_m LEFT JOIN (VALUES ('3/4', 'BOY', 'colors_rec', 2, false, '2'), ('3/4', 'BOY', 'colors_name', 2, false, '2'), ('3/4', 'BOY', 'shapes_rec', 1, false, '1'), ('3/4', 'BOY', 'shapes_name', 1, false, '1'), ('3/4', 'BOY', 'numbers_rec', 1, false, '1-2 between 0-10'), ('3/4', 'BOY', 'numbers_name', 1, false, '1 or 2 within 0-10'), ('3/4', 'BOY', 'count_rote', 3, false, 'Counts 1-3'), ('3/4', 'BOY', 'letters_rec', 1, true, '1 letter in name'), ('3/4', 'MOY', 'colors_rec', 4, false, '4'), ('3/4', 'MOY', 'colors_name', 4, false, '4'), ('3/4', 'MOY', 'shapes_rec', 2, false, '2'), ('3/4', 'MOY', 'shapes_name', 2, false, '2'), ('3/4', 'MOY', 'numbers_rec', 2, false, '2-3 between 0-10'), ('3/4', 'MOY', 'numbers_name', 2, false, '2-3 between 0-10'), ('3/4', 'MOY', 'count_rote', 5, false, 'Counts 1-5'), ('3/4', 'MOY', 'count_one_to_one', 3, false, '3 items'), ('3/4', 'MOY', 'letters_rec', 2, true, '2 letters in name'), ('3/4', 'MOY', 'letters_name', 2, true, '2 in name'), ('3/4', 'EOY', 'colors_rec', 6, false, '6'), ('3/4', 'EOY', 'colors_name', 6, false, '6'), ('3/4', 'EOY', 'shapes_rec', 3, false, '3'), ('3/4', 'EOY', 'shapes_name', 3, false, '3'), ('3/4', 'EOY', 'numbers_rec', 2, false, '2-3 between 0-10'), ('3/4', 'EOY', 'numbers_name', 2, false, '2-3 between 0-10'), ('3/4', 'EOY', 'count_rote', 10, false, 'Counts 1-10'), ('3/4', 'EOY', 'count_one_to_one', 5, false, '5 items'), ('3/4', 'EOY', 'letters_rec', 3, false, '3 or more'), ('3/4', 'EOY', 'letters_name', 2, true, '2-3 in name'), ('4/5', 'BOY', 'colors_rec', 6, false, '6'), ('4/5', 'BOY', 'colors_name', 6, false, '6'), ('4/5', 'BOY', 'shapes_rec', 3, false, '3'), ('4/5', 'BOY', 'shapes_name', 3, false, '3'), ('4/5', 'BOY', 'numbers_rec', 3, false, '3-5 between 0-10'), ('4/5', 'BOY', 'numbers_name', 3, false, '3-5 within 0-10'), ('4/5', 'BOY', 'count_rote', 10, false, 'Counts 1-10'), ('4/5', 'BOY', 'count_one_to_one', 5, false, '5 items'), ('4/5', 'BOY', 'letters_rec', 6, false, '6'), ('4/5', 'BOY', 'letters_name', 4, false, '4-6'), ('4/5', 'MOY', 'colors_rec', 8, false, '8'), ('4/5', 'MOY', 'colors_name', 8, false, '8'), ('4/5', 'MOY', 'shapes_rec', 6, false, '6'), ('4/5', 'MOY', 'shapes_name', 6, false, '6'), ('4/5', 'MOY', 'numbers_rec', 5, false, '5-8 between 0-10'), ('4/5', 'MOY', 'numbers_name', 5, false, '5-8 between 0-10'), ('4/5', 'MOY', 'count_rote', 15, false, 'Counts 1-15'), ('4/5', 'MOY', 'count_one_to_one', 8, false, '8 items'), ('4/5', 'MOY', 'letters_rec', 8, false, '8'), ('4/5', 'MOY', 'letters_name', 5, false, '5-8'), ('4/5', 'EOY', 'colors_rec', 10, false, '10'), ('4/5', 'EOY', 'colors_name', 10, false, '10'), ('4/5', 'EOY', 'shapes_rec', 8, false, '8'), ('4/5', 'EOY', 'shapes_name', 8, false, '8'), ('4/5', 'EOY', 'numbers_rec', 5, false, '5-8 between 0-10'), ('4/5', 'EOY', 'numbers_name', 5, false, '5-8 between 0-10'), ('4/5', 'EOY', 'count_rote', 20, false, 'Counts 1-20'), ('4/5', 'EOY', 'count_one_to_one', 10, false, '10 items'), ('4/5', 'EOY', 'letters_rec', 10, false, '10'), ('4/5', 'EOY', 'letters_name', 10, false, '10'), ('4/5', 'EOY', 'letter_sounds', 1, false, '1-3') ) AS x(age_group, assess_window, measure, min_n, in_name, label) ON x.age_group = ec_m.age_group AND x.assess_window = ec_m.assess_window AND x.measure = ec_m.measure )

Demo: Early Childhood — Academics