Exploration

At a Glance

#

The schema phase produced a queryable database with three tables and known relationships. The natural first move is breadth before depth: run a handful of queries that cover the obvious dimensions (region, campus, program, cohort, retake count, and time from graduation to test) without yet reaching for window functions or model fits. See what the data says at a glance, then decide which questions are worth following further in phase 04.

Every SQL block in this phase has a Datasette Lite link below it so the reader can run the query directly in the browser against the same database, no setup required. Every claim in the prose ties to a query the reader can re-run.

The dimension that matters most is already known from the source phase: campus, with a 21-point spread between the lowest and highest first-time pass rate. This phase confirms that spread, adds confidence intervals so the reader can see which campus differences are real and which are sample noise, and develops five other dimensions that the case study will not lead with in phase 04 but that shape the analysis along the way.

How To Read These Numbers

#

Every pass rate in this phase comes with a 95 percent confidence interval computed inline in SQL using the standard Wald formula for a proportion:

$$\hat{p} \pm 1.96 \sqrt{\frac{\hat{p}(1-\hat{p})}{n}}$$

The interval widens as the sample size shrinks. At $n=2{,}572$ (Greater Boston Region, the largest in the data), the margin is roughly $\pm 1.4$ percentage points. At $n=47$ (Northern New England, the smallest region), it widens to roughly $\pm 9.5$ points. Reading these widths is the difference between treating two campuses as meaningfully different and reading them as statistically indistinguishable.

The Wald formula has one known weakness worth flagging before phase 03 starts using it: at very small $n$ combined with extreme $\hat{p}$, the upper bound can exceed 100 percent. One campus in the dataset (MAN, $n=11$) hits this case, producing a nominal upper bound around 105 percent. The right reading is that the interval is too wide to be informative at that sample size, not that pass rates above 100 percent exist. Phase 04’s predictive-modeling section uses Wilson or Agresti-Coull intervals where this matters; phase 03 uses Wald throughout for transparency about the formula and accepts the occasional out-of-bound upper as a flag that $n$ is too small to draw inferences from.

Pass Rate By Region

#

The first cut: first-time pass rate by region, with sample sizes and confidence intervals.

-- Pass rate by region for first attempts, with a 95% Wald CI
-- computed inline in SQL. The CTE separates the rate computation
-- from the presentation rounding so the margin formula stays
-- readable. Filter to attempt_number = 1 because the question
-- is about first-time performance; retakes are a separate question.
WITH region_stats AS (
  SELECT
    region,
    COUNT(*)                AS n,
    AVG(CAST(result AS REAL)) AS p
  FROM attempts
  WHERE attempt_number = 1
  GROUP BY region
)
SELECT
  region                                                AS "Region",
  n                                                     AS "First Attempts",
  ROUND(100.0 * p, 2)                                   AS "Pass Rate %",
  ROUND(100.0 * (p - 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Lower %",
  ROUND(100.0 * (p + 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Upper %"
FROM region_stats
ORDER BY p DESC;

Run this query in Datasette Lite

Result:

Two readings of this table.

First, the regional spread is small. The top region (Hudson Valley at 87.76 percent) is roughly four points above the bottom region (Greater Boston at 83.90 percent). For comparison, the campus spread that phase 01 surfaced is 21 points. The dimension on which this institution varies most is not its regions but the campuses within those regions: Hudson Valley’s POU campus at 73.62 percent and SCH campus at 92.51 percent are 19 points apart, almost five times the regional spread. Region is not the lever; campus is.

Second, three regions overlap with each other in their confidence intervals (Hudson Valley, Lehigh Valley, and Northern New England all have overlapping bands), so it would be a stretch to claim a meaningful order among them. Greater Boston’s confidence interval does not overlap with Hudson Valley’s, so that gap is statistically real even before considering effect size. Connecticut Valley sits between them, overlapping with Greater Boston on the low side and not quite reaching Hudson Valley on the high side.

Pass Rate By Campus

#

The campus breakdown deepens the phase 01 discovery with confidence intervals and sample sizes. Reading the table requires the small-n caveat from the section above: campuses with fewer than fifty first-time attempts produce intervals too wide to draw conclusions from.

-- Pass rate by campus, sorted ascending so the bottom of the
-- distribution is at the top of the result. Includes region for
-- context. Same Wald CI as the region query; sample sizes vary
-- by nearly two orders of magnitude across campuses, so the CI
-- widths are the part to pay attention to.
WITH campus_stats AS (
  SELECT
    region, campus,
    COUNT(*)                AS n,
    AVG(CAST(result AS REAL)) AS p
  FROM attempts
  WHERE attempt_number = 1
  GROUP BY region, campus
)
SELECT
  campus                                                AS "Campus",
  region                                                AS "Region",
  n                                                     AS "First Attempts",
  ROUND(100.0 * p, 2)                                   AS "Pass Rate %",
  ROUND(100.0 * (p - 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Lower %",
  ROUND(100.0 * (p + 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Upper %"
FROM campus_stats
ORDER BY p ASC;

Run this query in Datasette Lite

Result (19 rows, sorted by pass rate ascending):

The headline finding from phase 01 holds at deeper inspection. The three lowest campuses (NEW, POU, SPR) have confidence intervals that do not overlap with the three highest (REA, SCH, BTH). The 21-point spread is not a sampling artifact; it is structural.

Two side observations worth naming. NEW ($n=45$, pass rate 73.33 percent) and POU ($n=235$, pass rate 73.62 percent) sit at virtually the same point estimate. Their confidence intervals tell different stories: POU’s narrow interval (67.98 to 79.25) makes it a campus with a real performance problem at substantial sample size, while NEW’s wide interval (60.41 to 86.25) means the apparent low rate could shrink considerably with more data. The two campuses look the same in a point-estimate table and are quite different signals.

MAN’s upper bound at 104.61 percent is the small-sample Wald artifact the formula-introduction section warned about. With $n=11$, the interval is too wide to be informative. Phase 04 uses a better-behaved interval method for the predictive-modeling work; phase 03 keeps the Wald formula visible so the limitation is documented rather than hidden.

Pass Rate By Program

#

The eight program codes in the data resolve to four pathways: traditional two-year ADN tracks, an advanced-standing ADN track, three bridge tracks (LPN-to-RN), and a prelicensure BSN track. Grouping by pathway makes the educational-design differences visible.

-- Pass rate by program, with a derived pathway grouping in the
-- first column. The CASE WHEN maps the eight raw program codes
-- to four readable pathway labels. Programs within each pathway
-- are listed alphabetically; the case study reads them as variants
-- of the same educational design.
WITH prog_stats AS (
  SELECT
    program,
    CASE
      WHEN program LIKE 'ADN.2YR%'  THEN 'ADN, Traditional 2-Year'
      WHEN program LIKE 'ADN.ADV%'  THEN 'ADN, Advanced Standing'
      WHEN program LIKE 'ADN.BRDG%' THEN 'ADN, Bridge (LPN-to-RN)'
      WHEN program LIKE 'BSN.PRE%'  THEN 'BSN, Prelicensure'
      ELSE 'Other'
    END                       AS pathway,
    COUNT(*)                  AS n,
    AVG(CAST(result AS REAL)) AS p
  FROM attempts
  WHERE attempt_number = 1
  GROUP BY program
)
SELECT
  pathway                                               AS "Pathway",
  program                                               AS "Program",
  n                                                     AS "First Attempts",
  ROUND(100.0 * p, 2)                                   AS "Pass Rate %",
  ROUND(100.0 * (p - 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Lower %",
  ROUND(100.0 * (p + 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Upper %"
FROM prog_stats
ORDER BY pathway, program;

Run this query in Datasette Lite

Result:

The bridge pathway is where the largest within-pathway variation lives. ADN.BRDG.AAS (83.27 percent, $n=1{,}883$) and ADN.BRDG.AS (91.02 percent, $n=880$) are nearly eight points apart with non-overlapping confidence intervals, despite both being labeled as bridge programs. The same pattern shows up in the traditional pathway: ADN.2YR.AAS.2 (83.67 percent) sits roughly six points below ADN.2YR.AAS (89.81 percent). The pathway label is not the full explanation; the AAS-vs-AS credential split within each pathway is doing work the pathway label hides.

The Advanced Standing program (ADN.ADV.AAS) is the top performer at 93.55 percent, but at $n=62$ the confidence interval (87.43 to 99.66) overlaps with the top of ADN.BRDG.AS. The two are statistically indistinguishable; the point estimate ranking is real but the sample size is too small to claim a meaningful gap between them.

The Prelicensure BSN program performs in the middle of the pack (86.74 percent), which is itself worth naming. A common assumption is that BSN students outperform ADN students on NCLEX, since the BSN curriculum is longer and more theoretically grounded. This dataset shows the opposite: the BSN cohort performs below the strongest ADN bridge track and below the Advanced Standing track. Phase 04 develops what this implies for program-level interventions.

Cohort Trend

#

The two-year testing window runs from winter 2024 through fall 2025 (eight quarters in chronological order). Sorting by the term_order ordinal puts them in calendar order rather than the lexical-string order that would scramble them.

-- Pass rate by testing cohort, in chronological order via the
-- term_order ordinal. The ordinal is the column that prevents
-- 2024FAQ from sorting before 2024SUQ; ordering by it puts the
-- terms in calendar order. CIs as before.
WITH cohort_stats AS (
  SELECT
    a.testing_cohort,
    t.ordinal,
    COUNT(*)                  AS n,
    AVG(CAST(a.result AS REAL)) AS p
  FROM attempts a
  JOIN term_order t ON t.cohort = a.testing_cohort
  WHERE a.attempt_number = 1
  GROUP BY a.testing_cohort, t.ordinal
)
SELECT
  testing_cohort                                        AS "Testing Cohort",
  n                                                     AS "First Attempts",
  ROUND(100.0 * p, 2)                                   AS "Pass Rate %",
  ROUND(100.0 * (p - 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Lower %",
  ROUND(100.0 * (p + 1.96 * SQRT(p * (1 - p) / n)), 2)  AS "CI Upper %"
FROM cohort_stats
ORDER BY ordinal;

Run this query in Datasette Lite

Result:

The shape across the eight cohorts has two phases. The 2024 cohorts (winter, spring, summer, fall) hold a pass rate in the 87 to 92 percent range, with summer 2024 the peak at 91.78 percent. The 2025 cohorts drop to the 83 to 86 percent range, with winter 2025 the lowest at 83.32 percent.

The drop from 2024SUQ (91.78 percent) to 2025WIQ (83.32 percent) is 8.46 percentage points across two consecutive quarters. The 2024SUQ and 2025WIQ confidence intervals do not come close to overlapping, so the gap is real even before considering effect size.

Phase 04 reads this as the post-NGN decline pattern, with two specific framings. The NCSBN published national NCLEX-RN pass rates for the same two-year window show roughly a four-point decline; the institution drops twice as much. And the 2024-2025 boundary is roughly the point at which the Next Generation NCLEX format completed its first-year transition cycle, with cohorts who were trained pre-NGN and tested post-NGN largely flushed through by mid-2024. The 2025 cohorts are the first who were both trained and tested under NGN, and their pass rates are the ones reverting toward (or below) the national trend.

The case study does not claim the NGN transition is the cause of the drop. It is one plausible explanation and the most prominent industry-level event in the window. Phase 04 develops the rest.

Time From Graduation To First Test

#

The phase 01 quirks section flagged a multimodal distribution of terms_grad_to_first_test. The schema phase precomputed this column on students, so phase 03 can query it directly.

-- Distribution of terms between graduation and first test, with
-- a running cumulative count and percentage so the multimodal
-- shape is visible alongside the running coverage. Bounded at
-- -5 to +8 because the long right tail (one student each at
-- +20, +32, +39, +44, +50, +54, +74) is a separate story not
-- worth crowding the main table with.
WITH dist AS (
  SELECT
    terms_grad_to_first_test AS terms,
    COUNT(*)                 AS students
  FROM students
  GROUP BY terms_grad_to_first_test
)
SELECT
  terms                                                AS "Terms After Graduation",
  students                                             AS "Students",
  ROUND(100.0 * students /
    (SELECT COUNT(*) FROM students), 2)                AS "% of Students",
  SUM(students) OVER (ORDER BY terms)                  AS "Cumulative Students",
  ROUND(100.0 * SUM(students) OVER (ORDER BY terms) /
    (SELECT COUNT(*) FROM students), 2)                AS "Cumulative %"
FROM dist
WHERE terms BETWEEN -5 AND 8
ORDER BY terms;

Run this query in Datasette Lite

Result:

Three modes, each accounting for between 22 and 43 percent of students: minus three terms (23.6 percent), plus one term (42.7 percent), and plus five terms (22.1 percent). By plus one term, roughly three-quarters of the cohort has tested. By plus five, the cumulative reaches 98 percent. The remaining 1.65 percent is the long tail that the WHERE terms BETWEEN -5 AND 8 clause hides; the queryable database includes outliers at plus 32, plus 50, and plus 74 terms.

The negative-mode group (1,673 students, roughly 24.5 percent of the cohort) tested before their recorded graduating cohort. The most likely explanation, as phase 01 flagged, is that the Graduating Cohort field is set at enrollment time as an expected graduation date and is not always updated when actual graduation moves later. The case study does not exclude these students from the analysis; their attempt records are real attempts and their pass rates contribute to the campus and program aggregates above. But any analysis that treats terms_grad_to_first_test as elapsed time since graduation needs to account for this interpretation issue.

Retake Outcomes

#

The last orientation cut is by retake count: how often students take multiple attempts, and how their eventual outcome depends on how many attempts they take.

-- Outcomes by total visible attempts. Each row is a count of
-- students who took exactly that many NCLEX attempts in the
-- two-year testing window. "Eventually Passed" is from the
-- students table's eventually_passed flag, computed at build
-- time as MAX(result) across that student's attempts. Reading
-- this table from the top: most students pass on attempt one
-- and never retake; among retakers, eventual pass rates remain
-- well above 50 percent through five attempts.
SELECT
  total_attempts        AS "Total Attempts",
  COUNT(*)              AS "Students",
  SUM(eventually_passed) AS "Eventually Passed",
  COUNT(*) - SUM(eventually_passed) AS "Never Passed",
  ROUND(100.0 * SUM(eventually_passed) / COUNT(*), 2) AS "Eventually Passed %"
FROM students
GROUP BY total_attempts
ORDER BY total_attempts;

Run this query in Datasette Lite

Result:

A few details to read carefully. The “1 attempt” row at 93.84 percent is not the first-time pass rate; it is the pass rate among students whose only visible attempt is their first one. This combines students who passed on their first try with students who failed their first try and did not retake within the window. The first-time pass rate from the campus table above (overall 86.94 percent across attempts where attempt_number = 1) is the right number for first-time pass rate; the 93.84 percent in this row is a different quantity that mixes first-try passers with first-try failers who did not retake.

Among students who do retake (rows 2 through 9), the eventually-passed rate stays meaningfully above 50 percent through five attempts. The institution’s retake support is doing real work for students who engage with it. Phase 04 develops the framing: the institution’s retake conversion rate runs about nine points above the NCSBN national benchmark for repeat NCLEX-RN takers. The opportunity is not improving retake conversion; it is getting more first-time failers to engage with the retake program in the first place.

The two outlier rows (one student at six attempts who passed, one student at nine attempts who did not) are individual records and not analytically meaningful at $n=1$ each. They are kept in the table because the schema preserves them, and the case study commits to showing what the data shows.

Looking Ahead

#

Phase 04 (Findings) develops the three analytical threads that the orientation queries above point to. The 21-point campus spread becomes a structured argument for campus-level intervention targeting, with the within-region campus variance the actual lever rather than the regional or programmatic aggregates. The 2024-to-2025 cohort decline becomes a comparison against the NCSBN national trend, with the institution dropping at roughly twice the national rate. And the retake-attempt distribution becomes the entry point to the retake-engagement framing: the case study’s strongest counterintuitive finding, that the conversion rate among retakers is already high and the lever is engagement, not conversion.

Phase 04’s predictive-modeling subsection switches briefly to R for the logistic regression and AUC discussion, where SQL hits its analytical ceiling. The structural data limits identified in phase 01 (binary outcome, no demographics, no academic-readiness scores) shape what any model could and could not do with this dataset.