Analyze Hospital Readmission Risk with SQL

Query synthetic hospital data to detect 30-day readmissions using SQL.

Introduction

30 Second Summary

Every time a patient returns to the hospital within 30 days of being discharged, it signals a gap in care. Hospitals track these readmissions obsessively because Medicare financially penalizes high rates.

In this project, you will build a SQL analytics pipeline that answers the question every hospital executive asks: which patients are most likely to be readmitted within 30 days? You will query CSV files directly with DuckDB, combine patient demographics with admission records, and detect readmission patterns using window functions.

What You'll Build

You will run a single SQL file in your terminal and instantly see a ranked list of departments by readmission rate, with every patient who returned within 30 days flagged and ready for care team review.

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

  • Department-level queries that reveal admission volume, average length of stay, and emergency ratios across the hospital.
  • Demographic breakdowns showing which age groups and insurance types correlate with the most frequent hospital visits.
  • A readmission detection pipeline using window functions and CTEs that flags every patient who returned within 30 days and calculates readmission rates by department.
  • Secret Mission: A multi-factor risk scoring model that assigns HIGH, MEDIUM, or LOW risk categories to every patient based on five weighted factors.

Are there any prerequisites?

You should be familiar with basic SQL concepts like SELECT, FROM, and WHERE. No tools need to be pre-installed. You will set everything up from scratch in Step 1.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your Analytics Environment

Before you can query hospital data, you need two tools on your machine: a code editor to write SQL files, and a database engine to run them.

In this step you will install VS Code for writing .sql files and DuckDB, a modern analytics database that runs locally as a single binary. No server setup, no accounts, no configuration.

In this step, get ready to:
  • Install VS Code with a SQL syntax highlighting extension.
  • Install the DuckDB CLI via Homebrew.
  • Verify DuckDB runs and can execute a query.
Install VS Code
  • Open your browser and go to https://code.visualstudio.com/.
  • Click the download button for macOS.
  • Once the download finishes, open the .zip file and drag Visual Studio Code into your Applications folder.
  • Press Cmd+Space to open Spotlight.
  • Type Visual Studio Code and press Enter to launch it.

VS Code opens with a Welcome tab. You now have a code editor ready for writing SQL.

✔️ I already have VS Code installed

Great. Open VS Code and continue to the extension install below.

ⓧ I just installed it

You should see the VS Code Welcome tab. Continue below.

Next, add a SQL extension for syntax highlighting in your .sql files.

  • Click the Extensions icon in the left sidebar (or press Cmd+Shift+X).
  • Type SQL in the search bar.
  • Find the extension named SQL by weSQL and click Install.

You should see a blue Installed badge appear next to the extension name.

Why install a SQL extension?

VS Code highlights SQL keywords in different colours, making your queries much easier to read. Without this extension, your .sql files appear as plain text with no visual structure.

Install the DuckDB CLI

DuckDB is installed through Homebrew, the standard macOS package manager. First, confirm Homebrew is available on your machine.

  • Press Cmd+Space to open Spotlight.
  • Type Terminal and press Enter to open it.
  • Check if Homebrew is installed by running this command:
brew --version

✔️ I see a version number

Homebrew is ready. Continue to the DuckDB install below.

ⓧ Command not found

Homebrew is not installed yet. Install it by running this command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

What does this command do?

This downloads and runs the official Homebrew installer from brew.sh. It will ask for your macOS password and show progress as it installs.

Once the install finishes, close and reopen Terminal so the brew command is available.

  • Install DuckDB by running this command:
brew install duckdb

Homebrew downloads and installs the DuckDB binary. You should see progress output ending with a success message.

Seeing an error during install?

If you see a permission error, try running sudo brew install duckdb and entering your password.

If brew is still not found after installing Homebrew, make sure you closed and reopened Terminal.

Why can't I install DuckDB?

Verify DuckDB works

With the install complete, confirm DuckDB is ready to use.

  • Before you run the next command, what do you expect to see if DuckDB installed correctly?
  • Check the installed version by running:
duckdb --version

✔️ I see a version number

You should see something like v1.5.5 (or a similar recent version). DuckDB is installed and ready.

ⓧ Command not found

Terminal cannot find the duckdb binary. Try closing and reopening Terminal, then run duckdb --version again.

If it still fails, run brew install duckdb again and check for error messages.

Help me get DuckDB working

Now launch DuckDB and run a test query to confirm it can execute SQL.

  • Start DuckDB by running:
duckdb

What is happening when you run duckdb?

When you type duckdb with no arguments, it opens an in-memory database. This means your queries run instantly with no files saved to disk.

You will see a D prompt, which means DuckDB is waiting for SQL input.

  • Run this test query at the D prompt:
SELECT 'DuckDB is ready!' AS status;

You should see a single-row result with the text DuckDB is ready! in the status column.

  • Exit DuckDB by typing:
.quit

You are back at your normal Terminal prompt.

DuckDB not responding to .quit?

Make sure you type the dot before quit. The dot commands are special DuckDB CLI instructions, not SQL.

Help me exit DuckDB

Your analytics environment is ready. Next up, you will create the hospital dataset and write your first exploration queries.

Load and Explore the Hospital Dataset

Your analytics environment is ready. Now you need data to analyze.

In this step, you will create two CSV files representing hospital records and immediately query them with DuckDB. DuckDB can query CSV files directly without any import or table-creation step.

In this step, get ready to:
  • Create a project folder with patient and admission data files.
  • Write SQL queries that explore the dataset.
  • Run those queries in DuckDB and see results printed from the CSV files.
Create the project folder and data files

You need a dedicated folder on your Desktop to hold the hospital data and your SQL files.

  • Move to your Desktop by running this command in your terminal:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir hospital-analytics
cd hospital-analytics
  • Open this folder in VS Code by running:
code .

Command not found for code?

If code . does not work, open VS Code manually. Press Cmd+Space to open Spotlight, type Visual Studio Code, and press Enter. Then click File in the top menu, click Open Folder, navigate to Desktop > hospital-analytics, and click Open.

help me open a folder in VS Code on macOS

You should now see an empty hospital-analytics folder open in the VS Code Explorer sidebar.

  • Create a new file by right-clicking in the Explorer sidebar on the left.
  • Click New File.
  • Name the file patients.csv and press Enter.
  • Paste the following patient data into the file:
patient_id,first_name,last_name,date_of_birth,gender,zip_code,insurance_type
P001,Maria,Santos,1952-03-14,F,02101,Medicare
P002,James,Chen,1968-07-22,M,02102,Private
P003,Aisha,Johnson,1945-11-30,F,02103,Medicare
P004,Robert,Williams,1978-05-08,M,02104,Private
P005,Elena,Petrov,1960-02-19,F,02101,Medicaid
P006,David,Kim,1955-09-03,M,02102,Medicare
P007,Sarah,O'Brien,1972-12-25,F,02103,Private
P008,Michael,Davis,1948-06-17,M,02104,Medicare
P009,Linda,Martinez,1965-04-11,F,02101,Medicaid
P010,Thomas,Anderson,1958-08-29,M,02102,Medicare
P011,Patricia,Lee,1943-01-07,F,02103,Medicare
P012,William,Taylor,1975-10-14,M,02104,Private
P013,Dorothy,Wilson,1950-12-02,F,02101,Medicare
P014,Richard,Brown,1962-03-28,M,02102,Medicaid
P015,Margaret,Garcia,1947-07-19,F,02103,Medicare
P016,Joseph,Robinson,1970-11-05,M,02104,Private
P017,Karen,Thompson,1953-09-21,F,02101,Medicare
P018,Charles,White,1967-02-14,M,02102,Private
P019,Nancy,Harris,1944-05-30,F,02103,Medicare
P020,Daniel,Clark,1980-08-12,M,02104,Private

What is this data?

This file contains 20 synthetic patients. Each row has a unique patient_id, demographic information (name, date of birth, gender, zip code), and an insurance_type (Medicare, Private, or Medicaid).

Later, you will JOIN this data with admission records to answer questions about which demographics correlate with frequent hospital visits.

  • Save patients.csv with Cmd+S.
  • Confirm the file appears in the Explorer sidebar on the left.
  • Create another new file by right-clicking in the Explorer sidebar.
  • Click New File.
  • Name the file admissions.csv and press Enter.
  • Paste the following admission data into the file (this is the first half):
admission_id,patient_id,admit_date,discharge_date,department,diagnosis,admission_type
A001,P001,2024-01-05,2024-01-09,Cardiology,Heart Failure,Emergency
A002,P002,2024-01-08,2024-01-10,Orthopedics,Hip Fracture,Emergency
A003,P003,2024-01-12,2024-01-18,Pulmonology,Pneumonia,Emergency
A004,P001,2024-01-28,2024-02-01,Cardiology,Heart Failure,Emergency
A005,P004,2024-02-03,2024-02-05,General Surgery,Appendicitis,Emergency
A006,P005,2024-02-10,2024-02-14,Endocrinology,Diabetes Complications,Scheduled
A007,P003,2024-02-05,2024-02-10,Pulmonology,Pneumonia,Emergency
A008,P006,2024-02-15,2024-02-19,Cardiology,Arrhythmia,Scheduled
A009,P007,2024-02-20,2024-02-22,Orthopedics,Knee Replacement,Scheduled
A010,P008,2024-02-25,2024-03-02,Neurology,Stroke,Emergency
A011,P001,2024-03-01,2024-03-04,Cardiology,Heart Failure,Emergency
A012,P009,2024-03-05,2024-03-08,Endocrinology,Diabetes Complications,Emergency
A013,P010,2024-03-10,2024-03-14,Cardiology,Heart Failure,Scheduled
A014,P011,2024-03-15,2024-03-22,Pulmonology,COPD Exacerbation,Emergency
A015,P005,2024-03-12,2024-03-15,Endocrinology,Diabetes Complications,Emergency
A016,P012,2024-03-20,2024-03-22,General Surgery,Hernia Repair,Scheduled
A017,P013,2024-03-25,2024-03-30,Neurology,Stroke,Emergency
A018,P008,2024-03-20,2024-03-24,Neurology,Stroke,Emergency
A019,P014,2024-04-01,2024-04-04,Cardiology,Arrhythmia,Scheduled
A020,P015,2024-04-05,2024-04-12,Pulmonology,COPD Exacerbation,Emergency
A021,P003,2024-04-08,2024-04-12,Pulmonology,Pneumonia,Emergency
A022,P016,2024-04-10,2024-04-12,Orthopedics,Knee Replacement,Scheduled
A023,P017,2024-04-15,2024-04-19,Cardiology,Heart Failure,Emergency
A024,P010,2024-04-10,2024-04-13,Cardiology,Heart Failure,Emergency
A025,P018,2024-04-20,2024-04-22,General Surgery,Gallbladder Removal,Scheduled
  • Immediately below the last row (A025), continue pasting the remaining admission records:
A026,P019,2024-04-25,2024-05-02,Neurology,Stroke,Emergency
A027,P011,2024-04-10,2024-04-14,Pulmonology,COPD Exacerbation,Emergency
A028,P020,2024-05-01,2024-05-03,Orthopedics,Sports Injury,Emergency
A029,P006,2024-05-05,2024-05-09,Cardiology,Heart Failure,Emergency
A030,P013,2024-04-20,2024-04-24,Neurology,Stroke,Emergency
A031,P001,2024-05-10,2024-05-14,Cardiology,Heart Failure,Emergency
A032,P015,2024-05-01,2024-05-05,Pulmonology,COPD Exacerbation,Emergency
A033,P009,2024-04-02,2024-04-05,Endocrinology,Diabetes Complications,Scheduled
A034,P017,2024-05-10,2024-05-13,Cardiology,Heart Failure,Emergency
A035,P014,2024-05-15,2024-05-18,Cardiology,Arrhythmia,Emergency
A036,P011,2024-05-08,2024-05-12,Pulmonology,COPD Exacerbation,Emergency
A037,P008,2024-05-20,2024-05-25,Neurology,Stroke,Emergency
A038,P005,2024-05-18,2024-05-21,Endocrinology,Diabetes Complications,Emergency
A039,P019,2024-05-22,2024-05-27,Neurology,Stroke,Emergency
A040,P006,2024-06-01,2024-06-04,Cardiology,Arrhythmia,Scheduled
A041,P003,2024-06-05,2024-06-09,Pulmonology,COPD Exacerbation,Emergency
A042,P010,2024-06-08,2024-06-11,Cardiology,Heart Failure,Emergency
A043,P012,2024-06-10,2024-06-12,General Surgery,Hernia Repair,Scheduled
A044,P017,2024-06-12,2024-06-15,Cardiology,Heart Failure,Emergency
A045,P020,2024-06-15,2024-06-17,Orthopedics,Sports Injury,Emergency
A046,P013,2024-06-18,2024-06-22,Neurology,TIA,Emergency
A047,P015,2024-06-20,2024-06-25,Pulmonology,COPD Exacerbation,Emergency
A048,P001,2024-06-25,2024-06-28,Cardiology,Heart Failure,Emergency
A049,P008,2024-06-22,2024-06-26,Neurology,TIA,Scheduled
A050,P011,2024-06-28,2024-07-03,Pulmonology,Pneumonia,Emergency

What is this data?

This file contains 50 hospital admissions. Each row links to a patient_id from the patients file, and records the admission and discharge dates, which department handled the case, the diagnosis, and whether it was an Emergency or Scheduled visit.

Notice that some patients appear multiple times. Patient P001 (Maria Santos) has 5 admissions. These repeated visits are what you will later detect as potential readmissions.

  • Save admissions.csv with Cmd+S.
  • Confirm you see both patients.csv and admissions.csv listed in the VS Code Explorer sidebar.

✔️ I see both files in the sidebar

Your data is ready. You have 20 patients and 50 admissions to work with.

ⓧ I only see one file or none

Make sure you created both files inside the hospital-analytics folder, not outside it. If a file is missing, right-click in the Explorer sidebar, click New File, and create the missing CSV with the correct name.

help me find where my CSV files were saved

Write your first exploration queries

Before doing any analysis, you want to understand the shape of your data. How many records are there? What departments and diagnoses exist?

  • Create a new file in VS Code called explore.sql by right-clicking in the Explorer sidebar and clicking New File.
  • Paste the following SQL queries into the file:
-- Explore the hospital dataset
-- DuckDB queries CSV files directly - no import needed

-- Preview patient demographics
SELECT * FROM 'patients.csv' LIMIT 10;

-- Total patients in the dataset
SELECT COUNT(*) AS total_patients FROM 'patients.csv';

-- Preview admissions
SELECT * FROM 'admissions.csv' LIMIT 10;

-- Total admissions
SELECT COUNT(*) AS total_admissions FROM 'admissions.csv';

-- What departments exist?
SELECT DISTINCT department FROM 'admissions.csv' ORDER BY department;

-- What diagnoses are in the data?
SELECT DISTINCT diagnosis FROM 'admissions.csv' ORDER BY diagnosis;

What do these queries do?

  • SELECT * FROM 'patients.csv' LIMIT 10 reads the CSV file directly and shows the first 10 rows. No import step needed.
  • COUNT(*) gives you the total number of rows in each file.
  • SELECT DISTINCT shows every unique value in a column. This reveals what departments and diagnoses exist without duplicates.
  • Save explore.sql with Cmd+S.

✔️ Awesome, I've got everything!

Great. Make sure you have saved all three files.

ⓧ I'd like to double check the full code

Your explore.sql should contain exactly this:

-- Explore the hospital dataset
-- DuckDB queries CSV files directly - no import needed

-- Preview patient demographics
SELECT * FROM 'patients.csv' LIMIT 10;

-- Total patients in the dataset
SELECT COUNT(*) AS total_patients FROM 'patients.csv';

-- Preview admissions
SELECT * FROM 'admissions.csv' LIMIT 10;

-- Total admissions
SELECT COUNT(*) AS total_admissions FROM 'admissions.csv';

-- What departments exist?
SELECT DISTINCT department FROM 'admissions.csv' ORDER BY department;

-- What diagnoses are in the data?
SELECT DISTINCT diagnosis FROM 'admissions.csv' ORDER BY diagnosis;
Run the queries in DuckDB

Now you will run the SQL file against your CSV data using DuckDB's command-line interface.

  • Open a terminal inside VS Code by pressing Ctrl+` (the backtick key, next to the number 1).

Why use the VS Code terminal?

The integrated terminal automatically starts in the hospital-analytics folder because that is the workspace VS Code has open. This means DuckDB will find your CSV files without needing any extra path configuration.

  • Launch DuckDB by running:
duckdb

You should see the DuckDB prompt (D) appear, ready for queries.

Before you run the exploration file, what do you expect to see? Think about what six queries are in the file and what kind of output each one will produce.

  • Run all your exploration queries at once by entering:
.read explore.sql

You should see multiple result tables printed one after another. The first shows 10 rows of patient demographics. The second prints total_patients = 20. The third shows 10 admission records. The fourth prints total_admissions = 50. The final two list every unique department and diagnosis in the dataset.

Seeing an error about the file not found?

If DuckDB says it cannot find patients.csv or admissions.csv, your terminal is not in the same folder as the CSV files. Type .quit to exit DuckDB, then run pwd to check your current directory. It should end with hospital-analytics. If not, run cd ~/Desktop/hospital-analytics and launch duckdb again.

If DuckDB says it cannot find explore.sql, make sure you saved the file in the hospital-analytics folder. Check the VS Code Explorer sidebar to confirm it is listed alongside your CSV files.

DuckDB cannot find my CSV files when I run .read explore.sql

You now have a working local analytics environment. You loaded hospital data from CSV files and explored it with zero server configuration. Next up, you will use GROUP BY to answer your first real business question: which departments are the busiest?

Analyze Admission Patterns by Department

Your exploration queries confirmed the dataset is loaded and ready. Now it's time to answer the first question any hospital analyst tackles: "Where is the volume?"

Knowing which departments are busiest, which ones keep patients longest, and which ones handle the most emergencies gives hospital leadership the foundation for staffing and resource decisions. GROUP BY aggregations are the core SQL tool for collapsing row-level records into these department-level summaries.

In this step, get ready to:
  • Count admissions and unique patients per department.
  • Calculate average length of stay using date arithmetic.
  • Break down emergency vs. scheduled admissions by department.
Write department aggregation queries

The first analysis any hospital dashboard shows is admission volume by department. You need to know which departments see the most patients and whether those are the same patients returning or new ones each time.

  • In VS Code, create a new file called department_analysis.sql inside your hospital-analytics folder.
  • Add the file header and the first query that counts admissions per department:
-- Department-level admission analysis
-- Which departments are busiest? What's the average length of stay?

-- Total admissions and unique patients by department
SELECT
    department,
    COUNT(*) AS total_admissions,
    COUNT(DISTINCT patient_id) AS unique_patients
FROM 'admissions.csv'
GROUP BY department
ORDER BY total_admissions DESC;

What does this query do?

GROUP BY department collapses all 50 admission rows into one row per department. COUNT(*) counts total admissions in each group, while COUNT(DISTINCT patient_id) counts unique patients.

The difference between these two numbers tells you something important. If a department has 16 admissions but only 8 unique patients, those patients are coming back repeatedly.

  • Save department_analysis.sql.
  • Switch back to your terminal where DuckDB is running and execute the file by running:
.read department_analysis.sql

You should see Cardiology at the top with 16 total admissions, followed by Pulmonology and Neurology.

Seeing an error about the file?

Make sure you saved the file in VS Code before running .read. Also confirm your terminal is still in the hospital-analytics folder. You can check by running .quit, then ls to see your files, then relaunch with duckdb.

Help me debug a .read file not found error in DuckDB

Now you know which departments are busiest by volume. The next question is how long patients stay in each one.

  • Back in VS Code, add this query below your first one in department_analysis.sql to calculate average length of stay:
-- Average length of stay by department (in days)
SELECT
    department,
    ROUND(AVG(discharge_date - admit_date), 1) AS avg_los_days,
    MIN(discharge_date - admit_date) AS min_los,
    MAX(discharge_date - admit_date) AS max_los,
    COUNT(*) AS total_admissions
FROM 'admissions.csv'
GROUP BY department
ORDER BY avg_los_days DESC;

How does date subtraction work here?

discharge_date - admit_date returns the number of days between two dates as an integer. DuckDB auto-detects the YYYY-MM-DD format in your CSV as DATE columns, so the subtraction just works.

ROUND(..., 1) rounds the average to one decimal place. The MIN and MAX show the range of stays in each department, revealing whether the average is driven by a few extreme cases or consistent across patients.

  • Save department_analysis.sql and run .read department_analysis.sql again in DuckDB.

You should now see a second result table showing Neurology at the top with the longest average length of stay. This makes clinical sense because stroke patients require extended monitoring.

Seeing a syntax error?

Check that each query ends with a semicolon. Also verify there is no missing comma between column expressions in the SELECT clause. Each column (department, ROUND(...), MIN(...)) needs a comma after it except the last one.

Help me fix a syntax error in my DuckDB SQL query

Break down emergency vs. scheduled admissions

Volume and length of stay tell part of the story. The other key dimension is whether admissions are emergencies (unplanned) or scheduled procedures. Departments with high emergency ratios face unpredictable surges that make staffing harder.

  • Add this query at the bottom of department_analysis.sql to break down admission types per department:
-- Emergency vs Scheduled admissions by department
SELECT
    department,
    admission_type,
    COUNT(*) AS admission_count
FROM 'admissions.csv'
GROUP BY department, admission_type
ORDER BY department, admission_count DESC;

What does grouping by two columns do?

GROUP BY department, admission_type creates one row for every unique combination of department and admission type. So Cardiology gets two rows: one for Emergency and one for Scheduled.

Ordering by admission_count DESC within each department puts the dominant type first, so you can instantly see whether a department is primarily emergency-driven or scheduled.

  • Save department_analysis.sql.
Run and interpret the full analysis

Your file now contains three queries that paint a complete picture of department-level workload. Before you run it, think about what you expect to see.

  • Based on the admission volume results you already saw, which department do you think has the highest emergency ratio? Make a mental prediction.
  • Run the full file in DuckDB:
.read department_analysis.sql

You should see three result tables. The emergency breakdown reveals that high-volume departments like Cardiology and Pulmonology are overwhelmingly emergency-driven.

What do these results tell a hospital analyst?

Cardiology leads in volume (16 admissions) and is heavily skewed toward emergencies. Neurology keeps patients the longest (averaging 5+ days per stay). These are the two departments a hospital CFO would focus on first.

But notice a gap in what you can answer. You know WHERE the workload is, but you cannot yet answer WHO is most at risk. Are Medicare patients driving these numbers? Are older patients readmitted more? That information lives in patients.csv, and your queries only touch admissions.csv.

✔️ Awesome, I've got everything!

Great. Double check that you have saved department_analysis.sql with all three queries.

ⓧ I'd like to double check the full code

Scroll up and confirm your department_analysis.sql contains these three sections in order:

  • The file header comments ("Department-level admission analysis").
  • Query 1: total admissions and unique patients per department, grouped by department, ordered by total_admissions DESC.
  • Query 2: average, min, and max length of stay per department using discharge_date - admit_date, ordered by avg_los_days DESC.
  • Query 3: emergency vs. scheduled admission counts grouped by department, admission_type, ordered by department then count descending.

You now have a clear picture of departmental workload. Next up, you will link patient demographics to these admission records using JOINs to answer the question your current queries cannot: which patient populations are driving these numbers.

Combine Patient Demographics with Admissions

Your department analysis revealed that Cardiology handles the most admissions and Neurology has the longest average stays.

But you hit a wall. Questions like "do Medicare patients visit more often?" or "which age group is most at risk?" are unanswerable because patient demographics live in patients.csv while admission records live in admissions.csv. JOINs solve this by linking the two tables on a shared column.

In this step, get ready to:
  • Write JOIN queries that combine patient demographics with admission records.
  • Use CASE statements to bucket patients into age groups and reveal demographic patterns.
  • Identify the top 5 "frequent flyer" patients by total admissions.
Write your first JOIN query

A JOIN lets you combine rows from two tables based on a column they share. Both of your CSV files have a patient_id column. That shared column is the link between who the patient is and what happened during their hospital visit.

  • Switch back to VS Code and create a new file called demographic_analysis.sql inside your hospital-analytics folder.
  • Add the following query that joins admissions to patients and groups results by insurance type:
-- Combine patient demographics with admission data using JOINs
-- Answer: which demographics have the highest admission rates?

-- Admissions by insurance type
SELECT
    p.insurance_type,
    COUNT(*) AS total_admissions,
    COUNT(DISTINCT p.patient_id) AS unique_patients,
    ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT p.patient_id), 1) AS avg_admissions_per_patient
FROM 'admissions.csv' AS a
JOIN 'patients.csv' AS p ON a.patient_id = p.patient_id
GROUP BY p.insurance_type
ORDER BY avg_admissions_per_patient DESC;

What does this query do?

  • FROM 'admissions.csv' AS a JOIN 'patients.csv' AS p ON a.patient_id = p.patient_id connects every admission row to its matching patient row using the shared patient_id column.
  • GROUP BY p.insurance_type collapses all rows into one row per insurance type (Medicare, Private, Medicaid).
  • ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT p.patient_id), 1) calculates the average number of admissions per patient within each insurance group.
  • Save demographic_analysis.sql.
  • Return to your DuckDB session in Terminal and run the file:
.read demographic_analysis.sql

You should see a table with three rows. Medicare patients have the highest average admissions per patient, followed by Medicaid, then Private.

Seeing an error about the file?

  • Make sure you saved the file in the hospital-analytics folder (the same folder where your DuckDB session is running).
  • Check for typos in the filename. The .read command needs the exact filename including the .sql extension.
  • Why is my JOIN query failing?
Add age group analysis

Insurance type is one demographic lens. Age is another. A CASE statement inside your SELECT lets you bucket each patient into an age group based on their date of birth, turning a continuous value into categories you can group by.

  • In VS Code, add the following query below the insurance query in demographic_analysis.sql:
-- Admissions by age group and gender
SELECT
    CASE
        WHEN EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM p.date_of_birth) >= 75 THEN '75+'
        WHEN EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM p.date_of_birth) >= 65 THEN '65-74'
        WHEN EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM p.date_of_birth) >= 50 THEN '50-64'
        ELSE 'Under 50'
    END AS age_group,
    p.gender,
    COUNT(*) AS total_admissions,
    COUNT(DISTINCT a.patient_id) AS unique_patients
FROM 'admissions.csv' AS a
JOIN 'patients.csv' AS p ON a.patient_id = p.patient_id
GROUP BY age_group, p.gender
ORDER BY total_admissions DESC;

What does this query do?

  • EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM p.date_of_birth) calculates each patient's approximate age in years.
  • The CASE statement checks each age against thresholds (75, 65, 50) and assigns a label. DuckDB evaluates CASE top to bottom, so a 78-year-old matches the first condition and stops.
  • GROUP BY age_group, p.gender produces one row per combination (e.g. 75+ Female, 75+ Male, 65-74 Female).
  • Save demographic_analysis.sql.
  • Return to DuckDB and run the file again:
.read demographic_analysis.sql

You should see the insurance type results from before, followed by a new table showing age group and gender combinations. The 75+ group accounts for the most total admissions.

Age values look wrong?

  • The age calculation uses the current date, so your exact numbers will differ slightly from someone running this in a different year. The groupings (75+, 65-74, etc.) should still hold.
  • Check that you have EXTRACT(YEAR FROM CURRENT_DATE) and not just CURRENT_DATE alone. You need the year extraction on both sides of the subtraction.
  • Why are my age group results unexpected?
Find the frequent flyers

Insurance and age group analysis reveals patterns across populations. But a care coordinator also needs to know which specific individuals visit most often. These "frequent flyers" are the patients a hospital would flag for outreach programs.

  • Add the following query at the bottom of demographic_analysis.sql:
-- Top 5 patients by number of admissions (frequent flyers)
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    p.insurance_type,
    EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM p.date_of_birth) AS age,
    COUNT(*) AS total_admissions
FROM 'admissions.csv' AS a
JOIN 'patients.csv' AS p ON a.patient_id = p.patient_id
GROUP BY p.patient_id, patient_name, p.insurance_type, age
ORDER BY total_admissions DESC
LIMIT 5;

What does this query do?

  • p.first_name || ' ' || p.last_name AS patient_name concatenates first and last name into a single readable column.
  • GROUP BY groups all admissions for the same patient together so COUNT(*) counts how many times each patient was admitted.
  • ORDER BY total_admissions DESC LIMIT 5 sorts by most admissions first and keeps only the top 5 results.
  • Save demographic_analysis.sql.

Before you run the full file, predict: based on the admissions data you have explored so far, which patient do you think has the most admissions?

  • Run the complete analysis file in DuckDB:
.read demographic_analysis.sql

You should see three result tables. Medicare patients have the highest average admissions per patient. The 75+ age group accounts for the most total admissions. Patient P001 (Maria Santos) tops the frequent flyer list with 5 admissions.

Not seeing three result tables?

  • Each query must end with a semicolon. A missing semicolon causes DuckDB to treat multiple queries as one incomplete statement.
  • Make sure there is a blank line between each query for readability, though DuckDB only requires the semicolons to separate them.
  • Why is DuckDB only showing one result table?

✔️ Awesome, I've got everything!

Great. Double check you have saved demographic_analysis.sql with all three queries.

ⓧ I'd like to double check the full code

Your demographic_analysis.sql file should contain three queries in this order. Scroll up and confirm each one is present:

  • The file header comments ("Combine patient demographics..." and "Answer: which demographics...").
  • Query 1: Admissions by insurance type. Starts with -- Admissions by insurance type and JOINs both CSVs, grouping by p.insurance_type.
  • Query 2: Admissions by age group and gender. Starts with -- Admissions by age group and gender and uses a CASE statement with four age buckets.
  • Query 3: Top 5 frequent flyers. Starts with -- Top 5 patients by number of admissions (frequent flyers) and uses LIMIT 5.

You can now answer demographic questions that were impossible with admission data alone. Next, you will tackle the most critical metric in hospital analytics: detecting 30-day readmissions using window functions.

Detect 30-Day Readmissions with Window Functions

Your demographic analysis revealed which patient groups visit most often. Now you need to answer a harder question: which patients came back too soon?

A 30-day readmission means a patient returned to the hospital within 30 days of being discharged. This is the key quality metric hospitals track because Medicare financially penalizes hospitals with high readmission rates.

Window functions let you compare each admission to the previous one for the same patient without a self-JOIN. You will use the LAG function to pull each patient's prior discharge date onto the current row, then check if the gap is 30 days or fewer.

In this step, get ready to:
  • Use LAG window functions to find each patient's previous discharge date.
  • Flag readmissions where a patient returned within 30 days.
  • Calculate readmission rates by department to identify which units need attention.
Use LAG to find previous discharge dates

To detect a readmission, you need to compare each admission's admit_date against the same patient's previous discharge_date. The LAG window function reaches back one row (within a partition of rows for the same patient) and pulls a value from that previous row onto the current row.

  • In VS Code, create a new file called readmission_analysis.sql inside your hospital-analytics folder.
  • Add the file header comments and the first CTE by pasting this code:
-- Detect 30-day readmissions using window functions
-- A readmission = same patient admitted within 30 days of prior discharge

-- Find all readmission events
WITH ordered_admissions AS (
    SELECT
        patient_id,
        admission_id,
        admit_date,
        discharge_date,
        department,
        diagnosis,
        LAG(discharge_date) OVER (
            PARTITION BY patient_id
            ORDER BY admit_date
        ) AS prev_discharge_date
    FROM 'admissions.csv'
),

What does this CTE do?

  • PARTITION BY patient_id groups rows so LAG only looks at the same patient's previous admissions, not all patients.
  • ORDER BY admit_date sorts each patient's admissions chronologically so LAG pulls the correct previous discharge.
  • LAG(discharge_date) reaches back one row and copies that row's discharge date onto the current row as prev_discharge_date. For a patient's first admission, this value is NULL (no prior visit exists).

What is a CTE?

A Common Table Expression (CTE) is a named temporary result set defined with WITH. It exists only for the duration of the query. You can chain multiple CTEs together, with each one building on the previous, making complex logic readable step by step.

Flag readmissions with a CASE statement

With the previous discharge date now available on each row, you can calculate the gap in days and flag any admission where the patient returned within 30 days.

  • Directly below the trailing comma of your ordered_admissions CTE, add the readmissions CTE and the final SELECT by pasting this code:
readmissions AS (
    SELECT
        *,
        admit_date - prev_discharge_date AS days_since_last_discharge,
        CASE
            WHEN prev_discharge_date IS NOT NULL
                AND admit_date - prev_discharge_date <= 30
            THEN 1
            ELSE 0
        END AS is_readmission
    FROM ordered_admissions
)
SELECT
    patient_id,
    admission_id,
    admit_date,
    discharge_date,
    department,
    diagnosis,
    days_since_last_discharge,
    is_readmission
FROM readmissions
WHERE is_readmission = 1
ORDER BY patient_id, admit_date;

What does this code do?

  • admit_date - prev_discharge_date calculates the number of days between the patient's last discharge and their new admission. DuckDB returns an integer for date subtraction.
  • The CASE statement checks two conditions: that a previous discharge exists (not NULL) and that the gap is 30 days or fewer. If both are true, it flags the row as 1 (readmission).
  • The final SELECT filters to only rows where is_readmission = 1, giving you a clean list of every 30-day readmission event.
  • Save readmission_analysis.sql.
  • Switch back to your Terminal where DuckDB is running and execute the file by running:
.read readmission_analysis.sql

You should see a table of readmission events. Each row shows a patient who returned to the hospital within 30 days of their previous discharge, with the days_since_last_discharge column showing values of 30 or fewer.

Seeing a syntax error?

  • Check that the comma after the closing parenthesis of the first CTE is present. CTEs are separated by commas, but the last CTE before SELECT has no comma.
  • Make sure your CASE statement ends with END. A missing END is the most common cause of CTE errors.
  • Confirm the file is saved before running .read. DuckDB reads from disk, not your editor buffer.

Help me debug my readmission CTE query in DuckDB.

Calculate readmission rates by department

You can now see individual readmission events, but hospital executives want a summary: which departments have the highest readmission rates? This requires grouping all admissions by department and calculating what percentage were readmissions.

  • In readmission_analysis.sql, add a blank line below your first query's semicolon, then paste the CTE definitions for the department rate query:
-- Readmission rate by department
WITH ordered_admissions AS (
    SELECT
        patient_id,
        admission_id,
        admit_date,
        discharge_date,
        department,
        LAG(discharge_date) OVER (
            PARTITION BY patient_id
            ORDER BY admit_date
        ) AS prev_discharge_date
    FROM 'admissions.csv'
),
readmission_flags AS (
    SELECT
        *,
        CASE
            WHEN prev_discharge_date IS NOT NULL
                AND admit_date - prev_discharge_date <= 30
            THEN 1
            ELSE 0
        END AS is_readmission
    FROM ordered_admissions
)

Why repeat the ordered_admissions CTE?

Each SQL statement is independent. CTEs only exist for the single query they belong to. Since this is a separate query from the readmission-events query above, you define the CTEs again. Notice this version skips the diagnosis column since the department summary does not need it.

  • Directly below the closing parenthesis of the CTEs, add the final SELECT that aggregates by department:
SELECT
    department,
    COUNT(*) AS total_admissions,
    SUM(is_readmission) AS readmissions,
    ROUND(SUM(is_readmission) * 100.0 / COUNT(*), 1) AS readmission_rate_pct
FROM readmission_flags
GROUP BY department
ORDER BY readmission_rate_pct DESC;

What does this SELECT do?

  • SUM(is_readmission) counts how many admissions in each department were flagged as readmissions (since the flag is 1 or 0, summing gives the count).
  • The ROUND(...* 100.0 / COUNT(*), 1) expression converts the ratio to a percentage with one decimal place. Multiplying by 100.0 (not 100) forces decimal division so the result is not truncated to zero.
  • Save readmission_analysis.sql.

Before you run the file again, think about which department you expect to have the highest readmission rate. Is it the one with the most total admissions, or could a smaller department have a worse rate?

  • Run the updated file in DuckDB:
.read readmission_analysis.sql

You should see two result sets. The first is your list of individual readmission events (same as before). The second is a department-level summary showing each department's total admissions, readmission count, and readmission rate percentage, ordered from highest to lowest.

Only seeing one result set?

  • Make sure the first query ends with a semicolon before the second query starts. Each SQL statement must be terminated with ; for DuckDB to treat them as separate queries.
  • Check that you saved the file after adding the second query. Run .read readmission_analysis.sql again after saving.

Help me debug why my second query isn't running.

✔️ Awesome, I've got everything!

Great. Double check you have saved readmission_analysis.sql and can see both result sets when you run it.

ⓧ I'd like to double check the full code

Your readmission_analysis.sql should contain these chunks in order. Scroll up and confirm each one is present:

  • The file header comments (-- Detect 30-day readmissions using window functions).
  • The ordered_admissions CTE using LAG on discharge_date, partitioned by patient_id.
  • The readmissions CTE calculating days_since_last_discharge and is_readmission.
  • A SELECT filtering to WHERE is_readmission = 1, ending with a semicolon.
  • The second query's ordered_admissions CTE (without the diagnosis column).
  • The readmission_flags CTE flagging readmissions.
  • A final SELECT grouping by department with readmission_rate_pct, ordered DESC.

You now have a complete readmission detection pipeline. You can identify every patient who bounced back within 30 days and quantify which departments have the worst rates. In the Secret Mission, you will combine everything into a risk scoring model that predicts which patients are most likely to be readmitted before it happens.

Secret mission

Build a Patient Risk Scoring Model

Real hospital analytics teams don't just detect readmissions after the fact. They predict which patients are at highest risk so care teams can intervene early. Can you build a multi-factor risk scoring model that assigns HIGH, MEDIUM, or LOW risk categories to every patient?

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, so there are no ongoing costs.

Resources you used:

  • hospital-analytics project folder on your Desktop (contains all CSV data files and SQL query files).
  • DuckDB CLI (installed via Homebrew).

Keep everything running

No action needed. Choose this if you plan to keep practicing SQL queries or extending your analysis with new data.

  • Your hospital-analytics folder stays on your Desktop. You can reopen any .sql file in VS Code and re-run it with .read whenever you want.
  • DuckDB remains installed and available from any Terminal session. Launch it anytime by typing duckdb.

Pause - I'll come back to this later

Close your running sessions to free up memory. Your files stay on disk for when you return.

  • In the DuckDB prompt, type .quit to exit the session.
  • Close the Terminal window.
  • Close VS Code.

Delete - I don't want to use this again

Remove all project files and optionally uninstall DuckDB to start completely fresh.

  • Exit DuckDB by typing .quit if you have not already.
  • Delete the project folder by running this command in Terminal:
rm -rf ~/Desktop/hospital-analytics
  • Confirm the folder is gone by running:
ls ~/Desktop | grep hospital

You should see no output, confirming the folder has been removed.

  • Uninstall DuckDB by running:
brew uninstall duckdb
  • Confirm DuckDB is removed by running:
duckdb --version

You should see a "command not found" message, confirming the uninstall worked.

Nice Work!

Nice Work!

You just built a hospital readmission analytics pipeline from scratch using pure SQL. Starting with raw CSV files and zero server setup, you now have queries that answer the exact questions healthcare executives ask about patient risk and department performance.

You've learned how to:

  • Set up a local analytics environment with DuckDB that queries CSV files directly, with zero server configuration.
  • Use JOINs to combine patient demographics with admission records, revealing that Medicare patients and those aged 75+ have the highest admission rates.
  • Detect 30-day readmissions using LAG window functions and CTEs, calculating readmission rates by department the same way real hospital data analysts do.
  • Secret Mission: Built a multi-factor risk scoring model that assigns every patient a HIGH, MEDIUM, or LOW risk category based on age, admission frequency, length of stay, emergency ratio, and readmission history.

Ready to quiz yourself?