Probability and Hierarchical SQL

Introduction

30 Second Summary

When a doctor tells you a medical test is "95% accurate," that number can be deeply misleading. In populations where a disease is rare, most positive results are actually false alarms.

In this project, you will build a medical analytics database that proves this counter-intuitive reality using real patient data and SQL queries that compute probability from scratch.

What You'll Build

A finished database where you pick any glucose threshold, query its sensitivity and predictive value against 768 real patients, filter to a different subpopulation, and watch the predictive value collapse in real time.

By the end of this project, you'll have:

• A screening test you defined by glucose threshold, with sensitivity, specificity, and positive predictive value computed entirely in SQL on real patient records.
• A Bayes' theorem proof where the same test applied to a younger subpopulation produces dramatically worse predictions, demonstrating why base rates matter for medical decisions.
• A recursive CTE that traverses the ICD-10 code hierarchy and rolls up disease prevalence across an entire code family in a single query.
• Secret Mission: Add a B-tree index to the hierarchy table and use EXPLAIN ANALYZE to visualize the before-and-after query plan transformation.

Are there any prerequisites?

This project assumes you have MySQL 8.0 Community Edition and VS Code already installed on your machine. You should also be comfortable with basic SQL (SELECT, WHERE, JOIN).

Before We Start

Before we write any SQL, let's lock in what you're about to build and why getting probability right matters for real patient outcomes.

Set Up the Database and Load Data

You have your data files ready. The Pima Indians Diabetes CSV holds 768 real patient records, and the ICD-10-CM descriptions map the clinical code hierarchy for endocrine diseases.

Before you can ask any probability questions about this data, you need it inside a properly modeled database. In this step, you will design a relational schema with self-referencing foreign keys, then load all the data into MySQL.

In this step, get ready to:

• Create the medical_analytics database and define the patient_screening table.
• Design the EER model and add self-referencing icd10_code and diagnosis tables.
• Load 768 patient records from the CSV and seed the ICD-10 hierarchy.

Create the database and patient screening table

The first table you need stores the raw patient screening data. Each row represents one patient with their clinical measurements and a binary diabetes outcome.

• In VS Code, create a new file called 01_schema.sql inside your project SQL files directory.
• Add the database creation and patient screening table by pasting this code:

-- Create the database for all medical analytics work
CREATE DATABASE IF NOT EXISTS medical_analytics;
USE medical_analytics;

-- Store raw patient screening data from the Pima dataset
CREATE TABLE patient_screening (
    patient_id INT AUTO_INCREMENT PRIMARY KEY,
    pregnancies INT,
    glucose INT,
    blood_pressure INT,
    skin_thickness INT,
    insulin INT,
    bmi DECIMAL(4,1),
    diabetes_pedigree DECIMAL(4,3),
    age INT,
    outcome TINYINT
);

What does this code do?

CREATE DATABASE IF NOT EXISTS creates the database only when it does not already exist. This makes the script safe to re-run.

The patient_screening table mirrors the Pima dataset columns exactly. The outcome column stores 1 for diabetic and 0 for non-diabetic. An AUTO_INCREMENT primary key assigns each patient a unique ID on insert.

• Save 01_schema.sql. You will add more tables in the next substep before running the file.

Design the EER model and add relationship tables

Your schema needs two more tables to model the ICD-10 code hierarchy and link patients to diagnoses. The key design decision is a self-referencing foreign key on the icd10_code table.

What is an EER model?

An Enhanced Entity-Relationship model is a diagram showing entities (tables), their attributes (columns), and the relationships between them. Your model has three relationships:

• patient_screening 1:M diagnosis (one patient can have many diagnoses).
• icd10_code 1:M diagnosis (one code can appear on many diagnoses).
• icd10_code self-referencing (each code points to its parent via parent_code). This is the adjacency list pattern for hierarchical data.

• Add the following two table definitions below the patient_screening table in 01_schema.sql:

-- ICD-10 code hierarchy using adjacency list pattern
CREATE TABLE icd10_code (
    code VARCHAR(10) PRIMARY KEY,
    description VARCHAR(255) NOT NULL,
    parent_code VARCHAR(10),
    hierarchy_level TINYINT,
    FOREIGN KEY (parent_code) REFERENCES icd10_code(code)
);

-- Links patients to their diagnosis codes
CREATE TABLE diagnosis (
    diagnosis_id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    diagnosis_date DATE,
    FOREIGN KEY (patient_id) REFERENCES patient_screening(patient_id),
    FOREIGN KEY (code) REFERENCES icd10_code(code)
);

What does the self-referencing foreign key do?

The line FOREIGN KEY (parent_code) REFERENCES icd10_code(code) means each row can point to another row in the same table as its parent. This creates a tree structure: a chapter code has block codes as children, blocks have category codes, and so on.

The root of the tree (the top-level chapter) has NULL as its parent_code because nothing sits above it.

• Save 01_schema.sql.
• Run the schema file against MySQL by executing this command in your terminal:

mysql -u root -p < 01_schema.sql

• Enter your MySQL root password when prompted.
• Verify the tables were created by running:

mysql -u root -p -e "USE medical_analytics; SHOW TABLES;"

You should see three tables listed: diagnosis, icd10_code, and patient_screening.

Not seeing three tables?

• If you see an error about the database not existing, confirm 01_schema.sql starts with CREATE DATABASE IF NOT EXISTS medical_analytics; followed by USE medical_analytics; on the next line.
• If you see a foreign key error, check that icd10_code is defined before diagnosis in the file. The diagnosis table references icd10_code, so it must exist first.

Help me troubleshoot my schema creation error.

✔️ Awesome, I've got everything!

Great. Double check you have saved 01_schema.sql with all three CREATE TABLE statements.

ⓧ I'd like to double check the full code

Your 01_schema.sql file should contain these sections in order. Scroll up and confirm each one matches:

• CREATE DATABASE IF NOT EXISTS medical_analytics; and USE medical_analytics; at the top.
• CREATE TABLE patient_screening with 10 columns (patient_id through outcome).
• CREATE TABLE icd10_code with 4 columns and a self-referencing foreign key on parent_code.
• CREATE TABLE diagnosis with 4 columns and foreign keys to both patient_screening and icd10_code.

Load patient records and ICD-10 codes

With the schema in place, you can now fill it with real data. The CSV goes into patient_screening, and you will manually insert a representative subset of ICD-10 codes that shows the full hierarchy depth.

• First, enable MySQL's local file loading by running this command in your terminal:

mysql -u root -p -e "SET GLOBAL local_infile = 1;"

Why is this step needed?

MySQL disables local file loading by default for security. You need to enable it on the server side with SET GLOBAL local_infile = 1 and connect with the --local-infile=1 client flag. Without both, you will see the error: Loading local data is disabled; this must be enabled on both the client and server side.

• Create a new file called 02_load_data.sql in your project SQL files directory.
• Add the CSV loading statement by pasting this code:

USE medical_analytics;

-- Load the Pima Indians Diabetes CSV into patient_screening
LOAD DATA LOCAL INFILE '[[CSVPATH="/path/to/diabetes.csv"]]'
INTO TABLE patient_screening
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(pregnancies, glucose, blood_pressure, skin_thickness, insulin, bmi, diabetes_pedigree, age, outcome);

What does LOAD DATA LOCAL INFILE do?

This command reads a CSV file from your local machine and inserts each row into the specified table. The FIELDS TERMINATED BY ',' tells MySQL that columns are comma-separated. IGNORE 1 LINES skips the CSV header row so column names are not loaded as data.

• Replace the file path placeholder with the actual path to your downloaded diabetes.csv file.
• Now add the ICD-10 hierarchy data and diagnosis records below the LOAD DATA statement:

-- Insert ICD-10-CM codes showing Chapter > Block > Category > Code hierarchy
INSERT INTO icd10_code (code, description, parent_code, hierarchy_level) VALUES
('E00-E89', 'Endocrine, nutritional and metabolic diseases', NULL, 0),
('E08-E13', 'Diabetes mellitus', 'E00-E89', 1),
('E08', 'Diabetes mellitus due to underlying condition', 'E08-E13', 2),
('E09', 'Drug or chemical induced diabetes mellitus', 'E08-E13', 2),
('E10', 'Type 1 diabetes mellitus', 'E08-E13', 2),
('E11', 'Type 2 diabetes mellitus', 'E08-E13', 2),
('E11.0', 'Type 2 diabetes with hyperosmolarity', 'E11', 3),
('E11.2', 'Type 2 diabetes with kidney complications', 'E11', 3),
('E11.21', 'Type 2 diabetes with diabetic nephropathy', 'E11.2', 4),
('E11.22', 'Type 2 diabetes with diabetic chronic kidney disease', 'E11.2', 4),
('E11.3', 'Type 2 diabetes with ophthalmic complications', 'E11', 3),
('E11.31', 'Type 2 diabetes with unspecified diabetic retinopathy', 'E11.3', 4),
('E11.4', 'Type 2 diabetes with neurological complications', 'E11', 3),
('E11.40', 'Type 2 diabetes with diabetic neuropathy, unspecified', 'E11.4', 4),
('E11.5', 'Type 2 diabetes with circulatory complications', 'E11', 3),
('E11.6', 'Type 2 diabetes with other specified complications', 'E11', 3),
('E11.65', 'Type 2 diabetes with hyperglycemia', 'E11.6', 4),
('E11.8', 'Type 2 diabetes with unspecified complications', 'E11', 3),
('E11.9', 'Type 2 diabetes without complications', 'E11', 3),
('E12', 'Malnutrition-related diabetes mellitus', 'E08-E13', 2),
('E13', 'Other specified diabetes mellitus', 'E08-E13', 2),
('E13.1', 'Other specified diabetes with ketoacidosis', 'E13', 3),
('E13.9', 'Other specified diabetes without complications', 'E13', 3);

What does this hierarchy represent?

These 23 rows form a tree structure. At the top sits the chapter code E00-E89 (level 0). Below it, E08-E13 is the diabetes block (level 1). The block branches into categories like E11 for Type 2 diabetes (level 2), which branches further into complication subcategories (levels 3 and 4).

Each child row points back to its parent via parent_code. This is what makes the recursive CTE possible later.

• Finally, add the diagnosis linkage query at the bottom of the file:

-- Link all diabetic patients to the E11.9 diagnosis code
INSERT INTO diagnosis (patient_id, code, diagnosis_date)
SELECT patient_id, 'E11.9', '2024-01-15'
FROM patient_screening
WHERE outcome = 1;

What does this INSERT...SELECT do?

Instead of inserting literal values, this pulls every patient whose outcome = 1 (diabetic) and creates a diagnosis record linking them to code E11.9 (Type 2 diabetes without complications). This gives you real diagnosis data to query against the ICD-10 hierarchy.

• Save 02_load_data.sql.
• Run the complete data loading file with the local-infile flag enabled:

mysql --local-infile=1 -u root -p < 02_load_data.sql

Before you verify, think about how many rows you expect in patient_screening. The CSV has 768 data rows plus one header row that gets skipped. What number should the count return?

• Verify the data loaded correctly by running:

mysql -u root -p -e "SELECT COUNT(*) FROM medical_analytics.patient_screening;"

You should see 768 returned. That confirms all patient records loaded successfully.

• Confirm the ICD-10 codes and diagnoses loaded by running:

mysql -u root -p -e "USE medical_analytics; SELECT COUNT(*) AS codes FROM icd10_code; SELECT COUNT(*) AS diagnoses FROM diagnosis;"

You should see 23 codes and 268 diagnoses (one for each diabetic patient in the dataset).

Seeing 0 rows in patient_screening?

• If you see the error Loading local data is disabled, confirm you ran SET GLOBAL local_infile = 1 first AND used the --local-infile=1 flag in your connection command.
• If the command ran but you see 0 rows, check that the file path in your LOAD DATA LOCAL INFILE statement points to the actual location of diabetes.csv on your machine.
• If you see a foreign key error on the diagnosis INSERT, it means the icd10_code data did not load. Check that E11.9 is included in your INSERT VALUES list.

Help me debug my data loading errors in MySQL.

✔️ Awesome, I've got everything!

Great. Double check you have saved 02_load_data.sql with all three sections (LOAD DATA, ICD-10 INSERT, diagnosis INSERT).

ⓧ I'd like to double check the full code

Your 02_load_data.sql file should contain these sections in order. Scroll up and confirm each one matches:

• USE medical_analytics; at the top.
• LOAD DATA LOCAL INFILE statement with your CSV path, comma-separated fields, and IGNORE 1 LINES.
• INSERT INTO icd10_code with 23 rows of values covering hierarchy levels 0 through 4.
• INSERT INTO diagnosis ... SELECT linking all outcome = 1 patients to code E11.9.

Your database is loaded and ready. You have 768 real patient records, a properly modeled ICD-10 hierarchy, and diagnosis links tying them together. Next up, you will turn this data into probability calculations that reveal the true base rate of diabetes in the population.

Compute Probability as SQL Aggregates

Your database is loaded with 768 real patient records. Now you can start asking clinical questions.

Before you can evaluate whether a screening test is any good, you need to know the base rate of the disease in your population. A test that claims "95% accuracy" is useless if you don't know how common the disease is in the first place. This step computes that foundation, then builds a confusion matrix to measure how well a simple glucose threshold works as a screening test.

In this step, get ready to:

• Compute the base rate (prior probability) of diabetes in the dataset.
• Build a confusion matrix for a glucose-threshold screening test using CASE WHEN.
• Explore how changing the threshold shifts sensitivity and specificity.

Compute the base rate (prior probability)

The base rate is the simplest probability question you can ask: out of everyone in this dataset, what fraction actually has diabetes? This single number anchors every screening metric you compute later.

• Switch to VS Code.
• Create a new file called 03_probability.sql in the same folder as your other SQL files.
• Add the database selector and base rate query by pasting this code:

USE medical_analytics;

-- Compute the prior probability of diabetes
SELECT
    COUNT(*) AS total_patients,
    SUM(outcome) AS diabetic_count,
    SUM(outcome) / COUNT(*) AS base_rate,
    1 - (SUM(outcome) / COUNT(*)) AS complement
FROM patient_screening;

What does this query do?

This query computes P(Diabetes) directly from the data. SUM(outcome) counts every patient with a positive diabetes diagnosis (outcome = 1).

The complement column verifies a core probability axiom: P(A) + P(not A) = 1. If your base_rate + complement equals 1.0, the math checks out.

• Save 03_probability.sql.
• Switch to your MySQL terminal and run the file with this command:

source 03_probability.sql

Before you look at the output, what fraction of this population do you think has diabetes? Take a guess.

You should see: total_patients = 768, diabetic_count = 268, base_rate ≈ 0.349, and complement ≈ 0.651. About 34.9% of this population has diabetes.

Seeing an error when running source?

Make sure your MySQL terminal is still connected. If it disconnected, reconnect with mysql --local-infile=1 -u root -p and try again.

Check that your terminal's working directory is the same folder where 03_probability.sql lives. If not, provide the full path in the source command.

Help me debug my source command in MySQL.

Now break this down by age group. Knowing that diabetes prevalence varies by age will become important when you apply Bayes' theorem in the next step.

• Return to 03_probability.sql in VS Code.
• Add the age-group prevalence query below your first query:

-- Break down prevalence by age group
SELECT
    CASE
        WHEN age < 25 THEN 'Under 25'
        WHEN age BETWEEN 25 AND 40 THEN '25-40'
        WHEN age BETWEEN 41 AND 55 THEN '41-55'
        ELSE 'Over 55'
    END AS age_group,
    COUNT(*) AS total,
    SUM(outcome) AS diabetic,
    SUM(outcome) / COUNT(*) AS prevalence
FROM patient_screening
GROUP BY age_group
ORDER BY prevalence;

What does this query do?

The CASE WHEN expression assigns each patient to an age bucket. GROUP BY age_group then computes the prevalence within each bucket separately.

This reveals that "34.9% prevalence" is an average that hides dramatic variation across subgroups. Younger patients have much lower prevalence, and that difference will break screening test accuracy later.

• Save 03_probability.sql.
• Switch to your MySQL terminal and run the file again:

source 03_probability.sql

You should see four age groups sorted by prevalence. Notice how the "Under 25" group has significantly lower diabetes prevalence than the "Over 55" group.

Define a screening test and compute sensitivity and specificity

A screening test needs a threshold. You will define one now: if a patient's glucose reading is 140 or higher, the test says "positive for diabetes." The question is, how good is this rule?

You measure "how good" with two numbers. Sensitivity is the fraction of truly diabetic patients your test correctly catches. Specificity is the fraction of healthy patients your test correctly leaves alone.

• Return to 03_probability.sql in VS Code.
• Add the confusion matrix and screening metrics query below your age-group query:

-- Confusion matrix and screening metrics for glucose >= 140
SELECT
    SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) AS true_positives,
    SUM(CASE WHEN glucose >= 140 AND outcome = 0 THEN 1 ELSE 0 END) AS false_positives,
    SUM(CASE WHEN glucose < 140 AND outcome = 0 THEN 1 ELSE 0 END) AS true_negatives,
    SUM(CASE WHEN glucose < 140 AND outcome = 1 THEN 1 ELSE 0 END) AS false_negatives,
    SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) /
        (SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) +
         SUM(CASE WHEN glucose < 140 AND outcome = 1 THEN 1 ELSE 0 END)) AS sensitivity,
    SUM(CASE WHEN glucose < 140 AND outcome = 0 THEN 1 ELSE 0 END) /
        (SUM(CASE WHEN glucose < 140 AND outcome = 0 THEN 1 ELSE 0 END) +
         SUM(CASE WHEN glucose >= 140 AND outcome = 0 THEN 1 ELSE 0 END)) AS specificity
FROM patient_screening;

What does this query do?

Each CASE WHEN expression counts patients in one cell of the confusion matrix. True positives are patients where glucose >= 140 AND they actually have diabetes. False positives are patients where glucose >= 140 but they are healthy.

Sensitivity = TP / (TP + FN). It answers: "of all diabetic patients, how many did this threshold catch?" Specificity = TN / (TN + FP). It answers: "of all healthy patients, how many did this threshold correctly leave alone?"

• Save 03_probability.sql.
• Switch to your MySQL terminal and run the file:

source 03_probability.sql

You should see all four confusion matrix counts plus sensitivity and specificity as decimal values. Notice that with a glucose threshold of 140, sensitivity is moderate and specificity is fairly high.

Seeing NULL for sensitivity or specificity?

A NULL result means a division by zero occurred. Check that your patient_screening table has data by running SELECT COUNT(*) FROM patient_screening; first. If it returns 0, re-run your 02_load_data.sql file.

Help me debug NULL results in my sensitivity/specificity query.

Explore how threshold changes the trade-off

Your threshold of 140 is just one choice. Lowering it catches more diabetics (higher sensitivity) but also flags more healthy people (lower specificity). Raising it does the opposite. This is the ROC trade-off, and you can see it live.

• In your MySQL terminal, paste and run this query with a lower threshold of 120:

-- Lower threshold: catches more diabetics but more false alarms
SELECT
    SUM(CASE WHEN glucose >= 120 AND outcome = 1 THEN 1 ELSE 0 END) /
        (SUM(CASE WHEN glucose >= 120 AND outcome = 1 THEN 1 ELSE 0 END) +
         SUM(CASE WHEN glucose < 120 AND outcome = 1 THEN 1 ELSE 0 END)) AS sensitivity_120,
    SUM(CASE WHEN glucose < 120 AND outcome = 0 THEN 1 ELSE 0 END) /
        (SUM(CASE WHEN glucose < 120 AND outcome = 0 THEN 1 ELSE 0 END) +
         SUM(CASE WHEN glucose >= 120 AND outcome = 0 THEN 1 ELSE 0 END)) AS specificity_120
FROM patient_screening;

Compare these values to your threshold-140 results. Sensitivity went up, but specificity went down.

• Now run the same query with a higher threshold of 160:

-- Higher threshold: fewer false alarms but misses more diabetics
SELECT
    SUM(CASE WHEN glucose >= 160 AND outcome = 1 THEN 1 ELSE 0 END) /
        (SUM(CASE WHEN glucose >= 160 AND outcome = 1 THEN 1 ELSE 0 END) +
         SUM(CASE WHEN glucose < 160 AND outcome = 1 THEN 1 ELSE 0 END)) AS sensitivity_160,
    SUM(CASE WHEN glucose < 160 AND outcome = 0 THEN 1 ELSE 0 END) /
        (SUM(CASE WHEN glucose < 160 AND outcome = 0 THEN 1 ELSE 0 END) +
         SUM(CASE WHEN glucose >= 160 AND outcome = 0 THEN 1 ELSE 0 END)) AS specificity_160
FROM patient_screening;

Now you see the pattern. Specificity jumped up (fewer false alarms), but sensitivity dropped (more missed diabetics). There is no free lunch. Every threshold picks a point on this trade-off curve.

What is the ROC trade-off?

You just computed three points on what statisticians call a Receiver Operating Characteristic (ROC) curve. Each threshold gives you a different sensitivity/specificity pair. In clinical settings, choosing the threshold depends on which mistake is more dangerous: missing a diabetic patient or alarming a healthy one.

Before you move on, do one final check. What do you expect the base rate query to return if you run it one more time?

• Run the full file one more time to confirm everything works together:

source 03_probability.sql

You should see all three result sets: the base rate (268 out of 768, approximately 34.9%), the age-group breakdown, and the confusion matrix with sensitivity and specificity for the glucose >= 140 threshold.

✔️ Awesome, I've got everything!

Great. Make sure you have saved 03_probability.sql with all three queries (base rate, age-group breakdown, confusion matrix).

ⓧ I'd like to double check the full code

Your 03_probability.sql should contain these three sections in order:

• USE medical_analytics; at the top.
• The base rate SELECT with total_patients, diabetic_count, base_rate, and complement.
• The age-group SELECT with CASE WHEN, GROUP BY, and ORDER BY prevalence.
• The confusion matrix SELECT with all six computed columns: true_positives, false_positives, true_negatives, false_negatives, sensitivity, specificity.

Scroll up through this step and confirm each code block appears in your file, in order.

You now have the base rate and a way to measure any screening test's sensitivity and specificity. But here is the question that will break your intuition in the next step: if your test has 85% sensitivity, does that mean a positive result is 85% likely to be correct? The answer is no, and the gap between those numbers is exactly what Bayes' theorem explains.

Apply Bayes' Theorem to Break Your Intuition

In the previous step, you computed sensitivity and specificity for your glucose screening test. Those numbers told you how well the test catches diabetics and how well it avoids false alarms.

But here is the question a patient actually asks: "My test came back positive. Do I actually have diabetes?" That question flips the conditional probability. Answering it requires Bayes' theorem, and the result will break your intuition about what "accurate" means.

In this step, get ready to:

• Compute Positive Predictive Value (PPV) for your glucose threshold on the full dataset.
• Filter to a low-prevalence subgroup and watch PPV collapse.
• Implement the full Bayes formula in SQL and verify it matches the empirical result.

Compute Positive Predictive Value (PPV)

Sensitivity answers "of all the diabetics, how many did the test catch?" PPV answers the opposite: "of all the positive tests, how many are actually diabetic?" The formula is straightforward: TP / (TP + FP).

• In VS Code, create a new file called 04_bayes.sql in your project directory.
• Add the following query that computes PPV for the glucose >= 140 threshold on the full population:

USE medical_analytics;

-- PPV: of everyone who tested positive, how many are truly diabetic?
SELECT
    SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) /
    SUM(CASE WHEN glucose >= 140 THEN 1 ELSE 0 END) AS ppv_full_population
FROM patient_screening;

What does this query do?

The numerator counts true positives (glucose >= 140 AND actually diabetic). The denominator counts everyone who tested positive (glucose >= 140, regardless of actual outcome). Dividing gives you the probability that a positive test result is correct.

• Save 04_bayes.sql.
• Run this query in your MySQL session:

mysql --local-infile=1 -u root -p medical_analytics < 04_bayes.sql

You should see a ppv_full_population value around 0.60 to 0.65. Your screening test has roughly 85% sensitivity, but only about 60-65% of positive results are actually diabetic.

Why is PPV so much lower than sensitivity?

Sensitivity measures how well the test catches disease. PPV measures how trustworthy a positive result is. The gap between them exists because false positives dilute the positive pool. The size of that dilution depends on the base rate of the disease in the population.

Seeing NULL instead of a number?

If your result is NULL, the denominator is zero. This means no patients have glucose >= 140. Confirm you loaded data correctly by running SELECT COUNT(*) FROM patient_screening WHERE glucose >= 140; which should return a non-zero count.

help me debug a NULL result in my PPV query

Change the base rate and watch PPV collapse

Your overall dataset has a ~34.9% diabetes prevalence. That is unusually high for a general population. What happens to PPV when you screen a group where diabetes is less common?

• Add this query below the first one in 04_bayes.sql. It computes PPV for patients under 25 only:

-- Same test, younger population: what happens to PPV?
SELECT
    SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) /
    SUM(CASE WHEN glucose >= 140 THEN 1 ELSE 0 END) AS ppv_young_population,
    SUM(outcome) / COUNT(*) AS base_rate_young
FROM patient_screening
WHERE age < 25;

What does this query do?

This is the exact same PPV calculation as before, but filtered to patients under 25. It also outputs the base rate of this subgroup so you can see how prevalence drives the result.

• Save 04_bayes.sql.

Before you run this, predict: the test itself has not changed. Same glucose threshold, same sensitivity, same specificity. Do you think PPV will stay roughly the same, go up, or go down for younger patients?

• Run the updated file:

mysql --local-infile=1 -u root -p medical_analytics < 04_bayes.sql

The ppv_young_population drops below 0.40. The base_rate_young shows why: diabetes prevalence in this subgroup is much lower. The same test with the same threshold now produces mostly false positives in a low-prevalence group.

This is Bayes' theorem in action

The formula is: P(Disease | Positive Test) = [Sensitivity x Prevalence] / [Sensitivity x Prevalence + (1 - Specificity) x (1 - Prevalence)]. When prevalence drops, the numerator shrinks. The denominator stays relatively large because false positives dominate. This is why mass screening low-risk populations produces alarming numbers of false alarms.

Implement the full Bayes formula in SQL

You proved the intuition empirically by counting. Now you will implement the Bayes formula directly and confirm it produces the same number as your counting approach.

• Add this query at the bottom of 04_bayes.sql. It uses a CTE to compute all the ingredients, then applies the Bayes formula:

-- Bayes formula computed directly from the data
WITH metrics AS (
    SELECT
        SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) /
            (SUM(CASE WHEN glucose >= 140 AND outcome = 1 THEN 1 ELSE 0 END) +
             SUM(CASE WHEN glucose < 140 AND outcome = 1 THEN 1 ELSE 0 END)) AS sensitivity,
        SUM(CASE WHEN glucose < 140 AND outcome = 0 THEN 1 ELSE 0 END) /
            (SUM(CASE WHEN glucose < 140 AND outcome = 0 THEN 1 ELSE 0 END) +
             SUM(CASE WHEN glucose >= 140 AND outcome = 0 THEN 1 ELSE 0 END)) AS specificity,
        SUM(outcome) / COUNT(*) AS prevalence
    FROM patient_screening
)
SELECT
    sensitivity,
    specificity,
    prevalence,
    (sensitivity * prevalence) /
    (sensitivity * prevalence + (1 - specificity) * (1 - prevalence)) AS bayes_ppv
FROM metrics;

What does this query do?

The metrics CTE computes sensitivity, specificity, and prevalence in one pass over the data. The outer SELECT then plugs those values into the Bayes formula. This separates the "gather ingredients" step from the "apply formula" step, making the logic readable.

The bayes_ppv result should match your empirical ppv_full_population from the first query. If they match, you have proven the formula works on real data.

• Save 04_bayes.sql.

Before you run this, predict: should bayes_ppv match ppv_full_population from your first query, or will they differ?

• Run the final version:

mysql --local-infile=1 -u root -p medical_analytics < 04_bayes.sql

The bayes_ppv output matches your empirical ppv_full_population. The formula and the counting approach produce the same number, confirming Bayes' theorem on real patient data.

Numbers don't match exactly?

Tiny differences in the last few decimal places can appear due to floating-point division order. If both numbers agree to 2-3 decimal places, the formula is confirmed. If they differ significantly, check that your WITH metrics CTE computes sensitivity and specificity using the same threshold (glucose >= 140) as your first query.

help me debug my Bayes formula query

✔️ Awesome, I've got everything!

Great. Confirm you have saved 04_bayes.sql with all three queries.

ⓧ I'd like to double check the full code

Your 04_bayes.sql should contain these three query blocks in order. Scroll up and confirm each is present:

• The USE medical_analytics; statement and the ppv_full_population SELECT (counts true positives among all positive tests for the full dataset).
• The ppv_young_population SELECT with the WHERE age < 25 filter and base_rate_young column.
• The WITH metrics AS CTE computing sensitivity, specificity, prevalence, then the outer SELECT applying the Bayes formula as bayes_ppv.

You have just proven something that surprises even experienced clinicians. A screening test can have high sensitivity and still produce mostly false positives when the disease is rare. Next up, you will tackle a different kind of problem: traversing the ICD-10 code hierarchy with recursive SQL.

Traverse the ICD-10 Hierarchy with Recursive CTEs

Your Bayes queries proved that base rates change everything about a screening test's predictive value. Now you face a different kind of question: what is the total prevalence of diabetes across all its sub-types?

The ICD-10 hierarchy stores diabetes codes at multiple levels. A patient coded as E11.21 (diabetic nephropathy) is still a diabetes patient, but a simple WHERE parent_code = 'E08-E13' only finds direct children. You need a way to walk the entire tree. This step introduces recursive CTEs to solve that problem.

In this step, get ready to:

• Discover the limitation of flat SQL on hierarchical data.
• Write a recursive CTE that traverses the full ICD-10 tree automatically.
• Roll up patient prevalence across an entire code family with a single query.

Hit the wall with flat SQL

You want to find every code that falls under the "Diabetes mellitus" block. The obvious approach is to filter on parent_code. Let's see how far that gets you.

• In VS Code, create a new file called 05_recursive_cte.sql in the same folder as your other SQL files.
• Add the following query that tries to find all diabetes codes using a flat WHERE clause:

USE medical_analytics;

SELECT code, description
FROM icd10_code
WHERE parent_code = 'E08-E13';

What does this query do?

It asks for every row whose parent_code is E08-E13 (the Diabetes mellitus block). This should find direct children only.

• Save 05_recursive_cte.sql.
• In your MySQL client, run this query by executing:

source 05_recursive_cte.sql

Before you look at the result, think about what you expect. How many codes should appear if the query finds ALL diabetes-related codes in your hierarchy?

You should see only 6 rows returned: E08, E09, E10, E11, E12, and E13. These are the direct children of the Diabetes mellitus block.

Where are the grandchildren?

Codes like E11.2 (kidney complications) and E11.21 (diabetic nephropathy) are missing. They sit deeper in the tree because their parent is E11, not E08-E13.

To catch everything, you would need nested subqueries for each level. The ICD-10 tree goes 4-5 levels deep. This does not scale.

Why not just use multiple JOINs?

You could write a chain of LEFT JOINs, one per level. But you would need to know the maximum depth in advance. If the hierarchy changes, your query breaks. A recursive CTE handles arbitrary depth automatically.

Solve it with WITH RECURSIVE

A recursive CTE has two parts: an anchor query that picks your starting row, and a recursive query that finds children by joining back to the CTE itself. MySQL repeats the recursive part until no new rows appear.

• In 05_recursive_cte.sql, add the following recursive CTE below your existing flat query:

WITH RECURSIVE diabetes_tree AS (
    SELECT code, description, parent_code, hierarchy_level,
           CAST(code AS CHAR(200)) AS path
    FROM icd10_code
    WHERE code = 'E08-E13'

    UNION ALL

    SELECT child.code, child.description, child.parent_code, child.hierarchy_level,
           CONCAT(dt.path, ' > ', child.code)
    FROM icd10_code child
    INNER JOIN diabetes_tree dt ON child.parent_code = dt.code
)
SELECT
    CONCAT(REPEAT('  ', hierarchy_level), code) AS indented_code,
    description,
    path
FROM diabetes_tree
ORDER BY path;

What does this recursive CTE do?

• The anchor (WHERE code = 'E08-E13') starts at the Diabetes mellitus block.
• The recursive part joins icd10_code children to the CTE on child.parent_code = dt.code. Each iteration finds the next level of descendants.
• The path column uses CONCAT to build the full ancestry trail as the tree unfolds.
• The outer SELECT uses REPEAT(' ', hierarchy_level) to indent each code proportional to its depth.
• Ordering by path produces depth-first traversal so parents always appear before their children.

• Save 05_recursive_cte.sql and run it again in your MySQL client:

source 05_recursive_cte.sql

Do you think this query will return only the 6 direct children again, or more? Make your prediction before looking at the output.

You should now see the entire diabetes subtree unfolded. All 22 codes from E08-E13 down to E11.65 and E13.9 appear, indented to show their depth, with a path column showing the full trail from root to leaf.

Seeing an error about recursive references?

Make sure the keyword RECURSIVE appears after WITH. Without it, MySQL does not allow the CTE to reference itself in the recursive part.

Also check that the CTE name diabetes_tree in the INNER JOIN matches the name in the WITH RECURSIVE declaration exactly.

Help me debug my recursive CTE query.

Roll up prevalence across the hierarchy

The recursive CTE collects codes. Now you can use it to answer the original question: how many patients have a diagnosis anywhere in the diabetes subtree?

• Add this second recursive CTE at the bottom of 05_recursive_cte.sql:

WITH RECURSIVE diabetes_tree AS (
    SELECT code
    FROM icd10_code
    WHERE code = 'E08-E13'

    UNION ALL

    SELECT child.code
    FROM icd10_code child
    INNER JOIN diabetes_tree dt ON child.parent_code = dt.code
)
SELECT
    COUNT(DISTINCT d.patient_id) AS patients_in_diabetes_hierarchy
FROM diagnosis d
INNER JOIN diabetes_tree dt ON d.code = dt.code;

What does this query do?

The recursive CTE collects every code in the diabetes subtree. The outer query joins those codes to the diagnosis table and counts distinct patients. This catches patients regardless of which specificity level their code sits at.

Using COUNT(DISTINCT d.patient_id) ensures a patient diagnosed with multiple diabetes codes is only counted once.

• Save 05_recursive_cte.sql and run it one more time:

source 05_recursive_cte.sql

What number do you expect? Think about how many patients in your dataset had a positive diabetes outcome and received a diagnosis code in Step 1.

You should see patients_in_diabetes_hierarchy return 268. That matches the 268 positive-outcome patients who were each assigned code E11.9 in your 02_load_data.sql INSERT statement. The recursive CTE found them through the tree path E08-E13 > E11 > E11.9 without you listing any of those codes manually.

Getting 0 patients back?

Check that you ran 02_load_data.sql successfully in an earlier step. The INSERT into the diagnosis table depends on patient_screening having data with outcome = 1.

Also verify the join condition matches: d.code = dt.code. If you accidentally used d.code = dt.parent_code, the join will not find matches.

Help me debug why my prevalence rollup returns 0.

✔️ Awesome, I've got everything!

Great. Double check you have saved 05_recursive_cte.sql with all three queries in it.

ⓧ I'd like to double check the full code

Your 05_recursive_cte.sql should contain these three query blocks in order:

• The USE medical_analytics; statement followed by the flat SELECT on parent_code = 'E08-E13'.
• The full WITH RECURSIVE diabetes_tree CTE that selects indented_code, description, and path, ordered by path.
• The second WITH RECURSIVE diabetes_tree CTE that joins to diagnosis and selects COUNT(DISTINCT d.patient_id) AS patients_in_diabetes_hierarchy.

Scroll up and confirm each block matches the code you added in this step.

You can now traverse any branch of the ICD-10 tree with a single query, no matter how deep it goes. In the Secret Mission, you will add an index to this hierarchy and use EXPLAIN ANALYZE to prove the performance difference.

Secret mission

Index the Hierarchy and Prove It with EXPLAIN ANALYZE

Your recursive CTE traverses the ICD-10 hierarchy correctly, but it scans the entire icd10_code table at every recursion level. Can you add a B-tree index on parent_code and use EXPLAIN ANALYZE to prove the performance difference in milliseconds?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with MySQL, so there are no ongoing cloud costs.

Resources you used:

• The medical_analytics MySQL database (tables: patient_screening, icd10_code, diagnosis).
• SQL script files (01_schema.sql through 06_indexing.sql).
• Downloaded Pima Indians Diabetes CSV file.
• Downloaded ICD-10-CM code descriptions file.

Keep everything running

No action needed. Choose this if you are still actively building on this database or plan to extend it with more queries.

• Your medical_analytics database remains available whenever MySQL is running.
• Your SQL files stay on disk for future reference or modification.
• MySQL continues running in the background and uses some memory.

Pause - I'll come back to this later

Stop the MySQL server to free up memory. Your database and files remain intact for when you return.

• If you installed MySQL via Homebrew, stop the server by running:
• If you installed MySQL using the official macOS package, stop it by running:

For Homebrew installations:

brew services stop mysql

For official macOS package installations:

sudo /usr/local/mysql/support-files/mysql.server stop

Your database and all SQL files stay on disk. When you are ready to return, start MySQL again and pick up where you left off.

Delete - I don't want to use this again

Remove all project resources completely. This deletes the database and all local files you created.

• Connect to MySQL and drop the database by running:

mysql -u root -p

DROP DATABASE IF EXISTS medical_analytics;

• Exit the MySQL shell:

EXIT;

• Delete your SQL script files and downloaded data files. Navigate to the folder where you saved them and run:

rm 01_schema.sql 02_load_data.sql 03_probability.sql 04_bayes.sql 05_recursive_cte.sql 06_indexing.sql

• Delete the downloaded CSV and ICD-10 data files:

rm diabetes.csv

If you stored the ICD-10-CM descriptions file separately, delete that as well.

Nice Work!

Nice Work!

You just built a medical analytics database that answers real clinical questions using probability theory implemented as SQL queries. From 768 real patient records, you proved that a high-accuracy screening test can still produce mostly false positives when disease prevalence is low.

You learned how to:

• Design an EER model with self-referencing foreign keys and translate it into a relational schema that represents hierarchical medical code data.
• Compute base rates, sensitivity, specificity, and PPV entirely in SQL, then prove Bayes' theorem empirically by filtering to a low-prevalence subpopulation and watching predictive value collapse.
• Write recursive CTEs to traverse the ICD-10 hierarchy and aggregate prevalence across entire code families without manually listing every descendant.
• Use EXPLAIN ANALYZE to demonstrate B-tree index performance gains on recursive queries (Secret Mission).

Ready to quiz yourself?