Exploration

At a Glance

#

The schema phase produced a queryable database with three tables and known invariants. The natural first move is breadth before depth: run a handful of queries that cover the obvious dimensions (sector, year, institution, cost, completion, programs) without yet reaching for window functions or CTEs. See what the data says at a glance, then decide which questions are worth following further.

This is the exploration phase the case study philosophy describes: not the analysis, but the orientation. Findings live in the next phase. The six queries below are the act of getting bearings, the moments of “what does this dataset actually contain” that shape every deeper question that comes later.

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. The expected output shown alongside each query is the actual output from the live database: anyone running the query gets the same numbers shown here, or this case study fails its own reproducibility-is-the-floor test.

Counts By Sector And Year

#

The first query, and the one that anchors most of the later analysis. Florida four-year institution counts by sector across the twenty-one cohort years in the dataset:

-- Florida four-year institutions by sector and year.
-- Counts non-null annual_metrics rows: an institution is "in"
-- a year if and only if it has a row in annual_metrics for that
-- cohort_year. Sectors translated to display names with CASE WHEN.
SELECT
    am.cohort_year AS "Cohort Year",
    CASE i.sector
        WHEN 'public'            THEN 'Public'
        WHEN 'private_nonprofit' THEN 'Private Nonprofit'
        WHEN 'for_profit'        THEN 'For-Profit'
    END      AS "Sector",
    COUNT(*) AS "Institutions"
FROM annual_metrics am
JOIN institutions   i USING (unitid)
GROUP BY am.cohort_year, i.sector
ORDER BY am.cohort_year, i.sector;

Run this query in Datasette Lite

Result (30 rows):

The shape across sectors tells three different stories. The Public sector is essentially flat: 14 institutions in 2014, 13 in 2023. Florida Polytechnic University entered the data in 2017 (it was founded in 2012 and started awarding bachelor’s degrees that year), accounting for the brief 15-institution count from 2017 through 2019; the drop to 13 in 2020 reflects the USF consolidation, when the University of South Florida-Sarasota-Manatee and University of South Florida-St Petersburg campuses were merged into the main USF UNITID. The State University System has twelve voting members today, and the database reflects that.

The Private Nonprofit sector contracted modestly: 58 institutions in 2014, 52 in 2023. The decline is gradual across the decade, not concentrated in any one year. Most of the institutions that disappeared were small religious colleges, seminaries, or single-program institutions that had brief appearances in the data and stopped reporting.

The For-Profit sector is the one with a real shape change. 26-28 institutions in 2014-2016, dropping to 21 by 2018, then partial recovery to 22-23 by the early 2020s. The dip is real and concentrated: nine for-profit four-year institutions reported in 2016 but did not appear in 2018. Some of these were the well-publicized for-profit chain collapses of 2016-2017 (Argosy University, Sanford-Brown). Phase 04 develops the closure-wave thread further.

Who Does The Work

#

The second obvious cut: who actually does the educating?

-- Top 15 institutions by average undergraduate enrollment across
-- all reporting years. AVG(ugds) is computed only over rows where
-- ugds is non-null (the WHERE clause filters out privacy-suppressed
-- and unreported rows). Years Reporting is the count of non-null
-- ugds values per institution, so the reader can see whether the
-- average is across the full window or a partial window.
SELECT
    i.instnm AS "Institution",
    CASE i.sector
        WHEN 'public'            THEN 'Public'
        WHEN 'private_nonprofit' THEN 'Private Nonprofit'
        WHEN 'for_profit'        THEN 'For-Profit'
    END                    AS "Sector",
    CAST(ROUND(AVG(am.ugds), 0) AS INTEGER) AS "Avg UG Enrollment",
    COUNT(am.ugds)         AS "Years Reporting"
FROM annual_metrics am
JOIN institutions   i USING (unitid)
WHERE am.ugds IS NOT NULL
GROUP BY i.unitid, i.instnm, i.sector
ORDER BY AVG(am.ugds) DESC
LIMIT 15;

Run this query in Datasette Lite

Result:

The top 15 is dominated by Public institutions. The five largest publics (UCF, FIU, UF, USF, FSU) account for roughly 197,000 average annual undergraduate enrollment, more than the total enrollment of every other Florida four-year institution combined. This concentration is structural: Florida’s two-decade-old emphasis on growing the State University System has produced one of the largest public university systems in the country by enrollment, and UCF in particular has expanded so far past traditional flagship size that it consistently ranks among the largest universities in the United States.

The for-profit and private nonprofit sectors are visible too, but at much smaller scales. Full Sail University at #7 is the largest for-profit institution, an Orlando-based digital media school with an unusually concentrated curricular focus. Rasmussen University-Florida at #15 is the second-largest for-profit. The University of Miami at #10 is the largest private nonprofit. Most of the rest of the private nonprofit sector and most of the for-profit sector reports enrollments under 5,000.

Cost Trajectories

#

The third orientation: how has the price changed?

-- Average in-state tuition by sector and cohort year. The query
-- filters out rows where tuitionfee_in is null (most for-profit
-- and private nonprofit institutions don't have an "in-state"
-- distinction; they charge one rate to all students). The Reporting
-- column shows how many institutions per sector contributed to
-- each year's average so the reader can spot small-N risks.
SELECT
    am.cohort_year AS "Cohort Year",
    CASE i.sector
        WHEN 'public'            THEN 'Public'
        WHEN 'private_nonprofit' THEN 'Private Nonprofit'
        WHEN 'for_profit'        THEN 'For-Profit'
    END                             AS "Sector",
    CAST(ROUND(AVG(am.tuitionfee_in), 0) AS INTEGER) AS "Avg In-State Tuition",
    COUNT(am.tuitionfee_in)         AS "Reporting"
FROM annual_metrics am
JOIN institutions   i USING (unitid)
WHERE am.tuitionfee_in IS NOT NULL
GROUP BY am.cohort_year, i.sector
ORDER BY am.cohort_year, i.sector;

Run this query in Datasette Lite

Result (30 rows):

The Public sector tuition number is the headline finding. Florida public four-year in-state tuition was $5,984 in 2014 and $5,896 in 2023. Nominally lower in 2023 than in 2014. Inflation-adjusted, the decline is much steeper: a 2014 dollar is worth roughly $0.78 in 2023, so the real-cost reduction approaches twenty-five percent. This is not because Florida’s public universities became cheaper to operate; it is because the state legislature has held the published tuition rate effectively flat while subsidizing operations through Florida Bright Futures scholarships and direct appropriations. The published in-state tuition is a policy instrument, not a market price.

Private Nonprofit tuition climbed from $21,041 to $27,012, up 28 percent in nominal terms. After inflation adjustment, real cost growth is roughly two percent over the decade, essentially flat. Private nonprofits raise tuition, but they also raise institutional aid: the published number is the sticker price, and most students pay less than sticker.

For-Profit tuition is more volatile. $15,235 in 2014, dipping below $14,000 in 2015-2017, then climbing to $18,519 in 2023. The 2015-2017 trough corresponds to the closure wave: as institutions closed, average tuition was dragged toward whatever the surviving institutions charged, and several of the surviving for-profits had below-average tuition. The 2023 average is roughly twenty percent higher than 2014 in nominal terms.

Completion Rates

#

The fourth orientation: who actually finishes?

-- 150-percent-of-normal-time graduation rate distribution by
-- sector. AVG, MIN, MAX show the spread within each sector;
-- "Rows With Data" is the institution-year count that contributed.
-- The c150_4 column reports the fraction (0.0 to 1.0) of an
-- entering cohort that completed within six years (150% of the
-- four-year normal time); we multiply by 100 for display.
SELECT
    CASE i.sector
        WHEN 'public'            THEN 'Public'
        WHEN 'private_nonprofit' THEN 'Private Nonprofit'
        WHEN 'for_profit'        THEN 'For-Profit'
    END                            AS "Sector",
    ROUND(AVG(am.c150_4) * 100, 1) AS "Avg Completion %",
    ROUND(MIN(am.c150_4) * 100, 1) AS "Min %",
    ROUND(MAX(am.c150_4) * 100, 1) AS "Max %",
    COUNT(am.c150_4)               AS "Rows With Data"
FROM annual_metrics am
JOIN institutions   i USING (unitid)
WHERE am.c150_4 IS NOT NULL
GROUP BY i.sector
ORDER BY AVG(am.c150_4) DESC;

Run this query in Datasette Lite

Result:

Three different shapes. Public completion rates average 62.8 percent and span 31.8 to 91.5 percent: a tight distribution by sector standards, with no institution reporting catastrophic failure (below 30 percent) and no institution reporting perfection (100 percent). Florida’s public flagships drive the upper end, and regional comprehensives like the University of West Florida and Florida Gulf Coast cluster below the average.

Private Nonprofit completion averages 47.6 percent but ranges from 0 to 100 percent. The bimodal distribution reflects the sector’s heterogeneity. The University of Miami, Stetson University, Rollins College, Eckerd College — all selective private four-years — report completion rates well above the sector average. Smaller religious colleges, single-discipline institutions, and online-heavy programs report lower rates, sometimes much lower. The 100 percent maximum is real: a few small institutions report cohorts where every entering student completed, usually because the cohort itself was small enough to make the percentage statistically fragile.

For-Profit completion averages 39.7 percent and shows the same 0-to-100 range. The for-profit sector’s lower average is structural: many for-profit four-years serve adult learners, transfer students, and students who entered the institution to complete a credential rather than start one. Their completion rate measures something different from a traditional flagship’s graduation rate. The for-profit closure wave further complicates the average: institutions that closed in 2016-2017 contributed final-year completion rates, often distorted by the closing process itself.

The 0.0 percent minimum visible in both the table and the chart deserves a closer look. A literal zero-percent completion rate could mean an institution where every entering cohort fails to graduate, but in this data it almost always means something else. The c150_4 metric measures specifically first-time, full-time bachelor’s-seeking students who completed within 150 percent of normal time (six years for a four-year program). At institutions whose student bodies are mostly part-time, mostly transfer students, or pursuing programs longer than four years, this metric can report zero even when the institution graduates students normally.

The evidence is in the c200_4 column (the 200 percent, eight-year, completion rate) for the same institution-years where c150_4 is zero. A query on these rows shows that students DO graduate, just outside the six-year window:

In every case shown, the institution reported zero completion at six years and meaningful completion at eight. Argosy University-Sarasota in 2016 reported a 66.7 percent eight-year completion rate while reporting zero six-year completion in the same year, an institution where most graduates take longer than six years to finish. Polytechnic University of Puerto Rico-Miami in 2022 reported 100 percent eight-year completion, every counted student graduated, just past the six-year window. The 0.0 percent c150_4 values are honest data, but they reflect measurement-artifact constraints (a metric calibrated for traditional first-time-full-time students applied to institutions with mostly non-traditional students), not institutional failure. Phase 04 filters out small-cohort and statistically-fragile rows where they would distort findings.

The Closure Wave Forensics

#

The fifth orientation: what did the for-profit institutions that closed look like in their final reporting year?

-- For-profit institutions that disappeared from the data before
-- 2023, with their final reporting year's enrollment, completion
-- rate, and three-year cohort default rate. The JOIN matches each
-- institution to its annual_metrics row from its last_year_in_data,
-- giving a snapshot of the institution at the moment it stopped
-- reporting. NULL values in the snapshot reflect privacy
-- suppression or unreported metrics in that final year.
SELECT
    i.instnm                  AS "Institution",
    i.last_year_in_data       AS "Last Year",
    am.ugds                   AS "Final Enrollment",
    ROUND(am.c150_4 * 100, 1) AS "Completion %",
    ROUND(am.cdr3 * 100, 1)   AS "Default Rate %"
FROM institutions       i
JOIN annual_metrics     am
    ON i.unitid = am.unitid
   AND i.last_year_in_data = am.cohort_year
WHERE i.sector = 'for_profit'
  AND i.last_year_in_data < 2023
ORDER BY i.last_year_in_data, i.instnm;

Run this query in Datasette Lite

Result:

Thirteen for-profit four-year institutions closed during the window. The names map to several recognizable closure events. The Sanford-Brown chain (parent: Career Education Corporation) closed multiple Florida campuses in 2016-2017 as the parent company exited the for-profit education business. The Argosy University locations closed in 2017 along with the rest of the Education Management Corporation network. Florida Coastal School of Law lost ABA accreditation in 2018 and stopped enrolling new students; it appears in the data through 2020. University of Phoenix-Florida appeared as a locally-reported branch separate from the main University of Phoenix UNITID and stopped reporting in 2020.

The final-year metrics paint a consistent picture: small enrollments at the closure point (most under 300 students), low completion rates where reported (under 40 percent in most cases), and elevated default rates (15-24 percent for several). The institutions that closed were not closing from a position of analytical strength. None of this is causation in the strict sense; the data shows correlation between closure and weak outcomes, not which one drove the other or whether external factors (regulatory pressure, parent company finances) drove both.

Programs Across The Decade

#

The sixth orientation: what do Florida four-year institutions actually offer?

-- Top 10 most-offered programs across all Florida four-year
-- institutions. The grouping is on (CIPCODE, CREDLEV) since a
-- single CIP code can be offered at multiple credential levels
-- (bachelor's, master's, doctoral). "Institutions" counts the
-- distinct UNITIDs offering each program-credential combo;
-- "Program-Year Rows" counts the total reporting rows. A high
-- institutions count means broad availability across the state.
SELECT
    fos.cipdesc                AS "Program",
    fos.creddesc               AS "Credential",
    COUNT(DISTINCT fos.unitid) AS "Institutions",
    COUNT(*)                   AS "Program-Year Rows"
FROM field_of_study fos
GROUP BY fos.cipcode, fos.credlev, fos.cipdesc, fos.creddesc
ORDER BY COUNT(DISTINCT fos.unitid) DESC, COUNT(*) DESC
LIMIT 10;

Run this query in Datasette Lite

Result:

Business Administration is the most-offered program, available at 74 of the 108 institutions with field_of_study data (66 percent of the institution set with program-level outcomes). The same program at the master’s level reaches 61 institutions. The next most-common bachelor’s programs follow a recognizable pattern: Psychology, Liberal Arts, Teacher Education, Accounting, Marketing, Nursing, Criminal Justice, Communication. These are the standard regional-comprehensive bachelor’s portfolio: practical degrees with reliable enrollment, offered by most four-year institutions regardless of sector.

The pattern is informative for two reasons. First, it confirms that Florida four-year institutions are broadly comparable in what they offer: most institutions cover the same handful of programs at the bachelor’s level, and the variation across institutions is more about quality and depth than about field coverage. Second, the absence of niche programs from the top 10 is itself a finding: STEM disciplines (engineering, computer science, biology) appear lower in the full ranking, partly because they are concentrated at fewer institutions, and partly because they are less commonly offered by the for-profit sector that contributes a meaningful share of the institution count.

What’s Worth Following Further

#

The first-pass queries give the shape of the data, but they answer single-dimension questions: how many institutions by sector, how much they cost, what they offer, who closed. The interesting questions mostly live at intersections, where two dimensions meet and the cross-tabulation surfaces patterns that neither dimension alone shows.

Three threads worth pulling in phase 04:

How did cost-vs-outcome ratios shift across the decade? The cost trajectory is documented; the completion rate is documented. The interesting question is the ratio: which institutions deliver the highest completion rate per dollar of net price, and how has that ratio moved over time? Window functions can rank institutions within sector and within year on a constructed “cost per completer” metric, surfacing the institutions that improved their efficiency and the ones that did not.

What did the for-profit closure wave look like as a structural shift in Florida’s higher-education marketplace? Phase 03 captured the institutions that closed. Phase 04 can ask the systemic question: what fraction of Florida four-year for-profit enrollment was concentrated in the institutions that closed, and where did those students go? Some closure-wave students transferred to surviving for-profits, others to private nonprofits, others to publics. The data does not directly track student transfers, but enrollment shifts in surviving institutions during and after closure years can be analyzed as a proxy.

How do Florida’s HBCUs compare to the broader Florida four-year picture? FAMU is the only public HBCU in the State University System, and Bethune-Cookman, Edward Waters, and Florida Memorial are private nonprofit HBCUs. Their cost, completion, and outcomes profiles are interesting both individually and as a peer group. Phase 04 develops this thread.

These are the questions phase 04 reaches for, with window functions and common table expressions doing the work that group-bys alone cannot.