EMR Data Extraction with SQL

Introduction

30 Second Summary

Every time you visit a doctor, your symptoms, diagnoses, medications, and procedures get recorded in a database. Hospitals and clinics rely on specialists to extract meaningful insights from these records for reporting, research, and patient care improvement.

In this project, you will build a complete EMR (Electronic Medical Records) data extraction workflow. You'll create a clinical database, write complex queries to pull patient data, validate data quality, and visualize results in a live dashboard.

What You'll Build

A production-quality EMR data extraction pipeline using PostgreSQL for the database, complex SQL for extraction, and Metabase for dashboard visualization.

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

• A PostgreSQL database with 7 interconnected clinical tables and 200+ sample records modeled after real EMR systems.
• A collection of complex SQL queries using JOINs, CTEs, and window functions to extract patient demographics, encounter histories, and diagnosis trends.
• A live Metabase dashboard with 3 visualizations connected to your EMR database, plus reusable views, stored procedures, and CSV/JSON exports.
• Secret Mission: Build a Patient 360 stored procedure that generates a complete clinical summary for any patient.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, FROM). No prior healthcare data experience is required.

This project uses Docker to run Metabase. You'll install everything from scratch in Step 1.

Before We Start

Before We Start...

Set Up PostgreSQL and Docker

Before you can query clinical data, you need a database to store it in and a container runtime to power your BI tool later.

In this step, you will install PostgreSQL 17 as your relational database and Docker Desktop for running Metabase in a later step.

In this step, get ready to:

• Install PostgreSQL 17 via Homebrew and start it as a background service.
• Install Docker Desktop and verify the Docker CLI is available.
• Create the EMR database and connect to it with psql.

Install PostgreSQL 17 via Homebrew

PostgreSQL is the database engine that will store all of your EMR tables. You will install it using Homebrew, the standard package manager for macOS.

• Open Terminal by pressing Cmd+Space (macOS) to open Spotlight, then typing Terminal and pressing Enter.
• Check if Homebrew is already installed by running:

brew --version

✔️ I see a version number

Homebrew is ready. Move on to installing PostgreSQL below.

ⓧ Command not found

• Install Homebrew 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 script. It will explain what it plans to do and ask for your confirmation before proceeding. You may be prompted for your macOS password.

• Follow any on-screen instructions the installer prints. On Apple Silicon Macs, it may ask you to run an eval command to add Homebrew to your PATH. Copy and run that command if prompted.
• Confirm Homebrew is working by running:

brew --version

You should see a version number printed in the terminal.

• Install PostgreSQL 17 by running:

brew install postgresql@17

What does this command do?

This downloads and installs the PostgreSQL 17 database server, the psql command-line client, and related utilities. Homebrew installs it into its own prefix so it does not interfere with other software on your system.

• Start the PostgreSQL service so it runs in the background:

brew services start postgresql@17

• Verify PostgreSQL is running by checking its version:

psql --version

✔️ I see version 17

PostgreSQL 17 is installed and ready. You are good to continue.

ⓧ I see an older version

Your system is finding a different PostgreSQL installation on the PATH. Homebrew's versioned formula installs the binary at a specific path that may not be linked by default.

• Add the PostgreSQL 17 binaries to your PATH by running:

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

• Run psql --version again. You should now see version 17.

ⓧ Command not found

The PostgreSQL 17 binaries are not on your PATH yet. Homebrew installs versioned formulas without linking them automatically.

• Add the PostgreSQL 17 binaries to your PATH by running:

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

• Run psql --version again. You should now see a version 17 number.

Seeing a connection error or service not starting?

Make sure you ran brew services start postgresql@17 before attempting to connect. You can check the service status with brew services list and confirm postgresql@17 shows as started.

If the service shows an error, try stopping and restarting it:

brew services stop postgresql@17
brew services start postgresql@17

help me troubleshoot PostgreSQL not starting after brew services start

Install Docker Desktop for Mac

Docker Desktop provides the container runtime you will use later to run Metabase. Installing it now means it will be warmed up and ready when you need it in Step 6.

• Download Docker Desktop from the official site:
• https://www.docker.com/products/docker-desktop/
• Choose the installer for your chip (Apple Silicon or Intel).
• Double-click the downloaded Docker.dmg file to open the installer.
• Drag the Docker icon into the Applications folder.
• Open Docker Desktop by pressing Cmd+Space (macOS), typing Docker, and pressing Enter.
• Accept the Docker Subscription Service Agreement when prompted.
• Wait for Docker Desktop to finish starting. You will see the Docker whale icon in your menu bar when it is ready.

What is Docker Desktop?

Docker Desktop is an application that lets you run lightweight virtual containers on your Mac. You will use it to run Metabase (a BI dashboard tool) without needing to install Java or other dependencies directly on your machine.

• Verify Docker is available by running this command in Terminal:

docker --version

✔️ I see a version number

Docker is ready. Move on to the next substep.

ⓧ Command not found

Docker Desktop may still be starting up. Give it another minute for the whale icon to appear in the menu bar, then try the command again.

If it still does not work, make sure Docker Desktop is actually open. Look for the whale icon in the top menu bar. If the icon is not there, open Docker Desktop from your Applications folder again.

Docker Desktop stuck on starting?

The first time Docker Desktop launches, macOS runs security checks that can take several minutes. Wait for the whale icon in the menu bar to stop animating before running commands.

If it remains stuck for more than five minutes, try quitting Docker Desktop (right-click the whale icon and select Quit Docker Desktop) and reopening it.

help me troubleshoot Docker Desktop not starting on macOS

Create the EMR database and connect to it

Now that PostgreSQL is running, you will create the database that will hold all of your clinical tables throughout this project.

• Create a new database called emr_clinic by running:

createdb emr_clinic

What does this command do?

The createdb utility is a PostgreSQL helper that creates a new empty database on your local server. You are naming it emr_clinic to represent the clinical system you are building.

• Connect to the new database using the psql interactive shell:

psql emr_clinic

You should see a prompt that looks like emr_clinic=# indicating you are connected to the database.

What is psql?

The psql tool is the official PostgreSQL interactive terminal. It lets you run SQL queries, inspect tables, and manage your database directly from the command line. You will use it throughout this project to run extraction queries and validate data.

• Confirm you are connected by running this quick query inside psql:

SELECT current_database();

You should see emr_clinic returned as the result.

Seeing a database does not exist error?

Make sure you typed emr_clinic exactly (all lowercase, with an underscore). If the error persists, confirm PostgreSQL is running with brew services list and check that postgresql@17 shows as started.

help me fix a database does not exist error when running createdb or psql on macOS

Your environment is ready. Next up, you will build the EMR database schema with interconnected clinical tables.

Build the EMR Database Schema

Your tools are installed and your emr_clinic database is ready. Now it's time to design the data structure that will hold clinical information.

Real EMR systems organize patient information across interconnected tables. Each table serves a specific purpose, and foreign keys enforce relationships between them. You'll build this structure from scratch to understand how clinical data flows through a relational schema.

In this step, get ready to:

• Create the EMR database schema with 7 interconnected clinical tables.
• Understand the entity relationships between tables.
• Populate the database with realistic sample data spanning 2 years.

Create the core clinical tables

You'll store your SQL in files so you can re-run them if needed. First, create a project folder to keep everything organized.

• Open a new Terminal window by pressing Cmd+Space (macOS) or the Windows key (Windows), typing Terminal, and pressing Enter.
• Move to your Desktop and create a project folder by running these commands:

cd ~/Desktop
mkdir emr-data-extraction
cd emr-data-extraction

• Confirm the folder exists by running:

ls

You should see the emr-data-extraction folder listed (it will appear empty for now).

• Open your text editor. Press Cmd+Space (macOS) or the Windows key (Windows), type VS Code, and press Enter.
• In VS Code, click File in the top menu bar.
• Click Open Folder.
• Select the emr-data-extraction folder on your Desktop, then click Open.
• Create a new file by clicking File > New File.
• Save it immediately as schema.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows) and typing schema.sql as the filename.

Now you'll add the table definitions to schema.sql. The schema starts with the two foundational entity tables: providers and patients.

• Paste the following code into schema.sql:

-- EMR Database Schema
-- Models a simplified clinical EMR system

-- Providers table
CREATE TABLE providers (
    provider_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialty VARCHAR(100),
    npi VARCHAR(10) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Patients table
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender VARCHAR(10),
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(2),
    zip_code VARCHAR(10),
    phone VARCHAR(20),
    email VARCHAR(100),
    insurance_provider VARCHAR(100),
    insurance_id VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does this code do?

These two tables store the core entities in any clinical system. providers holds clinician records with a unique npi (National Provider Identifier). patients holds demographics and insurance details.

SERIAL auto-generates incrementing IDs. NOT NULL constraints ensure critical fields like names and date of birth are always present.

Next, add the encounters table. This is the central hub that connects patients to providers through clinical visits.

• Add the following code below the patients table in schema.sql:

-- Encounters table (visits)
CREATE encounters (
    encounter_id SERIAL PRIMARY KEY,
    patient_id INTEGER NOT NULL REFERENCES patients(patient_id),
    provider_id INTEGER NOT NULL REFERENCES providers(provider_id),
    encounter_date TIMESTAMP NOT NULL,
    encounter_type VARCHAR(20) NOT NULL CHECK (encounter_type IN ('outpatient', 'inpatient', 'emergency', 'telehealth')),
    chief_complaint TEXT,
    disposition VARCHAR(50),
    facility VARCHAR(100) DEFAULT 'Main Campus',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_encounters_patient ON encounters(patient_id);
CREATE INDEX idx_encounters_date ON encounters(encounter_date);
CREATE INDEX idx_encounters_provider ON encounters(provider_id);

What does this code do?

The encounters table represents every clinical visit. REFERENCES creates foreign key constraints linking each encounter to an existing patient and provider.

The CHECK constraint limits encounter types to four valid values. The three indexes speed up queries that filter by patient, date, or provider.

Now add the diagnosis subsystem. A reference table holds standardized ICD-10 codes, and a junction table links those codes to specific encounters.

• Add the following code below the encounter indexes in schema.sql:

-- Diagnosis codes reference table (ICD-10)
CREATE TABLE diagnosis_codes (
    code_id SERIAL PRIMARY KEY,
    icd_code VARCHAR(10) UNIQUE NOT NULL,
    description VARCHAR(255) NOT NULL,
    category VARCHAR(100)
);

-- Diagnoses table (links encounters to diagnosis codes)
CREATE TABLE diagnoses (
    diagnosis_id SERIAL PRIMARY KEY,
    encounter_id INTEGER NOT NULL REFERENCES encounters(encounter_id),
    icd_code VARCHAR(10) NOT NULL REFERENCES diagnosis_codes(icd_code),
    diagnosis_type VARCHAR(20) DEFAULT 'secondary' CHECK (diagnosis_type IN ('primary', 'secondary', 'admitting')),
    diagnosed_date DATE,
    notes TEXT
);

CREATE INDEX idx_diagnoses_encounter ON diagnoses(encounter_id);
CREATE INDEX idx_diagnoses_icd ON diagnoses(icd_code);

What does this code do?

The diagnosis_codes table stores standardized ICD-10 codes (like I10 for hypertension). The diagnoses table links each encounter to one or more diagnosis codes.

This two-table pattern (reference + junction) is standard in EMR systems. It lets multiple encounters share the same diagnosis code without duplicating the description.

Finally, add the tables for clinical actions: procedures performed and medications prescribed.

• Add the following code at the end of schema.sql:

-- Procedures table
CREATE TABLE procedures (
    procedure_id SERIAL PRIMARY KEY,
    encounter_id INTEGER NOT NULL REFERENCES encounters(encounter_id),
    cpt_code VARCHAR(10) NOT NULL,
    description VARCHAR(255) NOT NULL,
    procedure_date DATE NOT NULL,
    performing_provider_id INTEGER REFERENCES providers(provider_id),
    notes TEXT
);

CREATE INDEX idx_procedures_encounter ON procedures(encounter_id);

-- Medications table
CREATE TABLE medications (
    medication_id SERIAL PRIMARY KEY,
    encounter_id INTEGER NOT NULL REFERENCES encounters(encounter_id),
    drug_name VARCHAR(255) NOT NULL,
    dosage VARCHAR(50),
    frequency VARCHAR(50),
    route VARCHAR(50),
    start_date DATE NOT NULL,
    end_date DATE,
    prescribing_provider_id INTEGER REFERENCES providers(provider_id),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'discontinued'))
);

CREATE INDEX idx_medications_encounter ON medications(encounter_id);

What does this code do?

The procedures table records clinical actions using CPT codes (like 99214 for an office visit). The medications table tracks prescriptions with dosage, frequency, and a status field for lifecycle management.

Both tables reference encounters via foreign keys. This means every procedure and medication is tied to a specific visit, which is how real EMR audit trails work.

• Save schema.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Now execute the schema against your database. Switch back to the Terminal window where psql is connected to emr_clinic.

• Run the schema file by entering this command in psql:

\i ~/Desktop/emr-data-extraction/schema.sql

• Verify the tables were created by running:

\dt

You should see a list of 7 tables: diagnosis_codes, diagnoses, encounters, medications, patients, procedures, and providers.

Seeing an error when running the schema?

If you see a syntax error, double-check that every CREATE TABLE statement has matching parentheses and ends with a semicolon.

If you see a "relation already exists" error, the tables were already created from a previous run. Drop them with DROP SCHEMA public CASCADE; CREATE SCHEMA public; and re-run the file.

help me fix a PostgreSQL schema creation error

✔️ Awesome, I've got everything!

Great. Double check you've saved schema.sql and that \dt shows all 7 tables.

ⓧ I'd like to double check the full code

-- EMR Database Schema
-- Models a simplified clinical EMR system

-- Providers table
CREATE TABLE providers (
    provider_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialty VARCHAR(100),
    npi VARCHAR(10) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Patients table
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender VARCHAR(10),
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(2),
    zip_code VARCHAR(10),
    phone VARCHAR(20),
    email VARCHAR(100),
    insurance_provider VARCHAR(100),
    insurance_id VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Encounters table (visits)
CREATE TABLE encounters (
    encounter_id SERIAL PRIMARY KEY,
    patient_id INTEGER NOT NULL REFERENCES patients(patient_id),
    provider_id INTEGER NOT NULL REFERENCES providers(provider_id),
    encounter_date TIMESTAMP NOT NULL,
    encounter_type VARCHAR(20) NOT NULL CHECK (encounter_type IN ('outpatient', 'inpatient', 'emergency', 'telehealth')),
    chief_complaint TEXT,
    disposition VARCHAR(50),
    facility VARCHAR(100) DEFAULT 'Main Campus',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_encounters_patient ON encounters(patient_id);
CREATE INDEX idx_encounters_date ON encounters(encounter_date);
CREATE INDEX idx_encounters_provider ON encounters(provider_id);

-- Diagnosis codes reference table (ICD-10)
CREATE TABLE diagnosis_codes (
    code_id SERIAL PRIMARY KEY,
    icd_code VARCHAR(10) UNIQUE NOT NULL,
    description VARCHAR(255) NOT NULL,
    category VARCHAR(100)
);

-- Diagnoses table (links encounters to diagnosis codes)
CREATE TABLE diagnoses (
    diagnosis_id SERIAL PRIMARY KEY,
    encounter_id INTEGER NOT NULL REFERENCES encounters(encounter_id),
    icd_code VARCHAR(10) NOT NULL REFERENCES diagnosis_codes(icd_code),
    diagnosis_type VARCHAR(20) DEFAULT 'secondary' CHECK (diagnosis_type IN ('primary', 'secondary', 'admitting')),
    diagnosed_date DATE,
    notes TEXT
);

CREATE INDEX idx_diagnoses_encounter ON diagnoses(encounter_id);
CREATE INDEX idx_diagnoses_icd ON diagnoses(icd_code);

-- Procedures table
CREATE TABLE procedures (
    procedure_id SERIAL PRIMARY KEY,
    encounter_id INTEGER NOT NULL REFERENCES encounters(encounter_id),
    cpt_code VARCHAR(10) NOT NULL,
    description VARCHAR(255) NOT NULL,
    procedure_date DATE NOT NULL,
    performing_provider_id INTEGER REFERENCES providers(provider_id),
    notes TEXT
);

CREATE INDEX idx_procedures_encounter ON procedures(encounter_id);

-- Medications table
CREATE TABLE medications (
    medication_id SERIAL PRIMARY KEY,
    encounter_id INTEGER NOT NULL REFERENCES encounters(encounter_id),
    drug_name VARCHAR(255) NOT NULL,
    dosage VARCHAR(50),
    frequency VARCHAR(50),
    route VARCHAR(50),
    start_date DATE NOT NULL,
    end_date DATE,
    prescribing_provider_id INTEGER REFERENCES providers(provider_id),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'discontinued'))
);

CREATE INDEX idx_medications_encounter ON medications(encounter_id);

Review the Entity Relationship Diagram

Before populating the tables with data, take a moment to understand how they connect.

How do the tables relate?

The schema follows a hub-and-spoke pattern centered on the encounters table. Every clinical event flows through an encounter.

• A patient has many encounters (one-to-many via patient_id).
• A provider is linked to many encounters (one-to-many via provider_id).
• Each encounter can have multiple diagnoses, procedures, and medications (one-to-many from encounter to each detail table).
• The diagnosis_codes table is a reference lookup. diagnoses links encounters to standardized codes via icd_code.

Why this hub-and-spoke pattern?

This mirrors how real EMR systems like OpenEMR structure data. The patient ID links to encounters, and encounters fan out to diagnoses, procedures, and medications. This design makes it straightforward to answer questions like "what happened during visit X" or "show me all of patient Y's history."

Populate the database with sample data

An empty schema is not useful for practicing extraction queries. You need realistic data that simulates a working clinic with varied patients, multiple specialties, and a mix of encounter types across a 2-year window.

• Switch back to VS Code and create a new file called seed_data.sql in the same emr-data-extraction folder.

The seed data must be inserted in dependency order. Reference data (providers and diagnosis codes) goes first because other tables have foreign keys pointing to them.

• Paste the following reference data into seed_data.sql:

-- Seed data for EMR database

-- Insert providers
INSERT INTO providers (first_name, last_name, specialty, npi) VALUES
('Sarah', 'Chen', 'Internal Medicine', '1234567890'),
('Michael', 'Rodriguez', 'Cardiology', '2345678901'),
('Jennifer', 'Williams', 'Pulmonology', '3456789012'),
('David', 'Kim', 'Emergency Medicine', '4567890123'),
('Lisa', 'Patel', 'Endocrinology', '5678901234'),
('Robert', 'Johnson', 'Orthopedics', '6789012345'),
('Amanda', 'Thompson', 'Neurology', '7890123456'),
('James', 'Garcia', 'General Surgery', '8901234567');

-- Insert diagnosis codes (ICD-10)
INSERT INTO diagnosis_codes (icd_code, description, category) VALUES
('I10', 'Essential hypertension', 'Cardiovascular'),
('E11.9', 'Type 2 diabetes without complications', 'Endocrine'),
('J06.9', 'Acute upper respiratory infection', 'Respiratory'),
('M54.5', 'Low back pain', 'Musculoskeletal'),
('J44.1', 'COPD with acute exacerbation', 'Respiratory'),
('I25.10', 'Atherosclerotic heart disease', 'Cardiovascular'),
('E78.5', 'Hyperlipidemia', 'Endocrine'),
('F32.1', 'Major depressive disorder, moderate', 'Mental Health'),
('K21.0', 'GERD with esophagitis', 'Digestive'),
('N39.0', 'Urinary tract infection', 'Genitourinary'),
('R05.9', 'Cough, unspecified', 'Respiratory'),
('G43.909', 'Migraine, unspecified', 'Neurological'),
('J18.9', 'Pneumonia, unspecified organism', 'Respiratory'),
('I48.91', 'Atrial fibrillation, unspecified', 'Cardiovascular'),
('E03.9', 'Hypothyroidism, unspecified', 'Endocrine');

What does this data represent?

The 8 providers cover common hospital specialties (Internal Medicine, Cardiology, Pulmonology, etc.). The 15 ICD-10 codes represent frequently seen diagnoses in a general clinic setting. Both must exist before inserting encounters because of foreign key constraints.

• Add the patient data below the diagnosis codes in seed_data.sql:

-- Insert patients (50 patients)
INSERT INTO patients (first_name, last_name, date_of_birth, gender, address_line1, city, state, zip_code, phone, insurance_provider, insurance_id) VALUES
('John', 'Smith', '1955-03-15', 'Male', '123 Oak Street', 'Springfield', 'IL', '62701', '555-0101', 'Blue Cross', 'BC-001'),
('Mary', 'Johnson', '1962-07-22', 'Female', '456 Elm Avenue', 'Springfield', 'IL', '62702', '555-0102', 'Aetna', 'AE-002'),
('Robert', 'Williams', '1948-11-30', 'Male', '789 Pine Road', 'Decatur', 'IL', '62521', '555-0103', 'Medicare', 'MC-003'),
('Patricia', 'Brown', '1970-05-18', 'Female', '321 Maple Drive', 'Springfield', 'IL', '62703', '555-0104', 'United Health', 'UH-004'),
('James', 'Davis', '1980-09-25', 'Male', '654 Cedar Lane', 'Chatham', 'IL', '62629', '555-0105', 'Cigna', 'CG-005'),
('Linda', 'Miller', '1958-01-12', 'Female', '987 Birch Court', 'Springfield', 'IL', '62704', '555-0106', 'Blue Cross', 'BC-006'),
('William', 'Wilson', '1945-06-08', 'Male', '147 Walnut Way', 'Jacksonville', 'IL', '62650', '555-0107', 'Medicare', 'MC-007'),
('Barbara', 'Moore', '1972-12-03', 'Female', '258 Ash Boulevard', 'Springfield', 'IL', '62701', '555-0108', 'Aetna', 'AE-008'),
('Richard', 'Taylor', '1965-04-20', 'Male', '369 Spruce Street', 'Decatur', 'IL', '62522', '555-0109', 'United Health', 'UH-009'),
('Susan', 'Anderson', '1978-08-14', 'Female', '741 Willow Place', 'Springfield', 'IL', '62702', '555-0110', 'Cigna', 'CG-010'),
('Charles', 'Thomas', '1952-02-28', 'Male', '852 Poplar Avenue', 'Chatham', 'IL', '62629', '555-0111', 'Medicare', 'MC-011'),
('Jessica', 'Jackson', '1985-10-09', 'Female', '963 Hickory Lane', 'Springfield', 'IL', '62703', '555-0112', 'Blue Cross', 'BC-012'),
('Thomas', 'White', '1960-06-17', 'Male', '174 Magnolia Road', 'Jacksonville', 'IL', '62650', '555-0113', 'Aetna', 'AE-013'),
('Sarah', 'Harris', '1975-03-22', 'Female', '285 Cypress Court', 'Springfield', 'IL', '62704', '555-0114', 'United Health', 'UH-014'),
('Daniel', 'Martin', '1968-11-05', 'Male', '396 Dogwood Drive', 'Decatur', 'IL', '62521', '555-0115', 'Cigna', 'CG-015'),
('Karen', 'Thompson', '1982-07-30', 'Female', '407 Redwood Way', 'Springfield', 'IL', '62701', '555-0116', 'Blue Cross', 'BC-016'),
('Mark', 'Garcia', '1957-09-12', 'Male', '518 Sequoia Street', 'Chatham', 'IL', '62629', '555-0117', 'Medicare', 'MC-017'),
('Nancy', 'Martinez', '1973-04-25', 'Female', '629 Chestnut Place', 'Springfield', 'IL', '62702', '555-0118', 'Aetna', 'AE-018'),
('Steven', 'Robinson', '1950-08-19', 'Male', '730 Sycamore Avenue', 'Jacksonville', 'IL', '62650', '555-0119', 'Medicare', 'MC-019'),
('Betty', 'Clark', '1988-01-07', 'Female', '841 Beech Boulevard', 'Springfield', 'IL', '62703', '555-0120', 'United Health', 'UH-020'),
('Paul', 'Lewis', '1963-05-14', 'Male', '952 Fir Lane', 'Decatur', 'IL', '62522', '555-0121', 'Cigna', 'CG-021'),
('Dorothy', 'Lee', '1976-12-28', 'Female', '163 Hemlock Court', 'Springfield', 'IL', '62704', '555-0122', 'Blue Cross', 'BC-022'),
('Andrew', 'Walker', '1954-10-03', 'Male', '274 Juniper Drive', 'Chatham', 'IL', '62629', '555-0123', 'Medicare', 'MC-023'),
('Margaret', 'Hall', '1981-06-19', 'Female', '385 Larch Way', 'Springfield', 'IL', '62701', '555-0124', 'Aetna', 'AE-024'),
('Joshua', 'Allen', '1990-02-11', 'Male', '496 Olive Street', 'Jacksonville', 'IL', '62650', '555-0125', 'United Health', 'UH-025'),
('Ruth', 'Young', '1967-08-06', 'Female', '507 Palm Road', 'Springfield', 'IL', '62702', '555-0126', 'Cigna', 'CG-026'),
('Kenneth', 'Hernandez', '1959-03-30', 'Male', '618 Pecan Place', 'Decatur', 'IL', '62521', '555-0127', 'Medicare', 'MC-027'),
('Sharon', 'King', '1984-11-22', 'Female', '729 Alder Avenue', 'Springfield', 'IL', '62703', '555-0128', 'Blue Cross', 'BC-028'),
('Brian', 'Wright', '1971-07-15', 'Male', '830 Cottonwood Lane', 'Chatham', 'IL', '62629', '555-0129', 'Aetna', 'AE-029'),
('Michelle', 'Lopez', '1979-04-08', 'Female', '941 Hawthorn Court', 'Springfield', 'IL', '62704', '555-0130', 'United Health', 'UH-030'),
('Edward', 'Hill', '1953-09-27', 'Male', '152 Ivy Drive', 'Jacksonville', 'IL', '62650', '555-0131', 'Medicare', 'MC-031'),
('Laura', 'Scott', '1986-06-13', 'Female', '263 Jasmine Way', 'Springfield', 'IL', '62701', '555-0132', 'Cigna', 'CG-032'),
('George', 'Green', '1961-01-04', 'Male', '374 Laurel Street', 'Decatur', 'IL', '62522', '555-0133', 'Blue Cross', 'BC-033'),
('Donna', 'Adams', '1974-10-16', 'Female', '485 Myrtle Road', 'Springfield', 'IL', '62702', '555-0134', 'Aetna', 'AE-034'),
('Ronald', 'Baker', '1956-05-21', 'Male', '596 Orchid Place', 'Chatham', 'IL', '62629', '555-0135', 'Medicare', 'MC-035'),
('Carol', 'Gonzalez', '1983-08-29', 'Female', '607 Primrose Avenue', 'Springfield', 'IL', '62703', '555-0136', 'United Health', 'UH-036'),
('Anthony', 'Nelson', '1969-02-17', 'Male', '718 Rose Lane', 'Jacksonville', 'IL', '62650', '555-0137', 'Cigna', 'CG-037'),
('Helen', 'Carter', '1977-07-10', 'Female', '829 Sage Court', 'Springfield', 'IL', '62704', '555-0138', 'Blue Cross', 'BC-038'),
('Kevin', 'Mitchell', '1964-12-05', 'Male', '930 Thyme Drive', 'Decatur', 'IL', '62521', '555-0139', 'Aetna', 'AE-039'),
('Sandra', 'Perez', '1987-03-23', 'Female', '141 Violet Way', 'Springfield', 'IL', '62701', '555-0140', 'United Health', 'UH-040'),
('Gary', 'Roberts', '1951-11-08', 'Male', '252 Wisteria Street', 'Chatham', 'IL', '62629', '555-0141', 'Medicare', 'MC-041'),
('Ashley', 'Turner', '1992-06-30', 'Female', '363 Azalea Road', 'Springfield', 'IL', '62702', '555-0142', 'Cigna', 'CG-042'),
('Larry', 'Phillips', '1966-09-18', 'Male', '474 Bluebell Place', 'Jacksonville', 'IL', '62650', '555-0143', 'Blue Cross', 'BC-043'),
('Kimberly', 'Campbell', '1980-04-11', 'Female', '585 Clover Avenue', 'Springfield', 'IL', '62703', '555-0144', 'Aetna', 'AE-044'),
('Frank', 'Parker', '1958-08-24', 'Male', '696 Daisy Lane', 'Decatur', 'IL', '62522', '555-0145', 'Medicare', 'MC-045'),
('Emily', 'Evans', '1989-01-16', 'Female', '707 Foxglove Court', 'Springfield', 'IL', '62704', '555-0146', 'United Health', 'UH-046'),
('Scott', 'Edwards', '1962-05-07', 'Male', '818 Geranium Drive', 'Chatham', 'IL', '62629', '555-0147', 'Cigna', 'CG-047'),
('Angela', 'Collins', '1976-10-29', 'Female', '929 Heather Way', 'Springfield', 'IL', '62701', '555-0148', 'Blue Cross', 'BC-048'),
('Raymond', 'Stewart', '1955-07-13', 'Male', '130 Iris Street', 'Jacksonville', 'IL', '62650', '555-0149', 'Medicare', 'MC-049'),
('Melissa', 'Sanchez', '1991-12-20', 'Female', '241 Lily Road', 'Springfield', 'IL', '62702', '555-0150', 'Aetna', 'AE-050');

What does this data represent?

These 50 patients span birth years from 1945 to 1992, giving you a realistic age distribution. They carry five different insurance providers (Blue Cross, Aetna, Medicare, United Health, Cigna) across multiple cities in Illinois.

Next, add the encounter records. These represent the clinical visits that link patients to providers.

• Add the encounter data below the patients in seed_data.sql:

-- Insert encounters (generating ~150 encounters across 2 years)
INSERT INTO encounters (patient_id, provider_id, encounter_date, encounter_type, chief_complaint, disposition, facility) VALUES
(1, 1, '2023-01-15 09:00:00', 'outpatient', 'Routine follow-up for hypertension', 'Discharged', 'Main Campus'),
(1, 2, '2023-03-22 10:30:00', 'outpatient', 'Chest pain evaluation', 'Discharged', 'Main Campus'),
(1, 1, '2023-06-10 14:00:00', 'outpatient', 'Medication review', 'Discharged', 'Main Campus'),
(1, 4, '2023-09-05 22:15:00', 'emergency', 'Shortness of breath', 'Admitted', 'Main Campus'),
(1, 1, '2024-01-08 09:30:00', 'outpatient', 'Follow-up after hospitalization', 'Discharged', 'Main Campus'),
(2, 5, '2023-02-10 11:00:00', 'outpatient', 'Diabetes management', 'Discharged', 'Main Campus'),
(2, 5, '2023-05-18 10:00:00', 'outpatient', 'HbA1c review', 'Discharged', 'Main Campus'),
(2, 1, '2023-08-25 09:00:00', 'telehealth', 'Medication refill', 'Discharged', 'Telehealth'),
(2, 5, '2023-11-30 11:30:00', 'outpatient', 'Annual diabetes checkup', 'Discharged', 'Main Campus'),
(2, 5, '2024-03-15 10:00:00', 'outpatient', 'Foot exam and labs', 'Discharged', 'Main Campus'),
(3, 3, '2023-01-20 08:30:00', 'outpatient', 'COPD follow-up', 'Discharged', 'Main Campus'),
(3, 4, '2023-04-12 19:45:00', 'emergency', 'Acute dyspnea', 'Admitted', 'Main Campus'),
(3, 3, '2023-07-05 09:00:00', 'outpatient', 'Post-discharge follow-up', 'Discharged', 'Main Campus'),
(3, 3, '2023-10-18 10:30:00', 'outpatient', 'Pulmonary function test', 'Discharged', 'Main Campus'),
(3, 3, '2024-02-20 09:00:00', 'outpatient', 'COPD management review', 'Discharged', 'Main Campus'),
(4, 1, '2023-03-05 13:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(4, 7, '2023-06-22 15:30:00', 'outpatient', 'Headache evaluation', 'Discharged', 'Main Campus'),
(4, 1, '2023-12-11 09:00:00', 'telehealth', 'Follow-up on medication', 'Discharged', 'Telehealth'),
(5, 6, '2023-02-28 10:00:00', 'outpatient', 'Back pain', 'Discharged', 'Main Campus'),
(5, 6, '2023-05-15 11:00:00', 'outpatient', 'Back pain follow-up', 'Discharged', 'Main Campus'),
(5, 1, '2023-09-20 09:30:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(6, 2, '2023-01-30 14:00:00', 'outpatient', 'Palpitations', 'Discharged', 'Main Campus'),
(6, 2, '2023-04-25 10:00:00', 'outpatient', 'Cardiology follow-up', 'Discharged', 'Main Campus'),
(6, 2, '2023-08-14 09:00:00', 'outpatient', 'Echocardiogram review', 'Discharged', 'Main Campus'),
(6, 4, '2023-11-02 21:00:00', 'emergency', 'Irregular heartbeat', 'Admitted', 'Main Campus'),
(6, 2, '2024-01-22 10:30:00', 'outpatient', 'Post-discharge cardiology', 'Discharged', 'Main Campus'),
(7, 1, '2023-02-14 09:00:00', 'outpatient', 'Routine checkup', 'Discharged', 'Main Campus'),
(7, 3, '2023-06-30 11:00:00', 'outpatient', 'Cough evaluation', 'Discharged', 'Main Campus'),
(7, 1, '2023-10-05 09:00:00', 'outpatient', 'Follow-up', 'Discharged', 'Main Campus'),
(7, 4, '2024-01-15 18:30:00', 'emergency', 'Pneumonia symptoms', 'Admitted', 'Main Campus'),
(8, 1, '2023-03-12 10:00:00', 'outpatient', 'Acid reflux', 'Discharged', 'Main Campus'),
(8, 1, '2023-07-20 09:30:00', 'telehealth', 'Medication check', 'Discharged', 'Telehealth'),
(8, 1, '2024-02-05 10:00:00', 'outpatient', 'Annual visit', 'Discharged', 'Main Campus'),
(9, 2, '2023-04-03 14:30:00', 'outpatient', 'Blood pressure management', 'Discharged', 'Main Campus'),
(9, 2, '2023-07-18 10:00:00', 'outpatient', 'Cardiology checkup', 'Discharged', 'Main Campus'),
(9, 1, '2023-11-22 09:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(10, 1, '2023-05-08 09:00:00', 'outpatient', 'UTI symptoms', 'Discharged', 'Main Campus'),
(10, 1, '2023-05-22 09:30:00', 'outpatient', 'UTI follow-up', 'Discharged', 'Main Campus'),
(10, 7, '2023-09-15 11:00:00', 'outpatient', 'Migraine evaluation', 'Discharged', 'Main Campus'),
(10, 1, '2024-03-01 10:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(11, 1, '2023-01-25 09:00:00', 'outpatient', 'Diabetes screening', 'Discharged', 'Main Campus'),
(11, 5, '2023-04-17 10:30:00', 'outpatient', 'Diabetes management', 'Discharged', 'Main Campus'),
(11, 5, '2023-08-28 11:00:00', 'outpatient', 'Diabetes follow-up', 'Discharged', 'Main Campus'),
(12, 1, '2023-06-05 09:00:00', 'outpatient', 'Preventive care', 'Discharged', 'Main Campus'),
(12, 1, '2024-01-10 09:30:00', 'telehealth', 'Cold symptoms', 'Discharged', 'Telehealth'),
(13, 2, '2023-03-28 14:00:00', 'outpatient', 'Hypertension management', 'Discharged', 'Main Campus'),
(13, 2, '2023-09-12 10:00:00', 'outpatient', 'Cardiology follow-up', 'Discharged', 'Main Campus'),
(14, 1, '2023-04-10 09:00:00', 'outpatient', 'Thyroid check', 'Discharged', 'Main Campus'),
(14, 5, '2023-07-25 10:30:00', 'outpatient', 'Thyroid follow-up', 'Discharged', 'Main Campus'),
(14, 1, '2024-02-12 09:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(15, 6, '2023-05-03 11:00:00', 'outpatient', 'Knee pain', 'Discharged', 'Main Campus'),
(15, 6, '2023-08-10 10:00:00', 'outpatient', 'Orthopedic follow-up', 'Discharged', 'Main Campus'),
(16, 1, '2023-02-20 09:00:00', 'outpatient', 'Depression screening', 'Discharged', 'Main Campus'),
(16, 1, '2023-06-15 09:30:00', 'outpatient', 'Mental health follow-up', 'Discharged', 'Main Campus'),
(16, 1, '2023-10-30 10:00:00', 'telehealth', 'Medication adjustment', 'Discharged', 'Telehealth'),
(17, 2, '2023-03-15 14:00:00', 'outpatient', 'Heart failure check', 'Discharged', 'Main Campus'),
(17, 2, '2023-07-10 10:30:00', 'outpatient', 'Cardiology review', 'Discharged', 'Main Campus'),
(17, 4, '2023-12-01 20:00:00', 'emergency', 'Chest tightness', 'Admitted', 'Main Campus'),
(18, 1, '2023-04-20 09:00:00', 'outpatient', 'Annual wellness', 'Discharged', 'Main Campus'),
(18, 1, '2023-11-08 09:30:00', 'outpatient', 'Follow-up labs', 'Discharged', 'Main Campus'),
(19, 3, '2023-02-05 10:00:00', 'outpatient', 'Chronic cough', 'Discharged', 'Main Campus'),
(19, 3, '2023-05-22 09:00:00', 'outpatient', 'Pulmonary follow-up', 'Discharged', 'Main Campus'),
(19, 4, '2023-09-08 17:30:00', 'emergency', 'Breathing difficulty', 'Admitted', 'Main Campus'),
(20, 1, '2023-06-20 09:00:00', 'outpatient', 'Preventive visit', 'Discharged', 'Main Campus'),
(20, 1, '2024-01-25 10:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus');

What does this data represent?

These 65 encounters span January 2023 through March 2024. They include outpatient visits, emergency admissions, and telehealth calls. Some patients (like patient 1 and patient 6) have 5 encounters each, giving you high-utilization patients to identify in later queries.

The final batch adds clinical detail records: diagnoses, procedures, and medications linked to the encounters above.

• Add the clinical detail data at the end of seed_data.sql:

-- Insert diagnoses
INSERT INTO diagnoses (encounter_id, icd_code, diagnosis_type, diagnosed_date, notes) VALUES
(1, 'I10', 'primary', '2023-01-15', 'Blood pressure 150/95'),
(1, 'E78.5', 'secondary', '2023-01-15', 'Cholesterol elevated'),
(2, 'I10', 'primary', '2023-03-22', 'Stable hypertension'),
(2, 'I25.10', 'secondary', '2023-03-22', 'Stress test borderline'),
(3, 'I10', 'primary', '2023-06-10', 'Well controlled on medication'),
(4, 'J44.1', 'primary', '2023-09-05', 'Acute COPD exacerbation'),
(4, 'I10', 'secondary', '2023-09-05', NULL),
(5, 'I10', 'primary', '2024-01-08', 'Stable'),
(6, 'E11.9', 'primary', '2023-02-10', 'HbA1c 7.8%'),
(6, 'I10', 'secondary', '2023-02-10', NULL),
(7, 'E11.9', 'primary', '2023-05-18', 'HbA1c improved to 7.2%'),
(8, 'E11.9', 'primary', '2023-08-25', 'Medication refill'),
(9, 'E11.9', 'primary', '2023-11-30', 'HbA1c 6.9% - good control'),
(10, 'E11.9', 'primary', '2024-03-15', 'Annual review'),
(11, 'J44.1', 'primary', '2023-01-20', 'Stable COPD'),
(12, 'J44.1', 'primary', '2023-04-12', 'Severe exacerbation'),
(13, 'J44.1', 'primary', '2023-07-05', 'Improving post-discharge'),
(14, 'J44.1', 'primary', '2023-10-18', 'PFT shows moderate obstruction'),
(15, 'J44.1', 'primary', '2024-02-20', 'Stable on current therapy'),
(16, 'I10', 'primary', '2023-03-05', 'Mild hypertension detected'),
(16, 'E78.5', 'secondary', '2023-03-05', NULL),
(17, 'G43.909', 'primary', '2023-06-22', 'Chronic migraine'),
(18, 'I10', 'primary', '2023-12-11', 'Controlled'),
(18, 'G43.909', 'secondary', '2023-12-11', NULL),
(19, 'M54.5', 'primary', '2023-02-28', 'Lumbar strain'),
(20, 'M54.5', 'primary', '2023-05-15', 'Improving with PT'),
(21, 'M54.5', 'secondary', '2023-09-20', 'Resolved'),
(22, 'I48.91', 'primary', '2023-01-30', 'New onset AFib'),
(22, 'I10', 'secondary', '2023-01-30', NULL),
(23, 'I48.91', 'primary', '2023-04-25', 'Rate controlled'),
(24, 'I48.91', 'primary', '2023-08-14', 'Echo normal EF'),
(25, 'I48.91', 'primary', '2023-11-02', 'Paroxysmal AFib episode'),
(26, 'I48.91', 'primary', '2024-01-22', 'Stable on anticoagulation'),
(27, 'I10', 'primary', '2023-02-14', 'Hypertension, well controlled'),
(28, 'R05.9', 'primary', '2023-06-30', 'Persistent cough 3 weeks'),
(29, 'I10', 'primary', '2023-10-05', 'Stable'),
(30, 'J18.9', 'primary', '2024-01-15', 'Community-acquired pneumonia'),
(31, 'K21.0', 'primary', '2023-03-12', 'GERD symptoms'),
(32, 'K21.0', 'primary', '2023-07-20', 'Improving on PPI'),
(33, 'K21.0', 'secondary', '2024-02-05', 'Well controlled'),
(34, 'I10', 'primary', '2023-04-03', 'Hypertension stage 2'),
(34, 'E78.5', 'secondary', '2023-04-03', 'LDL 165'),
(35, 'I10', 'primary', '2023-07-18', 'Better controlled'),
(36, 'I10', 'primary', '2023-11-22', 'Annual review'),
(37, 'N39.0', 'primary', '2023-05-08', 'Uncomplicated UTI'),
(38, 'N39.0', 'primary', '2023-05-22', 'Resolved'),
(39, 'G43.909', 'primary', '2023-09-15', 'Migraine with aura'),
(40, 'I10', 'secondary', '2024-03-01', 'Borderline elevated'),
(41, 'E11.9', 'primary', '2023-01-25', 'New diagnosis T2DM'),
(42, 'E11.9', 'primary', '2023-04-17', 'Started metformin'),
(43, 'E11.9', 'primary', '2023-08-28', 'HbA1c 7.5%'),
(44, 'J06.9', 'primary', '2023-06-05', 'Upper respiratory infection'),
(45, 'J06.9', 'primary', '2024-01-10', 'Common cold'),
(46, 'I10', 'primary', '2023-03-28', 'Hypertension management'),
(46, 'I25.10', 'secondary', '2023-03-28', NULL),
(47, 'I10', 'primary', '2023-09-12', 'Controlled'),
(48, 'E03.9', 'primary', '2023-04-10', 'Hypothyroidism detected'),
(49, 'E03.9', 'primary', '2023-07-25', 'TSH normalizing'),
(50, 'E03.9', 'secondary', '2024-02-12', 'Stable on levothyroxine'),
(51, 'M54.5', 'primary', '2023-05-03', 'Knee osteoarthritis'),
(52, 'M54.5', 'primary', '2023-08-10', 'PT helping'),
(53, 'F32.1', 'primary', '2023-02-20', 'Moderate depression'),
(54, 'F32.1', 'primary', '2023-06-15', 'Responding to SSRI'),
(55, 'F32.1', 'primary', '2023-10-30', 'Stable, continue medication'),
(56, 'I25.10', 'primary', '2023-03-15', 'CAD with stable angina'),
(56, 'I10', 'secondary', '2023-03-15', NULL),
(57, 'I25.10', 'primary', '2023-07-10', 'Stable'),
(58, 'I25.10', 'primary', '2023-12-01', 'Unstable angina episode'),
(59, 'I10', 'primary', '2023-04-20', 'Mild hypertension'),
(60, 'I10', 'primary', '2023-11-08', 'Controlled'),
(61, 'J44.1', 'primary', '2023-02-05', 'COPD evaluation'),
(62, 'J44.1', 'primary', '2023-05-22', 'Moderate COPD'),
(63, 'J44.1', 'primary', '2023-09-08', 'COPD exacerbation'),
(64, 'I10', 'secondary', '2023-06-20', 'Borderline hypertension'),
(65, 'I10', 'primary', '2024-01-25', 'Monitoring');

-- Insert procedures
INSERT INTO procedures (encounter_id, cpt_code, description, procedure_date, performing_provider_id, notes) VALUES
(1, '99214', 'Office visit, moderate complexity', '2023-01-15', 1, NULL),
(2, '93000', 'Electrocardiogram (ECG)', '2023-03-22', 2, 'Normal sinus rhythm'),
(2, '93015', 'Cardiovascular stress test', '2023-03-22', 2, 'Borderline results'),
(4, '94060', 'Bronchospasm evaluation', '2023-09-05', 4, NULL),
(4, '71046', 'Chest X-ray, 2 views', '2023-09-05', 4, 'Hyperinflation noted'),
(6, '83036', 'Hemoglobin A1c test', '2023-02-10', 5, 'Result: 7.8%'),
(7, '83036', 'Hemoglobin A1c test', '2023-05-18', 5, 'Result: 7.2%'),
(9, '83036', 'Hemoglobin A1c test', '2023-11-30', 5, 'Result: 6.9%'),
(10, '83036', 'Hemoglobin A1c test', '2024-03-15', 5, 'Result: 6.8%'),
(11, '94010', 'Spirometry', '2023-01-20', 3, 'FEV1 55% predicted'),
(12, '71046', 'Chest X-ray, 2 views', '2023-04-12', 4, 'Bilateral infiltrates'),
(12, '94060', 'Bronchospasm evaluation', '2023-04-12', 3, NULL),
(14, '94010', 'Spirometry', '2023-10-18', 3, 'FEV1 52% predicted'),
(16, '99395', 'Preventive visit, 40-64 years', '2023-03-05', 1, NULL),
(16, '80061', 'Lipid panel', '2023-03-05', 1, NULL),
(19, '72148', 'MRI lumbar spine', '2023-02-28', 6, 'Mild disc bulge L4-L5'),
(20, '97110', 'Physical therapy evaluation', '2023-05-15', 6, NULL),
(22, '93000', 'Electrocardiogram (ECG)', '2023-01-30', 2, 'AFib detected'),
(22, '93306', 'Echocardiogram', '2023-01-30', 2, 'EF 60%'),
(24, '93306', 'Echocardiogram', '2023-08-14', 2, 'EF 58%, stable'),
(25, '93000', 'Electrocardiogram (ECG)', '2023-11-02', 4, 'Rapid AFib'),
(30, '71046', 'Chest X-ray, 2 views', '2024-01-15', 4, 'Right lower lobe consolidation'),
(34, '80061', 'Lipid panel', '2023-04-03', 2, 'LDL 165 mg/dL'),
(37, '81001', 'Urinalysis with microscopy', '2023-05-08', 1, 'WBC elevated'),
(39, '99213', 'Office visit, low complexity', '2023-09-15', 7, NULL),
(41, '83036', 'Hemoglobin A1c test', '2023-01-25', 1, 'Result: 8.1%'),
(42, '83036', 'Hemoglobin A1c test', '2023-04-17', 5, 'Result: 7.5%'),
(48, '84443', 'TSH blood test', '2023-04-10', 1, 'TSH 8.2 mIU/L'),
(49, '84443', 'TSH blood test', '2023-07-25', 5, 'TSH 4.1 mIU/L'),
(56, '93000', 'Electrocardiogram (ECG)', '2023-03-15', 2, 'ST changes'),
(56, '93306', 'Echocardiogram', '2023-03-15', 2, 'EF 45%'),
(58, '93000', 'Electrocardiogram (ECG)', '2023-12-01', 4, 'ST elevation'),
(58, '93458', 'Cardiac catheterization', '2023-12-01', 2, 'LAD 70% stenosis');

-- Insert medications
INSERT INTO medications (encounter_id, drug_name, dosage, frequency, route, start_date, end_date, prescribing_provider_id, status) VALUES
(1, 'Lisinopril', '10mg', 'Once daily', 'Oral', '2023-01-15', NULL, 1, 'active'),
(1, 'Atorvastatin', '20mg', 'Once daily at bedtime', 'Oral', '2023-01-15', NULL, 1, 'active'),
(4, 'Prednisone', '40mg', 'Once daily, taper over 10 days', 'Oral', '2023-09-05', '2023-09-15', 4, 'completed'),
(4, 'Albuterol', '90mcg', 'Every 4-6 hours as needed', 'Inhaled', '2023-09-05', NULL, 4, 'active'),
(6, 'Metformin', '500mg', 'Twice daily', 'Oral', '2023-02-10', NULL, 5, 'active'),
(7, 'Metformin', '1000mg', 'Twice daily', 'Oral', '2023-05-18', NULL, 5, 'active'),
(11, 'Tiotropium', '18mcg', 'Once daily', 'Inhaled', '2023-01-20', NULL, 3, 'active'),
(11, 'Fluticasone/Salmeterol', '250/50mcg', 'Twice daily', 'Inhaled', '2023-01-20', NULL, 3, 'active'),
(12, 'Amoxicillin', '500mg', 'Three times daily', 'Oral', '2023-04-12', '2023-04-22', 4, 'completed'),
(12, 'Prednisone', '30mg', 'Daily, taper', 'Oral', '2023-04-12', '2023-04-19', 3, 'completed'),
(17, 'Sumatriptan', '50mg', 'As needed for migraine', 'Oral', '2023-06-22', NULL, 7, 'active'),
(19, 'Ibuprofen', '400mg', 'Three times daily with food', 'Oral', '2023-02-28', '2023-04-28', 6, 'completed'),
(19, 'Cyclobenzaprine', '10mg', 'At bedtime', 'Oral', '2023-02-28', '2023-03-28', 6, 'completed'),
(22, 'Apixaban', '5mg', 'Twice daily', 'Oral', '2023-01-30', NULL, 2, 'active'),
(22, 'Metoprolol', '50mg', 'Twice daily', 'Oral', '2023-01-30', NULL, 2, 'active'),
(30, 'Azithromycin', '500mg day 1, 250mg days 2-5', 'Once daily', 'Oral', '2024-01-15', '2024-01-20', 4, 'completed'),
(31, 'Omeprazole', '20mg', 'Once daily before breakfast', 'Oral', '2023-03-12', NULL, 1, 'active'),
(34, 'Amlodipine', '5mg', 'Once daily', 'Oral', '2023-04-03', NULL, 2, 'active'),
(34, 'Rosuvastatin', '10mg', 'Once daily', 'Oral', '2023-04-03', NULL, 2, 'active'),
(37, 'Nitrofurantoin', '100mg', 'Twice daily for 5 days', 'Oral', '2023-05-08', '2023-05-13', 1, 'completed'),
(41, 'Metformin', '500mg', 'Once daily', 'Oral', '2023-01-25', NULL, 1, 'active'),
(42, 'Metformin', '1000mg', 'Twice daily', 'Oral', '2023-04-17', NULL, 5, 'active'),
(48, 'Levothyroxine', '50mcg', 'Once daily on empty stomach', 'Oral', '2023-04-10', NULL, 1, 'active'),
(53, 'Sertraline', '50mg', 'Once daily', 'Oral', '2023-02-20', NULL, 1, 'active'),
(54, 'Sertraline', '100mg', 'Once daily', 'Oral', '2023-06-15', NULL, 1, 'active'),
(56, 'Aspirin', '81mg', 'Once daily', 'Oral', '2023-03-15', NULL, 2, 'active'),
(56, 'Metoprolol', '25mg', 'Twice daily', 'Oral', '2023-03-15', NULL, 2, 'active'),
(56, 'Nitroglycerin', '0.4mg', 'As needed for chest pain', 'Sublingual', '2023-03-15', NULL, 2, 'active'),
(58, 'Clopidogrel', '75mg', 'Once daily', 'Oral', '2023-12-01', NULL, 2, 'active');

What does this data represent?

The diagnoses link ICD-10 codes to encounters, with primary and secondary classifications. Procedures use CPT codes for everything from routine office visits (99214) to cardiac catheterizations (93458). Medications include both ongoing prescriptions (active) and short courses (completed).

This variety gives you realistic patterns to query in the next steps: chronic disease management across visits, medication changes over time, and procedure frequency by specialty.

• Save seed_data.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
• Switch back to the Terminal window with psql and execute the seed file:

\i ~/Desktop/emr-data-extraction/seed_data.sql

You should see a series of INSERT 0 X messages confirming each batch was inserted.

• Verify the data loaded correctly by running these count queries:

SELECT
    (SELECT COUNT(*) FROM patients) AS patients,
    (SELECT COUNT(*) FROM providers) AS providers,
    (SELECT COUNT(*) FROM encounters) AS encounters,
    (SELECT COUNT(*) FROM diagnoses) AS diagnoses,
    (SELECT COUNT(*) FROM procedures) AS procedures,
    (SELECT COUNT(*) FROM medications) AS medications;

You should see: 50 patients, 8 providers, 65 encounters, and non-zero counts for diagnoses, procedures, and medications.

Seeing a foreign key violation error?

Foreign key errors mean you're trying to insert a record that references a row that doesn't exist yet. Make sure the providers and diagnosis_codes INSERT statements come before the encounters, and encounters come before diagnoses, procedures, and medications.

If you need to start over, run TRUNCATE providers, patients, encounters, diagnoses, procedures, medications, diagnosis_codes CASCADE; to clear all data, then re-run the seed file.

help me fix a foreign key violation in my EMR seed data

✔️ Awesome, I've got everything!

Great. Make sure your count query returned 50 patients, 8 providers, and 65 encounters.

ⓧ I'd like to double check the full code

Here is the complete seed_data.sql file. Compare yours against it:

-- Seed data for EMR database

-- Insert providers
INSERT INTO providers (first_name, last_name, specialty, npi) VALUES
('Sarah', 'Chen', 'Internal Medicine', '1234567890'),
('Michael', 'Rodriguez', 'Cardiology', '2345678901'),
('Jennifer', 'Williams', 'Pulmonology', '3456789012'),
('David', 'Kim', 'Emergency Medicine', '4567890123'),
('Lisa', 'Patel', 'Endocrinology', '5678901234'),
('Robert', 'Johnson', 'Orthopedics', '6789012345'),
('Amanda', 'Thompson', 'Neurology', '7890123456'),
('James', 'Garcia', 'General Surgery', '8901234567');

-- Insert diagnosis codes (ICD-10)
INSERT INTO diagnosis_codes (icd_code, description, category) VALUES
('I10', 'Essential hypertension', 'Cardiovascular'),
('E11.9', 'Type 2 diabetes without complications', 'Endocrine'),
('J06.9', 'Acute upper respiratory infection', 'Respiratory'),
('M54.5', 'Low back pain', 'Musculoskeletal'),
('J44.1', 'COPD with acute exacerbation', 'Respiratory'),
('I25.10', 'Atherosclerotic heart disease', 'Cardiovascular'),
('E78.5', 'Hyperlipidemia', 'Endocrine'),
('F32.1', 'Major depressive disorder, moderate', 'Mental Health'),
('K21.0', 'GERD with esophagitis', 'Digestive'),
('N39.0', 'Urinary tract infection', 'Genitourinary'),
('R05.9', 'Cough, unspecified', 'Respiratory'),
('G43.909', 'Migraine, unspecified', 'Neurological'),
('J18.9', 'Pneumonia, unspecified organism', 'Respiratory'),
('I48.91', 'Atrial fibrillation, unspecified', 'Cardiovascular'),
('E03.9', 'Hypothyroidism, unspecified', 'Endocrine');

-- Insert patients (50 patients)
INSERT INTO patients (first_name, last_name, date_of_birth, gender, address_line1, city, state, zip_code, phone, insurance_provider, insurance_id) VALUES
('John', 'Smith', '1955-03-15', 'Male', '123 Oak Street', 'Springfield', 'IL', '62701', '555-0101', 'Blue Cross', 'BC-001'),
('Mary', 'Johnson', '1962-07-22', 'Female', '456 Elm Avenue', 'Springfield', 'IL', '62702', '555-0102', 'Aetna', 'AE-002'),
('Robert', 'Williams', '1948-11-30', 'Male', '789 Pine Road', 'Decatur', 'IL', '62521', '555-0103', 'Medicare', 'MC-003'),
('Patricia', 'Brown', '1970-05-18', 'Female', '321 Maple Drive', 'Springfield', 'IL', '62703', '555-0104', 'United Health', 'UH-004'),
('James', 'Davis', '1980-09-25', 'Male', '654 Cedar Lane', 'Chatham', 'IL', '62629', '555-0105', 'Cigna', 'CG-005'),
('Linda', 'Miller', '1958-01-12', 'Female', '987 Birch Court', 'Springfield', 'IL', '62704', '555-0106', 'Blue Cross', 'BC-006'),
('William', 'Wilson', '1945-06-08', 'Male', '147 Walnut Way', 'Jacksonville', 'IL', '62650', '555-0107', 'Medicare', 'MC-007'),
('Barbara', 'Moore', '1972-12-03', 'Female', '258 Ash Boulevard', 'Springfield', 'IL', '62701', '555-0108', 'Aetna', 'AE-008'),
('Richard', 'Taylor', '1965-04-20', 'Male', '369 Spruce Street', 'Decatur', 'IL', '62522', '555-0109', 'United Health', 'UH-009'),
('Susan', 'Anderson', '1978-08-14', 'Female', '741 Willow Place', 'Springfield', 'IL', '62702', '555-0110', 'Cigna', 'CG-010'),
('Charles', 'Thomas', '1952-02-28', 'Male', '852 Poplar Avenue', 'Chatham', 'IL', '62629', '555-0111', 'Medicare', 'MC-011'),
('Jessica', 'Jackson', '1985-10-09', 'Female', '963 Hickory Lane', 'Springfield', 'IL', '62703', '555-0112', 'Blue Cross', 'BC-012'),
('Thomas', 'White', '1960-06-17', 'Male', '174 Magnolia Road', 'Jacksonville', 'IL', '62650', '555-0113', 'Aetna', 'AE-013'),
('Sarah', 'Harris', '1975-03-22', 'Female', '285 Cypress Court', 'Springfield', 'IL', '62704', '555-0114', 'United Health', 'UH-014'),
('Daniel', 'Martin', '1968-11-05', 'Male', '396 Dogwood Drive', 'Decatur', 'IL', '62521', '555-0115', 'Cigna', 'CG-015'),
('Karen', 'Thompson', '1982-07-30', 'Female', '407 Redwood Way', 'Springfield', 'IL', '62701', '555-0116', 'Blue Cross', 'BC-016'),
('Mark', 'Garcia', '1957-09-12', 'Male', '518 Sequoia Street', 'Chatham', 'IL', '62629', '555-0117', 'Medicare', 'MC-017'),
('Nancy', 'Martinez', '1973-04-25', 'Female', '629 Chestnut Place', 'Springfield', 'IL', '62702', '555-0118', 'Aetna', 'AE-018'),
('Steven', 'Robinson', '1950-08-19', 'Male', '730 Sycamore Avenue', 'Jacksonville', 'IL', '62650', '555-0119', 'Medicare', 'MC-019'),
('Betty', 'Clark', '1988-01-07', 'Female', '841 Beech Boulevard', 'Springfield', 'IL', '62703', '555-0120', 'United Health', 'UH-020'),
('Paul', 'Lewis', '1963-05-14', 'Male', '952 Fir Lane', 'Decatur', 'IL', '62522', '555-0121', 'Cigna', 'CG-021'),
('Dorothy', 'Lee', '1976-12-28', 'Female', '163 Hemlock Court', 'Springfield', 'IL', '62704', '555-0122', 'Blue Cross', 'BC-022'),
('Andrew', 'Walker', '1954-10-03', 'Male', '274 Juniper Drive', 'Chatham', 'IL', '62629', '555-0123', 'Medicare', 'MC-023'),
('Margaret', 'Hall', '1981-06-19', 'Female', '385 Larch Way', 'Springfield', 'IL', '62701', '555-0124', 'Aetna', 'AE-024'),
('Joshua', 'Allen', '1990-02-11', 'Male', '496 Olive Street', 'Jacksonville', 'IL', '62650', '555-0125', 'United Health', 'UH-025'),
('Ruth', 'Young', '1967-08-06', 'Female', '507 Palm Road', 'Springfield', 'IL', '62702', '555-0126', 'Cigna', 'CG-026'),
('Kenneth', 'Hernandez', '1959-03-30', 'Male', '618 Pecan Place', 'Decatur', 'IL', '62521', '555-0127', 'Medicare', 'MC-027'),
('Sharon', 'King', '1984-11-22', 'Female', '729 Alder Avenue', 'Springfield', 'IL', '62703', '555-0128', 'Blue Cross', 'BC-028'),
('Brian', 'Wright', '1971-07-15', 'Male', '830 Cottonwood Lane', 'Chatham', 'IL', '62629', '555-0129', 'Aetna', 'AE-029'),
('Michelle', 'Lopez', '1979-04-08', 'Female', '941 Hawthorn Court', 'Springfield', 'IL', '62704', '555-0130', 'United Health', 'UH-030'),
('Edward', 'Hill', '1953-09-27', 'Male', '152 Ivy Drive', 'Jacksonville', 'IL', '62650', '555-0131', 'Medicare', 'MC-031'),
('Laura', 'Scott', '1986-06-13', 'Female', '263 Jasmine Way', 'Springfield', 'IL', '62701', '555-0132', 'Cigna', 'CG-032'),
('George', 'Green', '1961-01-04', 'Male', '374 Laurel Street', 'Decatur', 'IL', '62522', '555-0133', 'Blue Cross', 'BC-033'),
('Donna', 'Adams', '1974-10-16', 'Female', '485 Myrtle Road', 'Springfield', 'IL', '62702', '555-0134', 'Aetna', 'AE-034'),
('Ronald', 'Baker', '1956-05-21', 'Male', '596 Orchid Place', 'Chatham', 'IL', '62629', '555-0135', 'Medicare', 'MC-035'),
('Carol', 'Gonzalez', '1983-08-29', 'Female', '607 Primrose Avenue', 'Springfield', 'IL', '62703', '555-0136', 'United Health', 'UH-036'),
('Anthony', 'Nelson', '1969-02-17', 'Male', '718 Rose Lane', 'Jacksonville', 'IL', '62650', '555-0137', 'Cigna', 'CG-037'),
('Helen', 'Carter', '1977-07-10', 'Female', '829 Sage Court', 'Springfield', 'IL', '62704', '555-0138', 'Blue Cross', 'BC-038'),
('Kevin', 'Mitchell', '1964-12-05', 'Male', '930 Thyme Drive', 'Decatur', 'IL', '62521', '555-0139', 'Aetna', 'AE-039'),
('Sandra', 'Perez', '1987-03-23', 'Female', '141 Violet Way', 'Springfield', 'IL', '62701', '555-0140', 'United Health', 'UH-040'),
('Gary', 'Roberts', '1951-11-08', 'Male', '252 Wisteria Street', 'Chatham', 'IL', '62629', '555-0141', 'Medicare', 'MC-041'),
('Ashley', 'Turner', '1992-06-30', 'Female', '363 Azalea Road', 'Springfield', 'IL', '62702', '555-0142', 'Cigna', 'CG-042'),
('Larry', 'Phillips', '1966-09-18', 'Male', '474 Bluebell Place', 'Jacksonville', 'IL', '62650', '555-0143', 'Blue Cross', 'BC-043'),
('Kimberly', 'Campbell', '1980-04-11', 'Female', '585 Clover Avenue', 'Springfield', 'IL', '62703', '555-0144', 'Aetna', 'AE-044'),
('Frank', 'Parker', '1958-08-24', 'Male', '696 Daisy Lane', 'Decatur', 'IL', '62522', '555-0145', 'Medicare', 'MC-045'),
('Emily', 'Evans', '1989-01-16', 'Female', '707 Foxglove Court', 'Springfield', 'IL', '62704', '555-0146', 'United Health', 'UH-046'),
('Scott', 'Edwards', '1962-05-07', 'Male', '818 Geranium Drive', 'Chatham', 'IL', '62629', '555-0147', 'Cigna', 'CG-047'),
('Angela', 'Collins', '1976-10-29', 'Female', '929 Heather Way', 'Springfield', 'IL', '62701', '555-0148', 'Blue Cross', 'BC-048'),
('Raymond', 'Stewart', '1955-07-13', 'Male', '130 Iris Street', 'Jacksonville', 'IL', '62650', '555-0149', 'Medicare', 'MC-049'),
('Melissa', 'Sanchez', '1991-12-20', 'Female', '241 Lily Road', 'Springfield', 'IL', '62702', '555-0150', 'Aetna', 'AE-050');

-- Insert encounters (generating ~150 encounters across 2 years)
INSERT INTO encounters (patient_id, provider_id, encounter_date, encounter_type, chief_complaint, disposition, facility) VALUES
(1, 1, '2023-01-15 09:00:00', 'outpatient', 'Routine follow-up for hypertension', 'Discharged', 'Main Campus'),
(1, 2, '2023-03-22 10:30:00', 'outpatient', 'Chest pain evaluation', 'Discharged', 'Main Campus'),
(1, 1, '2023-06-10 14:00:00', 'outpatient', 'Medication review', 'Discharged', 'Main Campus'),
(1, 4, '2023-09-05 22:15:00', 'emergency', 'Shortness of breath', 'Admitted', 'Main Campus'),
(1, 1, '2024-01-08 09:30:00', 'outpatient', 'Follow-up after hospitalization', 'Discharged', 'Main Campus'),
(2, 5, '2023-02-10 11:00:00', 'outpatient', 'Diabetes management', 'Discharged', 'Main Campus'),
(2, 5, '2023-05-18 10:00:00', 'outpatient', 'HbA1c review', 'Discharged', 'Main Campus'),
(2, 1, '2023-08-25 09:00:00', 'telehealth', 'Medication refill', 'Discharged', 'Telehealth'),
(2, 5, '2023-11-30 11:30:00', 'outpatient', 'Annual diabetes checkup', 'Discharged', 'Main Campus'),
(2, 5, '2024-03-15 10:00:00', 'outpatient', 'Foot exam and labs', 'Discharged', 'Main Campus'),
(3, 3, '2023-01-20 08:30:00', 'outpatient', 'COPD follow-up', 'Discharged', 'Main Campus'),
(3, 4, '2023-04-12 19:45:00', 'emergency', 'Acute dyspnea', 'Admitted', 'Main Campus'),
(3, 3, '2023-07-05 09:00:00', 'outpatient', 'Post-discharge follow-up', 'Discharged', 'Main Campus'),
(3, 3, '2023-10-18 10:30:00', 'outpatient', 'Pulmonary function test', 'Discharged', 'Main Campus'),
(3, 3, '2024-02-20 09:00:00', 'outpatient', 'COPD management review', 'Discharged', 'Main Campus'),
(4, 1, '2023-03-05 13:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(4, 7, '2023-06-22 15:30:00', 'outpatient', 'Headache evaluation', 'Discharged', 'Main Campus'),
(4, 1, '2023-12-11 09:00:00', 'telehealth', 'Follow-up on medication', 'Discharged', 'Telehealth'),
(5, 6, '2023-02-28 10:00:00', 'outpatient', 'Back pain', 'Discharged', 'Main Campus'),
(5, 6, '2023-05-15 11:00:00', 'outpatient', 'Back pain follow-up', 'Discharged', 'Main Campus'),
(5, 1, '2023-09-20 09:30:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(6, 2, '2023-01-30 14:00:00', 'outpatient', 'Palpitations', 'Discharged', 'Main Campus'),
(6, 2, '2023-04-25 10:00:00', 'outpatient', 'Cardiology follow-up', 'Discharged', 'Main Campus'),
(6, 2, '2023-08-14 09:00:00', 'outpatient', 'Echocardiogram review', 'Discharged', 'Main Campus'),
(6, 4, '2023-11-02 21:00:00', 'emergency', 'Irregular heartbeat', 'Admitted', 'Main Campus'),
(6, 2, '2024-01-22 10:30:00', 'outpatient', 'Post-discharge cardiology', 'Discharged', 'Main Campus'),
(7, 1, '2023-02-14 09:00:00', 'outpatient', 'Routine checkup', 'Discharged', 'Main Campus'),
(7, 3, '2023-06-30 11:00:00', 'outpatient', 'Cough evaluation', 'Discharged', 'Main Campus'),
(7, 1, '2023-10-05 09:00:00', 'outpatient', 'Follow-up', 'Discharged', 'Main Campus'),
(7, 4, '2024-01-15 18:30:00', 'emergency', 'Pneumonia symptoms', 'Admitted', 'Main Campus'),
(8, 1, '2023-03-12 10:00:00', 'outpatient', 'Acid reflux', 'Discharged', 'Main Campus'),
(8, 1, '2023-07-20 09:30:00', 'telehealth', 'Medication check', 'Discharged', 'Telehealth'),
(8, 1, '2024-02-05 10:00:00', 'outpatient', 'Annual visit', 'Discharged', 'Main Campus'),
(9, 2, '2023-04-03 14:30:00', 'outpatient', 'Blood pressure management', 'Discharged', 'Main Campus'),
(9, 2, '2023-07-18 10:00:00', 'outpatient', 'Cardiology checkup', 'Discharged', 'Main Campus'),
(9, 1, '2023-11-22 09:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(10, 1, '2023-05-08 09:00:00', 'outpatient', 'UTI symptoms', 'Discharged', 'Main Campus'),
(10, 1, '2023-05-22 09:30:00', 'outpatient', 'UTI follow-up', 'Discharged', 'Main Campus'),
(10, 7, '2023-09-15 11:00:00', 'outpatient', 'Migraine evaluation', 'Discharged', 'Main Campus'),
(10, 1, '2024-03-01 10:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(11, 1, '2023-01-25 09:00:00', 'outpatient', 'Diabetes screening', 'Discharged', 'Main Campus'),
(11, 5, '2023-04-17 10:30:00', 'outpatient', 'Diabetes management', 'Discharged', 'Main Campus'),
(11, 5, '2023-08-28 11:00:00', 'outpatient', 'Diabetes follow-up', 'Discharged', 'Main Campus'),
(12, 1, '2023-06-05 09:00:00', 'outpatient', 'Preventive care', 'Discharged', 'Main Campus'),
(12, 1, '2024-01-10 09:30:00', 'telehealth', 'Cold symptoms', 'Discharged', 'Telehealth'),
(13, 2, '2023-03-28 14:00:00', 'outpatient', 'Hypertension management', 'Discharged', 'Main Campus'),
(13, 2, '2023-09-12 10:00:00', 'outpatient', 'Cardiology follow-up', 'Discharged', 'Main Campus'),
(14, 1, '2023-04-10 09:00:00', 'outpatient', 'Thyroid check', 'Discharged', 'Main Campus'),
(14, 5, '2023-07-25 10:30:00', 'outpatient', 'Thyroid follow-up', 'Discharged', 'Main Campus'),
(14, 1, '2024-02-12 09:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus'),
(15, 6, '2023-05-03 11:00:00', 'outpatient', 'Knee pain', 'Discharged', 'Main Campus'),
(15, 6, '2023-08-10 10:00:00', 'outpatient', 'Orthopedic follow-up', 'Discharged', 'Main Campus'),
(16, 1, '2023-02-20 09:00:00', 'outpatient', 'Depression screening', 'Discharged', 'Main Campus'),
(16, 1, '2023-06-15 09:30:00', 'outpatient', 'Mental health follow-up', 'Discharged', 'Main Campus'),
(16, 1, '2023-10-30 10:00:00', 'telehealth', 'Medication adjustment', 'Discharged', 'Telehealth'),
(17, 2, '2023-03-15 14:00:00', 'outpatient', 'Heart failure check', 'Discharged', 'Main Campus'),
(17, 2, '2023-07-10 10:30:00', 'outpatient', 'Cardiology review', 'Discharged', 'Main Campus'),
(17, 4, '2023-12-01 20:00:00', 'emergency', 'Chest tightness', 'Admitted', 'Main Campus'),
(18, 1, '2023-04-20 09:00:00', 'outpatient', 'Annual wellness', 'Discharged', 'Main Campus'),
(18, 1, '2023-11-08 09:30:00', 'outpatient', 'Follow-up labs', 'Discharged', 'Main Campus'),
(19, 3, '2023-02-05 10:00:00', 'outpatient', 'Chronic cough', 'Discharged', 'Main Campus'),
(19, 3, '2023-05-22 09:00:00', 'outpatient', 'Pulmonary follow-up', 'Discharged', 'Main Campus'),
(19, 4, '2023-09-08 17:30:00', 'emergency', 'Breathing difficulty', 'Admitted', 'Main Campus'),
(20, 1, '2023-06-20 09:00:00', 'outpatient', 'Preventive visit', 'Discharged', 'Main Campus'),
(20, 1, '2024-01-25 10:00:00', 'outpatient', 'Annual physical', 'Discharged', 'Main Campus');

-- Insert diagnoses
INSERT INTO diagnoses (encounter_id, icd_code, diagnosis_type, diagnosed_date, notes) VALUES
(1, 'I10', 'primary', '2023-01-15', 'Blood pressure 150/95'),
(1, 'E78.5', 'secondary', '2023-01-15', 'Cholesterol elevated'),
(2, 'I10', 'primary', '2023-03-22', 'Stable hypertension'),
(2, 'I25.10', 'secondary', '2023-03-22', 'Stress test borderline'),
(3, 'I10', 'primary', '2023-06-10', 'Well controlled on medication'),
(4, 'J44.1', 'primary', '2023-09-05', 'Acute COPD exacerbation'),
(4, 'I10', 'secondary', '2023-09-05', NULL),
(5, 'I10', 'primary', '2024-01-08', 'Stable'),
(6, 'E11.9', 'primary', '2023-02-10', 'HbA1c 7.8%'),
(6, 'I10', 'secondary', '2023-02-10', NULL),
(7, 'E11.9', 'primary', '2023-05-18', 'HbA1c improved to 7.2%'),
(8, 'E11.9', 'primary', '2023-08-25', 'Medication refill'),
(9, 'E11.9', 'primary', '2023-11-30', 'HbA1c 6.9% - good control'),
(10, 'E11.9', 'primary', '2024-03-15', 'Annual review'),
(11, 'J44.1', 'primary', '2023-01-20', 'Stable COPD'),
(12, 'J44.1', 'primary', '2023-04-12', 'Severe exacerbation'),
(13, 'J44.1', 'primary', '2023-07-05', 'Improving post-discharge'),
(14, 'J44.1', 'primary', '2023-10-18', 'PFT shows moderate obstruction'),
(15, 'J44.1', 'primary', '2024-02-20', 'Stable on current therapy'),
(16, 'I10', 'primary', '2023-03-05', 'Mild hypertension detected'),
(16, 'E78.5', 'secondary', '2023-03-05', NULL),
(17, 'G43.909', 'primary', '2023-06-22', 'Chronic migraine'),
(18, 'I10', 'primary', '2023-12-11', 'Controlled'),
(18, 'G43.909', 'secondary', '2023-12-11', NULL),
(19, 'M54.5', 'primary', '2023-02-28', 'Lumbar strain'),
(20, 'M54.5', 'primary', '2023-05-15', 'Improving with PT'),
(21, 'M54.5', 'secondary', '2023-09-20', 'Resolved'),
(22, 'I48.91', 'primary', '2023-01-30', 'New onset AFib'),
(22, 'I10', 'secondary', '2023-01-30', NULL),
(23, 'I48.91', 'primary', '2023-04-25', 'Rate controlled'),
(24, 'I48.91', 'primary', '2023-08-14', 'Echo normal EF'),
(25, 'I48.91', 'primary', '2023-11-02', 'Paroxysmal AFib episode'),
(26, 'I48.91', 'primary', '2024-01-22', 'Stable on anticoagulation'),
(27, 'I10', 'primary', '2023-02-14', 'Hypertension, well controlled'),
(28, 'R05.9', 'primary', '2023-06-30', 'Persistent cough 3 weeks'),
(29, 'I10', 'primary', '2023-10-05', 'Stable'),
(30, 'J18.9', 'primary', '2024-01-15', 'Community-acquired pneumonia'),
(31, 'K21.0', 'primary', '2023-03-12', 'GERD symptoms'),
(32, 'K21.0', 'primary', '2023-07-20', 'Improving on PPI'),
(33, 'K21.0', 'secondary', '2024-02-05', 'Well controlled'),
(34, 'I10', 'primary', '2023-04-03', 'Hypertension stage 2'),
(34, 'E78.5', 'secondary', '2023-04-03', 'LDL 165'),
(35, 'I10', 'primary', '2023-07-18', 'Better controlled'),
(36, 'I10', 'primary', '2023-11-22', 'Annual review'),
(37, 'N39.0', 'primary', '2023-05-08', 'Uncomplicated UTI'),
(38, 'N39.0', 'primary', '2023-05-22', 'Resolved'),
(39, 'G43.909', 'primary', '2023-09-15', 'Migraine with aura'),
(40, 'I10', 'secondary', '2024-03-01', 'Borderline elevated'),
(41, 'E11.9', 'primary', '2023-01-25', 'New diagnosis T2DM'),
(42, 'E11.9', 'primary', '2023-04-17', 'Started metformin'),
(43, 'E11.9', 'primary', '2023-08-28', 'HbA1c 7.5%'),
(44, 'J06.9', 'primary', '2023-06-05', 'Upper respiratory infection'),
(45, 'J06.9', 'primary', '2024-01-10', 'Common cold'),
(46, 'I10', 'primary', '2023-03-28', 'Hypertension management'),
(46, 'I25.10', 'secondary', '2023-03-28', NULL),
(47, 'I10', 'primary', '2023-09-12', 'Controlled'),
(48, 'E03.9', 'primary', '2023-04-10', 'Hypothyroidism detected'),
(49, 'E03.9', 'primary', '2023-07-25', 'TSH normalizing'),
(50, 'E03.9', 'secondary', '2024-02-12', 'Stable on levothyroxine'),
(51, 'M54.5', 'primary', '2023-05-03', 'Knee osteoarthritis'),
(52, 'M54.5', 'primary', '2023-08-10', 'PT helping'),
(53, 'F32.1', 'primary', '2023-02-20', 'Moderate depression'),
(54, 'F32.1', 'primary', '2023-06-15', 'Responding to SSRI'),
(55, 'F32.1', 'primary', '2023-10-30', 'Stable, continue medication'),
(56, 'I25.10', 'primary', '2023-03-15', 'CAD with stable angina'),
(56, 'I10', 'secondary', '2023-03-15', NULL),
(57, 'I25.10', 'primary', '2023-07-10', 'Stable'),
(58, 'I25.10', 'primary', '2023-12-01', 'Unstable angina episode'),
(59, 'I10', 'primary', '2023-04-20', 'Mild hypertension'),
(60, 'I10', 'primary', '2023-11-08', 'Controlled'),
(61, 'J44.1', 'primary', '2023-02-05', 'COPD evaluation'),
(62, 'J44.1', 'primary', '2023-05-22', 'Moderate COPD'),
(63, 'J44.1', 'primary', '2023-09-08', 'COPD exacerbation'),
(64, 'I10', 'secondary', '2023-06-20', 'Borderline hypertension'),
(65, 'I10', 'primary', '2024-01-25', 'Monitoring');

-- Insert procedures
INSERT INTO procedures (encounter_id, cpt_code, description, procedure_date, performing_provider_id, notes) VALUES
(1, '99214', 'Office visit, moderate complexity', '2023-01-15', 1, NULL),
(2, '93000', 'Electrocardiogram (ECG)', '2023-03-22', 2, 'Normal sinus rhythm'),
(2, '93015', 'Cardiovascular stress test', '2023-03-22', 2, 'Borderline results'),
(4, '94060', 'Bronchospasm evaluation', '2023-09-05', 4, NULL),
(4, '71046', 'Chest X-ray, 2 views', '2023-09-05', 4, 'Hyperinflation noted'),
(6, '83036', 'Hemoglobin A1c test', '2023-02-10', 5, 'Result: 7.8%'),
(7, '83036', 'Hemoglobin A1c test', '2023-05-18', 5, 'Result: 7.2%'),
(9, '83036', 'Hemoglobin A1c test', '2023-11-30', 5, 'Result: 6.9%'),
(10, '83036', 'Hemoglobin A1c test', '2024-03-15', 5, 'Result: 6.8%'),
(11, '94010', 'Spirometry', '2023-01-20', 3, 'FEV1 55% predicted'),
(12, '71046', 'Chest X-ray, 2 views', '2023-04-12', 4, 'Bilateral infiltrates'),
(12, '94060', 'Bronchospasm evaluation', '2023-04-12', 3, NULL),
(14, '94010', 'Spirometry', '2023-10-18', 3, 'FEV1 52% predicted'),
(16, '99395', 'Preventive visit, 40-64 years', '2023-03-05', 1, NULL),
(16, '80061', 'Lipid panel', '2023-03-05', 1, NULL),
(19, '72148', 'MRI lumbar spine', '2023-02-28', 6, 'Mild disc bulge L4-L5'),
(20, '97110', 'Physical therapy evaluation', '2023-05-15', 6, NULL),
(22, '93000', 'Electrocardiogram (ECG)', '2023-01-30', 2, 'AFib detected'),
(22, '93306', 'Echocardiogram', '2023-01-30', 2, 'EF 60%'),
(24, '93306', 'Echocardiogram', '2023-08-14', 2, 'EF 58%, stable'),
(25, '93000', 'Electrocardiogram (ECG)', '2023-11-02', 4, 'Rapid AFib'),
(30, '71046', 'Chest X-ray, 2 views', '2024-01-15', 4, 'Right lower lobe consolidation'),
(34, '80061', 'Lipid panel', '2023-04-03', 2, 'LDL 165 mg/dL'),
(37, '81001', 'Urinalysis with microscopy', '2023-05-08', 1, 'WBC elevated'),
(39, '99213', 'Office visit, low complexity', '2023-09-15', 7, NULL),
(41, '83036', 'Hemoglobin A1c test', '2023-01-25', 1, 'Result: 8.1%'),
(42, '83036', 'Hemoglobin A1c test', '2023-04-17', 5, 'Result: 7.5%'),
(48, '84443', 'TSH blood test', '2023-04-10', 1, 'TSH 8.2 mIU/L'),
(49, '84443', 'TSH blood test', '2023-07-25', 5, 'TSH 4.1 mIU/L'),
(56, '93000', 'Electrocardiogram (ECG)', '2023-03-15', 2, 'ST changes'),
(56, '93306', 'Echocardiogram', '2023-03-15', 2, 'EF 45%'),
(58, '93000', 'Electrocardiogram (ECG)', '2023-12-01', 4, 'ST elevation'),
(58, '93458', 'Cardiac catheterization', '2023-12-01', 2, 'LAD 70% stenosis');

-- Insert medications
INSERT INTO medications (encounter_id, drug_name, dosage, frequency, route, start_date, end_date, prescribing_provider_id, status) VALUES
(1, 'Lisinopril', '10mg', 'Once daily', 'Oral', '2023-01-15', NULL, 1, 'active'),
(1, 'Atorvastatin', '20mg', 'Once daily at bedtime', 'Oral', '2023-01-15', NULL, 1, 'active'),
(4, 'Prednisone', '40mg', 'Once daily, taper over 10 days', 'Oral', '2023-09-05', '2023-09-15', 4, 'completed'),
(4, 'Albuterol', '90mcg', 'Every 4-6 hours as needed', 'Inhaled', '2023-09-05', NULL, 4, 'active'),
(6, 'Metformin', '500mg', 'Twice daily', 'Oral', '2023-02-10', NULL, 5, 'active'),
(7, 'Metformin', '1000mg', 'Twice daily', 'Oral', '2023-05-18', NULL, 5, 'active'),
(11, 'Tiotropium', '18mcg', 'Once daily', 'Inhaled', '2023-01-20', NULL, 3, 'active'),
(11, 'Fluticasone/Salmeterol', '250/50mcg', 'Twice daily', 'Inhaled', '2023-01-20', NULL, 3, 'active'),
(12, 'Amoxicillin', '500mg', 'Three times daily', 'Oral', '2023-04-12', '2023-04-22', 4, 'completed'),
(12, 'Prednisone', '30mg', 'Daily, taper', 'Oral', '2023-04-12', '2023-04-19', 3, 'completed'),
(17, 'Sumatriptan', '50mg', 'As needed for migraine', 'Oral', '2023-06-22', NULL, 7, 'active'),
(19, 'Ibuprofen', '400mg', 'Three times daily with food', 'Oral', '2023-02-28', '2023-04-28', 6, 'completed'),
(19, 'Cyclobenzaprine', '10mg', 'At bedtime', 'Oral', '2023-02-28', '2023-03-28', 6, 'completed'),
(22, 'Apixaban', '5mg', 'Twice daily', 'Oral', '2023-01-30', NULL, 2, 'active'),
(22, 'Metoprolol', '50mg', 'Twice daily', 'Oral', '2023-01-30', NULL, 2, 'active'),
(30, 'Azithromycin', '500mg day 1, 250mg days 2-5', 'Once daily', 'Oral', '2024-01-15', '2024-01-20', 4, 'completed'),
(31, 'Omeprazole', '20mg', 'Once daily before breakfast', 'Oral', '2023-03-12', NULL, 1, 'active'),
(34, 'Amlodipine', '5mg', 'Once daily', 'Oral', '2023-04-03', NULL, 2, 'active'),
(34, 'Rosuvastatin', '10mg', 'Once daily', 'Oral', '2023-04-03', NULL, 2, 'active'),
(37, 'Nitrofurantoin', '100mg', 'Twice daily for 5 days', 'Oral', '2023-05-08', '2023-05-13', 1, 'completed'),
(41, 'Metformin', '500mg', 'Once daily', 'Oral', '2023-01-25', NULL, 1, 'active'),
(42, 'Metformin', '1000mg', 'Twice daily', 'Oral', '2023-04-17', NULL, 5, 'active'),
(48, 'Levothyroxine', '50mcg', 'Once daily on empty stomach', 'Oral', '2023-04-10', NULL, 1, 'active'),
(53, 'Sertraline', '50mg', 'Once daily', 'Oral', '2023-02-20', NULL, 1, 'active'),
(54, 'Sertraline', '100mg', 'Once daily', 'Oral', '2023-06-15', NULL, 1, 'active'),
(56, 'Aspirin', '81mg', 'Once daily', 'Oral', '2023-03-15', NULL, 2, 'active'),
(56, 'Metoprolol', '25mg', 'Twice daily', 'Oral', '2023-03-15', NULL, 2, 'active'),
(56, 'Nitroglycerin', '0.4mg', 'As needed for chest pain', 'Sublingual', '2023-03-15', NULL, 2, 'active'),
(58, 'Clopidogrel', '75mg', 'Once daily', 'Oral', '2023-12-01', NULL, 2, 'active');

Your EMR database is fully populated with realistic clinical data. Next up, you'll write complex SQL queries to extract meaningful insights from this data.

Write Complex Extraction Queries

Your EMR database is populated with realistic clinical data across 7 interconnected tables. Now comes the core skill of an EMR Data Extraction Specialist: writing complex queries that pull meaningful insights from those tables.

Real EMR extraction work involves combining data from multiple tables using JOINs, organizing query logic with CTEs, and analyzing patterns with window functions. In this step, you'll write 6 production-quality queries that demonstrate all three techniques.

In this step, get ready to:

• Write JOINs and CTEs to extract patient encounter histories.
• Use window functions to rank patients and analyze visit timing.
• Build a comprehensive multi-table clinical report.

Write patient encounter history and visit frequency queries

A CTE (Common Table Expression) lets you define a temporary named result set within a query. Combined with JOINs across multiple tables, CTEs make complex extraction logic readable and maintainable.

• Create a new file called queries.sql in your project folder (the same folder where schema.sql and seed_data.sql live).
• Add the file header and first query (patient encounter history with diagnoses) by pasting this code:

-- ============================================================
-- EMR DATA EXTRACTION QUERIES
-- ============================================================

-- ============================================================
-- QUERY 1: Patient Encounter History with Diagnoses
-- Uses JOINs and CTE to build a complete visit history
-- ============================================================
WITH encounter_details AS (
    SELECT
        e.encounter_id,
        e.encounter_date,
        e.encounter_type,
        e.chief_complaint,
        p.patient_id,
        p.first_name || ' ' || p.last_name AS patient_name,
        pr.first_name || ' ' || pr.last_name AS provider_name,
        pr.specialty
    FROM encounters e
    JOIN patients p ON e.patient_id = p.patient_id
    JOIN providers pr ON e.provider_id = pr.provider_id
)
SELECT
    ed.patient_name,
    ed.encounter_date,
    ed.encounter_type,
    ed.chief_complaint,
    ed.provider_name,
    ed.specialty,
    dc.icd_code,
    dc.description AS diagnosis_description,
    d.diagnosis_type
FROM encounter_details ed
LEFT JOIN diagnoses d ON ed.encounter_id = d.encounter_id
LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
ORDER BY ed.patient_name, ed.encounter_date DESC;

What does this query do?

The CTE (encounter_details) first joins three tables: encounters, patients, and providers. This builds a readable base of who visited, when, and which doctor they saw.

The main SELECT then LEFT JOINs diagnoses and diagnosis codes onto that base. LEFT JOIN ensures encounters without diagnoses still appear in results (they just show NULL for diagnosis columns).

• Save queries.sql.
• Test this query by pasting it into your psql session. You should see rows listing patient names, encounter dates, provider names, and associated diagnoses ordered alphabetically by patient.

Seeing zero rows or an error?

• Make sure you're connected to the emr_clinic database. Run \conninfo in psql to confirm.
• If you see relation does not exist, your schema or seed data was not loaded. Re-run \i schema.sql then \i seed_data.sql.
• Check for missing semicolons at the end of the query. PostgreSQL waits for a semicolon before executing.

Help me debug my patient encounter history query.

Window functions let you perform calculations across rows related to the current row without collapsing them into groups. ROW_NUMBER assigns a unique sequential number, while RANK allows ties (two patients with the same visit count get the same rank).

• Add Query 2 to queries.sql below the first query:

-- ============================================================
-- QUERY 2: Patient Visit Frequency Ranking (Window Functions)
-- Uses ROW_NUMBER and RANK to identify high-utilization patients
-- ============================================================
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    COUNT(e.encounter_id) AS total_visits,
    COUNT(CASE WHEN e.encounter_type = 'emergency' THEN 1 END) AS er_visits,
    MIN(e.encounter_date) AS first_visit,
    MAX(e.encounter_date) AS last_visit,
    RANK() OVER (ORDER BY COUNT(e.encounter_id) DESC) AS visit_rank,
    ROW_NUMBER() OVER (ORDER BY COUNT(e.encounter_id) DESC) AS row_num
FROM patients p
JOIN encounters e ON p.patient_id = e.patient_id
GROUP BY p.patient_id, p.first_name, p.last_name
ORDER BY total_visits DESC;

What does this query do?

• COUNT(e.encounter_id) tallies total visits per patient.
• The conditional COUNT(CASE WHEN ...) pattern counts only emergency visits without a separate subquery.
• RANK() OVER and ROW_NUMBER() OVER both rank patients by visit count, but RANK gives tied patients the same number while ROW_NUMBER always assigns a unique integer.

• Save queries.sql.
• Paste Query 2 into your psql session. You should see patients ranked by total visits, with the highest-utilization patients (5 visits) at the top and their ER visit counts alongside.

Getting a GROUP BY error?

• Window functions with aggregate functions require all non-aggregated columns in the GROUP BY clause. Make sure p.patient_id, p.first_name, p.last_name are all listed in your GROUP BY.
• If you see column must appear in GROUP BY, check that you haven't added extra columns to the SELECT without including them in GROUP BY.

Help me fix my window function query.

Write monthly volume and visit timing queries

Healthcare analysts often need to track trends over time. These next two queries demonstrate monthly volume aggregation with a running total, and how to calculate gaps between consecutive patient visits.

• Add Query 3 (monthly encounter volumes with a running total) to queries.sql:

-- ============================================================
-- QUERY 3: Monthly Encounter Volumes with Running Total (CTE)
-- ============================================================
WITH monthly_counts AS (
    SELECT
        DATE_TRUNC('month', encounter_date) AS month,
        COUNT(*) AS encounter_count,
        COUNT(CASE WHEN encounter_type = 'emergency' THEN 1 END) AS emergency_count,
        COUNT(CASE WHEN encounter_type = 'outpatient' THEN 1 END) AS outpatient_count,
        COUNT(CASE WHEN encounter_type = 'telehealth' THEN 1 END) AS telehealth_count
    FROM encounters
    GROUP BY DATE_TRUNC('month', encounter_date)
)
SELECT
    TO_CHAR(month, 'YYYY-MM') AS month,
    encounter_count,
    emergency_count,
    outpatient_count,
    telehealth_count,
    SUM(encounter_count) OVER (ORDER BY month) AS running_total
FROM monthly_counts
ORDER BY month;

What does this query do?

• DATE_TRUNC('month', encounter_date) truncates timestamps to the first of each month, grouping all encounters within the same month together.
• The CTE calculates per-month counts broken down by encounter type.
• SUM(encounter_count) OVER (ORDER BY month) computes a running total. Each row shows the cumulative number of encounters from the beginning up to that month.

• Save queries.sql.
• Paste Query 3 into psql. You should see one row per month (formatted as YYYY-MM) with encounter breakdowns and a running total that increases each row.

Running total not increasing correctly?

• Make sure the OVER (ORDER BY month) clause references the truncated month column from the CTE, not encounter_date directly.
• If you see duplicate months, check that the GROUP BY in the CTE uses DATE_TRUNC('month', encounter_date) rather than the raw date.

Help me debug my running total query.

The LAG window function accesses a row's predecessor within the same partition. This is perfect for calculating the time gap between consecutive visits for the same patient.

• Add Query 4 (time between consecutive visits) to queries.sql:

-- ============================================================
-- QUERY 4: Time Between Consecutive Visits (LAG Window Function)
-- ============================================================
WITH patient_visits AS (
    SELECT
        p.patient_id,
        p.first_name || ' ' || p.last_name AS patient_name,
        e.encounter_date,
        e.encounter_type,
        LAG(e.encounter_date) OVER (
            PARTITION BY p.patient_id
            ORDER BY e.encounter_date
        ) AS previous_visit_date
    FROM patients p
    JOIN encounters e ON p.patient_id = e.patient_id
)
SELECT
    patient_id,
    patient_name,
    encounter_date,
    encounter_type,
    previous_visit_date,
    encounter_date - previous_visit_date AS days_since_last_visit
FROM patient_visits
WHERE previous_visit_date IS NOT NULL
ORDER BY patient_name, encounter_date;

What does this query do?

• PARTITION BY p.patient_id ensures LAG only looks at the same patient's previous visit, not across all patients.
• ORDER BY e.encounter_date within the partition defines what "previous" means (chronological order).
• The subtraction encounter_date - previous_visit_date gives the interval in days between consecutive visits. The WHERE clause filters out each patient's first visit (which has no predecessor).

• Save queries.sql.
• Paste Query 4 into psql. You should see rows showing each visit alongside the previous visit date and the number of days between them. Patients with only one visit won't appear (filtered by the WHERE clause).

Seeing all patients including those with one visit?

• Check that your WHERE clause says WHERE previous_visit_date IS NOT NULL. Without this filter, the first visit for each patient shows NULL values.
• If days_since_last_visit shows unexpected large numbers, verify the PARTITION BY uses p.patient_id. Without partitioning, LAG looks across all patients.

Help me fix my LAG window function query.

Build a full clinical report and diagnosis frequency analysis

The most common real-world extraction request is a comprehensive clinical report that combines everything: demographics, encounters, diagnoses, procedures, and medications in a single result set. This requires joining all 6 core tables.

• Add Query 5 (full clinical report with multi-table JOIN) to queries.sql:

-- ============================================================
-- QUERY 5: Full Clinical Report (Multi-table JOIN)
-- ============================================================
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    p.date_of_birth,
    p.gender,
    p.insurance_provider,
    e.encounter_id,
    e.encounter_date,
    e.encounter_type,
    e.chief_complaint,
    pr.first_name || ' ' || pr.last_name AS provider_name,
    pr.specialty,
    dc.icd_code,
    dc.description AS diagnosis,
    d.diagnosis_type,
    proc.cpt_code,
    proc.description AS procedure_performed,
    m.drug_name,
    m.dosage,
    m.frequency,
    m.status AS medication_status
FROM patients p
JOIN encounters e ON p.patient_id = e.patient_id
JOIN providers pr ON e.provider_id = pr.provider_id
LEFT JOIN diagnoses d ON e.encounter_id = d.encounter_id
LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
LEFT JOIN procedures proc ON e.encounter_id = proc.encounter_id
LEFT JOIN medications m ON e.encounter_id = m.encounter_id
ORDER BY p.patient_id, e.encounter_date DESC, d.diagnosis_type;

What does this query do?

• The INNER JOINs (patients to encounters, encounters to providers) ensure every row has a real patient visit with a valid provider.
• The LEFT JOINs for diagnoses, procedures, and medications mean encounters without any of these still appear. Not every visit results in a procedure or new medication.
• The ORDER BY sorts by patient, then most recent encounter first, then diagnosis type (primary before secondary). This mirrors the chronological narrative a clinician expects.

• Save queries.sql.
• Paste Query 5 into psql. You should see a wide result set with 20 columns spanning patient demographics through medications. The output will have many rows because each encounter can generate multiple rows when it has multiple diagnoses, procedures, or medications.

Expand your psql display

If the output wraps awkwardly, type \x in psql to toggle expanded display mode. Each row will print vertically, making wide results easier to read. Type \x again to switch back.

Seeing duplicate rows?

• Duplicates are expected here. When an encounter has 2 diagnoses and 1 procedure, the JOIN produces 2 rows (one per diagnosis, each repeating the procedure). This is normal for multi-table JOINs with one-to-many relationships.
• If you see dramatically more rows than expected (thousands), check that your JOIN conditions reference the correct columns. A missing or incorrect ON clause creates a cross join.

Help me understand why my multi-table JOIN has duplicate rows.

The final query gives you a frequency analysis of diagnoses across the entire patient population. This is the kind of report an administrator would request to understand the clinic's case mix.

• Add Query 6 (top diagnoses by frequency) to queries.sql:

-- ============================================================
-- QUERY 6: Top Diagnoses by Frequency
-- ============================================================
SELECT
    dc.icd_code,
    dc.description,
    dc.category,
    COUNT(*) AS diagnosis_count,
    COUNT(DISTINCT d.encounter_id) AS unique_encounters,
    COUNT(DISTINCT e.patient_id) AS unique_patients,
    ROUND(
        COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM diagnoses) * 100, 1
    ) AS percentage_of_all_diagnoses
FROM diagnoses d
JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
JOIN encounters e ON d.encounter_id = e.encounter_id
GROUP BY dc.icd_code, dc.description, dc.category
ORDER BY diagnosis_count DESC;

What does this query do?

• COUNT(*) gives the raw frequency of each diagnosis code across all encounters.
• COUNT(DISTINCT d.encounter_id) and COUNT(DISTINCT e.patient_id) tell you how many unique visits and unique patients have each diagnosis. A high count with few unique patients means recurring chronic conditions.
• The ::NUMERIC cast forces decimal division (PostgreSQL integer division truncates to zero). The subquery (SELECT COUNT(*) FROM diagnoses) calculates the total for percentage computation.

• Save queries.sql.
• Paste Query 6 into psql. You should see diagnoses ranked by frequency, with Essential hypertension (I10) appearing near the top since it is the most common diagnosis in the sample data.

Percentage showing 0.0 for all rows?

• Make sure you have the ::NUMERIC cast on COUNT(*). Without it, PostgreSQL performs integer division which rounds everything to zero.
• If you see an error about the subquery, ensure the parentheses around (SELECT COUNT(*) FROM diagnoses) are present. The subquery must be wrapped in parentheses to be used inline.

Help me fix my diagnosis frequency percentage calculation.

✔️ Awesome, I've got everything!

Great. Double check you've saved queries.sql with all 6 queries.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file with all 6 queries:

-- ============================================================
-- EMR DATA EXTRACTION QUERIES
-- ============================================================

-- ============================================================
-- QUERY 1: Patient Encounter History with Diagnoses
-- Uses JOINs and CTE to build a complete visit history
-- ============================================================
WITH encounter_details AS (
    SELECT
        e.encounter_id,
        e.encounter_date,
        e.encounter_type,
        e.chief_complaint,
        p.patient_id,
        p.first_name || ' ' || p.last_name AS patient_name,
        pr.first_name || ' ' || pr.last_name AS provider_name,
        pr.specialty
    FROM encounters e
    JOIN patients p ON e.patient_id = p.patient_id
    JOIN providers pr ON e.provider_id = pr.provider_id
)
SELECT
    ed.patient_name,
    ed.encounter_date,
    ed.encounter_type,
    ed.chief_complaint,
    ed.provider_name,
    ed.specialty,
    dc.icd_code,
    dc.description AS diagnosis_description,
    d.diagnosis_type
FROM encounter_details ed
LEFT JOIN diagnoses d ON ed.encounter_id = d.encounter_id
LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
ORDER BY ed.patient_name, ed.encounter_date DESC;

-- ============================================================
-- QUERY 2: Patient Visit Frequency Ranking (Window Functions)
-- Uses ROW_NUMBER and RANK to identify high-utilization patients
-- ============================================================
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    COUNT(e.encounter_id) AS total_visits,
    COUNT(CASE WHEN e.encounter_type = 'emergency' THEN 1 END) AS er_visits,
    MIN(e.encounter_date) AS first_visit,
    MAX(e.encounter_date) AS last_visit,
    RANK() OVER (ORDER BY COUNT(e.encounter_id) DESC) AS visit_rank,
    ROW_NUMBER() OVER (ORDER BY COUNT(e.encounter_id) DESC) AS row_num
FROM patients p
JOIN encounters e ON p.patient_id = e.patient_id
GROUP BY p.patient_id, p.first_name, p.last_name
ORDER BY total_visits DESC;

-- ============================================================
-- QUERY 3: Monthly Encounter Volumes with Running Total (CTE)
-- ============================================================
WITH monthly_counts AS (
    SELECT
        DATE_TRUNC('month', encounter_date) AS month,
        COUNT(*) AS encounter_count,
        COUNT(CASE WHEN encounter_type = 'emergency' THEN 1 END) AS emergency_count,
        COUNT(CASE WHEN encounter_type = 'outpatient' THEN 1 END) AS outpatient_count,
        COUNT(CASE WHEN encounter_type = 'telehealth' THEN 1 END) AS telehealth_count
    FROM encounters
    GROUP BY DATE_TRUNC('month', encounter_date)
)
SELECT
    TO_CHAR(month, 'YYYY-MM') AS month,
    encounter_count,
    emergency_count,
    outpatient_count,
    telehealth_count,
    SUM(encounter_count) OVER (ORDER BY month) AS running_total
FROM monthly_counts
ORDER BY month;

-- ============================================================
-- QUERY 4: Time Between Consecutive Visits (LAG Window Function)
-- ============================================================
WITH patient_visits AS (
    SELECT
        p.patient_id,
        p.first_name || ' ' || p.last_name AS patient_name,
        e.encounter_date,
        e.encounter_type,
        LAG(e.encounter_date) OVER (
            PARTITION BY p.patient_id
            ORDER BY e.encounter_date
        ) AS previous_visit_date
    FROM patients p
    JOIN encounters e ON p.patient_id = e.patient_id
)
SELECT
    patient_id,
    patient_name,
    encounter_date,
    encounter_type,
    previous_visit_date,
    encounter_date - previous_visit_date AS days_since_last_visit
FROM patient_visits
WHERE previous_visit_date IS NOT NULL
ORDER BY patient_name, encounter_date;

-- ============================================================
-- QUERY 5: Full Clinical Report (Multi-table JOIN)
-- ============================================================
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    p.date_of_birth,
    p.gender,
    p.insurance_provider,
    e.encounter_id,
    e.encounter_date,
    e.encounter_type,
    e.chief_complaint,
    pr.first_name || ' ' || pr.last_name AS provider_name,
    pr.specialty,
    dc.icd_code,
    dc.description AS diagnosis,
    d.diagnosis_type,
    proc.cpt_code,
    proc.description AS procedure_performed,
    m.drug_name,
    m.dosage,
    m.frequency,
    m.status AS medication_status
FROM patients p
JOIN encounters e ON p.patient_id = e.patient_id
JOIN providers pr ON e.provider_id = pr.provider_id
LEFT JOIN diagnoses d ON e.encounter_id = d.encounter_id
LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
LEFT JOIN procedures proc ON e.encounter_id = proc.encounter_id
LEFT JOIN medications m ON e.encounter_id = m.encounter_id
ORDER BY p.patient_id, e.encounter_date DESC, d.diagnosis_type;

-- ============================================================
-- QUERY 6: Top Diagnoses by Frequency
-- ============================================================
SELECT
    dc.icd_code,
    dc.description,
    dc.category,
    COUNT(*) AS diagnosis_count,
    COUNT(DISTINCT d.encounter_id) AS unique_encounters,
    COUNT(DISTINCT e.patient_id) AS unique_patients,
    ROUND(
        COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM diagnoses) * 100, 1
    ) AS percentage_of_all_diagnoses
FROM diagnoses d
JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
JOIN encounters e ON d.encounter_id = e.encounter_id
GROUP BY dc.icd_code, dc.description, dc.category
ORDER BY diagnosis_count DESC;

You now have 6 production-quality extraction queries covering patient histories, visit rankings, volume trends, timing analysis, full clinical reports, and diagnosis frequencies. Next up, you'll validate data quality to make sure the information these queries return is trustworthy.

Validate Data Quality

Your extraction queries pull data from the EMR database. But how do you know the data itself is trustworthy?

In production EMR systems, data quality issues creep in constantly. Duplicate patient records, missing diagnosis codes, encounters that reference providers who no longer exist. In this step, you'll write validation queries that detect these problems before they corrupt downstream reports.

In this step, get ready to:

• Write targeted checks for duplicates, missing values, and orphaned records.
• Build summary reports that count data quality issues by category.

Write checks for duplicates, missing values, and orphaned records

Data quality validation follows a pattern: use a LEFT JOIN to find records that should have a match in another table, then filter for rows where the match is NULL. These orphaned or invalid records indicate broken relationships in your data.

• Create a new file called validation.sql in the same folder as your other SQL files.
• Add the following four targeted validation checks:

-- ============================================================
-- DATA QUALITY VALIDATION QUERIES
-- ============================================================

-- CHECK 1: Duplicate patients (same name and DOB)
SELECT
    first_name,
    last_name,
    date_of_birth,
    COUNT(*) AS duplicate_count
FROM patients
GROUP BY first_name, last_name, date_of_birth
HAVING COUNT(*) > 1;

-- CHECK 2: Encounters without a valid provider
SELECT
    e.encounter_id,
    e.patient_id,
    e.encounter_date,
    e.provider_id
FROM encounters e
LEFT JOIN providers pr ON e.provider_id = pr.provider_id
WHERE pr.provider_id IS NULL;

-- CHECK 3: Diagnoses referencing non-existent encounters (orphans)
SELECT
    d.diagnosis_id,
    d.encounter_id,
    d.icd_code
FROM diagnoses d
LEFT JOIN encounters e ON d.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL;

-- CHECK 4: Diagnoses with invalid ICD codes
SELECT
    d.diagnosis_id,
    d.encounter_id,
    d.icd_code
FROM diagnoses d
LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
WHERE dc.icd_code IS NULL;

What do these checks do?

• CHECK 1 uses GROUP BY with HAVING COUNT(*) > 1 to find patients who share the same first name, last name, and date of birth. These are likely duplicates from double-entry.
• CHECK 2 uses a LEFT JOIN to the providers table and filters for NULL matches. Any result means an encounter references a provider that doesn't exist.
• CHECK 3 finds orphaned diagnoses. These are diagnosis records that point to an encounter_id with no matching row in the encounters table.
• CHECK 4 catches diagnoses with ICD codes that aren't in the reference table. This would indicate a coding error or a missing entry in diagnosis_codes.

• Save validation.sql.

You won't see output yet. The next chunk adds the summary reports, then you'll run the full file.

Write referential integrity checks and a summary report

Individual checks are useful for debugging specific records. Summary reports give you an at-a-glance view of overall data health by counting issues across every category in one query.

• Add the following two summary checks to the bottom of validation.sql:

-- CHECK 5: Missing values in required fields
SELECT
    'Patients without DOB' AS check_name,
    COUNT(*) AS issue_count
FROM patients WHERE date_of_birth IS NULL
UNION ALL
SELECT
    'Encounters without date',
    COUNT(*)
FROM encounters WHERE encounter_date IS NULL
UNION ALL
SELECT
    'Encounters without provider',
    COUNT(*)
FROM encounters WHERE provider_id IS NULL
UNION ALL
SELECT
    'Diagnoses without ICD code',
    COUNT(*)
FROM diagnoses WHERE icd_code IS NULL OR icd_code = ''
UNION ALL
SELECT
    'Medications without drug name',
    COUNT(*)
FROM medications WHERE drug_name IS NULL OR drug_name = '';

-- CHECK 6: Referential integrity summary report
SELECT
    'Orphaned diagnoses (no matching encounter)' AS check_name,
    COUNT(*) AS issue_count
FROM diagnoses d
LEFT JOIN encounters e ON d.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL
UNION ALL
SELECT
    'Orphaned procedures (no matching encounter)',
    COUNT(*)
FROM procedures p
LEFT JOIN encounters e ON p.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL
UNION ALL
SELECT
    'Orphaned medications (no matching encounter)',
    COUNT(*)
FROM medications m
LEFT JOIN encounters e ON m.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL
UNION ALL
SELECT
    'Encounters for non-existent patients',
    COUNT(*)
FROM encounters e
LEFT JOIN patients p ON e.patient_id = p.patient_id
WHERE p.patient_id IS NULL;

What do these summary reports do?

Both checks use UNION ALL to stack multiple independent counts into a single result set. Each row represents one category of data quality issue with its count.

CHECK 5 scans every table for NULL or empty values in fields that should always be populated (dates, codes, names). CHECK 6 checks all foreign key relationships from child tables back to parent tables. If your schema's foreign key constraints are enforced, these should always return 0. The value of running them anyway is catching issues in systems where constraints are soft or disabled for bulk loads.

• Save validation.sql.
• Switch back to your psql session and run the validation file by entering this command:

\i validation.sql

You should see output for all 6 checks. Checks 1 through 4 return 0 rows (no results), confirming no duplicates, orphans, or invalid codes. Checks 5 and 6 each return a table where every issue_count value is 0.

Why do all checks return zero issues?

Your seed data was designed to be clean. In a production EMR, you would almost certainly see non-zero counts here. The value of these queries is that they run on any database. When you point them at a real EMR extract, they immediately surface problems that would otherwise silently corrupt your reports.

Seeing an error when running the file?

• If you see a "No such file or directory" message, make sure your psql session was started from the same folder where validation.sql is saved. You can also use the full file path instead.
• If you see a syntax error, check that each UNION ALL keyword sits between two complete SELECT statements. A missing semicolon between checks can also cause cascading errors.

Help me debug my validation.sql file

✔️ Awesome, I've got everything!

Great. Double check you've saved validation.sql and all 6 checks returned clean results.

ⓧ I'd like to double check the full code

Here's the complete validation.sql file for comparison:

-- ============================================================
-- DATA QUALITY VALIDATION QUERIES
-- ============================================================

-- CHECK 1: Duplicate patients (same name and DOB)
SELECT
    first_name,
    last_name,
    date_of_birth,
    COUNT(*) AS duplicate_count
FROM patients
GROUP BY first_name, last_name, date_of_birth
HAVING COUNT(*) > 1;

-- CHECK 2: Encounters without a valid provider
SELECT
    e.encounter_id,
    e.patient_id,
    e.encounter_date,
    e.provider_id
FROM encounters e
LEFT JOIN providers pr ON e.provider_id = pr.provider_id
WHERE pr.provider_id IS NULL;

-- CHECK 3: Diagnoses referencing non-existent encounters (orphans)
SELECT
    d.diagnosis_id,
    d.encounter_id,
    d.icd_code
FROM diagnoses d
LEFT JOIN encounters e ON d.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL;

-- CHECK 4: Diagnoses with invalid ICD codes
SELECT
    d.diagnosis_id,
    d.encounter_id,
    d.icd_code
FROM diagnoses d
LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
WHERE dc.icd_code IS NULL;

-- CHECK 5: Missing values in required fields
SELECT
    'Patients without DOB' AS check_name,
    COUNT(*) AS issue_count
FROM patients WHERE date_of_birth IS NULL
UNION ALL
SELECT
    'Encounters without date',
    COUNT(*)
FROM encounters WHERE encounter_date IS NULL
UNION ALL
SELECT
    'Encounters without provider',
    COUNT(*)
FROM encounters WHERE provider_id IS NULL
UNION ALL
SELECT
    'Diagnoses without ICD code',
    COUNT(*)
FROM diagnoses WHERE icd_code IS NULL OR icd_code = ''
UNION ALL
SELECT
    'Medications without drug name',
    COUNT(*)
FROM medications WHERE drug_name IS NULL OR drug_name = '';

-- CHECK 6: Referential integrity summary report
SELECT
    'Orphaned diagnoses (no matching encounter)' AS check_name,
    COUNT(*) AS issue_count
FROM diagnoses d
LEFT JOIN encounters e ON d.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL
UNION ALL
SELECT
    'Orphaned procedures (no matching encounter)',
    COUNT(*)
FROM procedures p
LEFT JOIN encounters e ON p.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL
UNION ALL
SELECT
    'Orphaned medications (no matching encounter)',
    COUNT(*)
FROM medications m
LEFT JOIN encounters e ON m.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL
UNION ALL
SELECT
    'Encounters for non-existent patients',
    COUNT(*)
FROM encounters e
LEFT JOIN patients p ON e.patient_id = p.patient_id
WHERE p.patient_id IS NULL;

Your data is validated and clean. Next, you'll turn your best queries into reusable database objects (views and stored procedures) and export data in CSV and JSON formats.

Create Views, Stored Procedures, and Export Data

Your data quality checks confirmed the EMR database is clean and well-structured. Now it's time to turn your ad-hoc queries into reusable database objects that anyone on a team can run without rewriting SQL from scratch.

In real healthcare data environments, analysts don't re-run complex queries every time they need a report. They create views (saved queries that act like virtual tables) and stored procedures (functions that accept parameters and return filtered results). In this step, you'll build both, then export the results in CSV and JSON formats for downstream systems.

In this step, get ready to:

• Create two reusable views for patient encounter summaries and active diagnoses.
• Build a stored function that extracts encounters by date range.
• Export data to CSV and JSON formats.

Create two reusable views

A view is a saved SELECT query that behaves like a virtual table. Once created, anyone can query it with a simple SELECT * FROM view_name instead of rewriting the full JOIN logic every time.

• Create a new file called views_and_procedures.sql in your text editor.
• Add the first view, which aggregates encounter statistics per patient, by pasting this code:

-- ============================================================
-- REUSABLE VIEWS
-- ============================================================

-- VIEW 1: Patient Encounter Summary
CREATE OR REPLACE VIEW patient_encounter_summary AS
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    p.date_of_birth,
    p.gender,
    p.insurance_provider,
    COUNT(e.encounter_id) AS total_encounters,
    COUNT(CASE WHEN e.encounter_type = 'emergency' THEN 1 END) AS emergency_visits,
    MAX(e.encounter_date) AS last_visit_date,
    MIN(e.encounter_date) AS first_visit_date,
    COUNT(DISTINCT proc.procedure_id) AS total_procedures
FROM patients p
LEFT JOIN encounters e ON p.patient_id = e.patient_id
LEFT JOIN procedures proc ON e.encounter_id = proc.encounter_id
GROUP BY p.patient_id, p.first_name, p.last_name, p.date_of_birth, p.gender, p.insurance_provider;

What does this view do?

This view joins patients to their encounters and procedures, then aggregates everything into one row per patient. Each row shows the patient's total encounters, emergency visits, last and first visit dates, and total procedures.

The LEFT JOIN ensures patients with zero encounters still appear in results. The COUNT(CASE WHEN ...) pattern counts only emergency-type encounters without filtering out other rows.

• Add the second view below the first. This one shows all diagnoses with their associated patient and provider details:

-- VIEW 2: Active Diagnoses with Patient and Provider Info
CREATE OR REPLACE VIEW active_diagnoses AS
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    dc.icd_code,
    dc.description AS diagnosis_description,
    dc.category,
    d.diagnosis_type,
    d.diagnosed_date,
    e.encounter_date,
    pr.first_name || ' ' || pr.last_name AS diagnosing_provider,
    pr.specialty
FROM diagnoses d
JOIN encounters e ON d.encounter_id = e.encounter_id
JOIN patients p ON e.patient_id = p.patient_id
JOIN providers pr ON e.provider_id = pr.provider_id
JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code;

What does this view do?

This view flattens the relationship between diagnoses, encounters, patients, providers, and diagnosis codes into a single queryable surface. Instead of writing a 5-table JOIN every time you need diagnosis details, you query active_diagnoses directly.

It uses JOIN (inner) instead of LEFT JOIN because every diagnosis must have a valid encounter, patient, provider, and ICD code to be meaningful.

• Save views_and_procedures.sql.

Create a stored function for date range extracts

In PostgreSQL, reusable data-returning routines use CREATE FUNCTION with RETURNS TABLE. This is PostgreSQL's equivalent of stored procedures in SQL Server or Oracle.

• Add the stored function below your views in views_and_procedures.sql. This function accepts a date range and returns all encounters, diagnoses, and procedures within that period:

-- ============================================================
-- STORED PROCEDURE: Date Range Extract
-- ============================================================
CREATE OR REPLACE FUNCTION extract_encounters_by_date_range(
    start_date DATE,
    end_date DATE
)
RETURNS TABLE (
    encounter_id INTEGER,
    patient_name TEXT,
    encounter_date TIMESTAMP,
    encounter_type VARCHAR(20),
    provider_name TEXT,
    icd_code VARCHAR(10),
    diagnosis_description VARCHAR(255),
    procedure_description VARCHAR(255),
    cpt_code VARCHAR(10)
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.encounter_id,
        (p.first_name || ' ' || p.last_name)::TEXT AS patient_name,
        e.encounter_date,
        e.encounter_type,
        (pr.first_name || ' ' || pr.last_name)::TEXT AS provider_name,
        dc.icd_code,
        dc.description AS diagnosis_description,
        proc.description AS procedure_description,
        proc.cpt_code
    FROM encounters e
    JOIN patients p ON e.patient_id = p.patient_id
    JOIN providers pr ON e.provider_id = pr.provider_id
    LEFT JOIN diagnoses d ON e.encounter_id = d.encounter_id
    LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
    LEFT JOIN procedures proc ON e.encounter_id = proc.encounter_id
    WHERE e.encounter_date >= start_date
      AND e.encounter_date < end_date + INTERVAL '1 day'
    ORDER BY e.encounter_date;
END;
$$;

What does this function do?

The function accepts two DATE parameters (start and end) and returns a table of encounters with their associated diagnoses and procedures. The RETURNS TABLE clause defines the output columns, making the function queryable like a table.

The end_date + INTERVAL '1 day' pattern ensures encounters on the end date itself are included. The ::TEXT casts are required because PostgreSQL's string concatenation returns VARCHAR, which must match the TEXT return type declared in the function signature.

• Save views_and_procedures.sql.
• Switch back to your psql session and execute the file by running:

\i views_and_procedures.sql

What should I see?

You should see CREATE VIEW printed twice (once for each view) followed by CREATE FUNCTION. This confirms all three objects were created successfully.

• Test the patient_encounter_summary view by running:

SELECT * FROM patient_encounter_summary ORDER BY total_encounters DESC LIMIT 10;

You should see 10 rows showing patients ranked by their encounter count, with columns for demographics, visit counts, and procedure totals.

• Test the stored function by extracting all encounters from January 2023:

SELECT * FROM extract_encounters_by_date_range('2023-01-01', '2023-01-31');

You should see encounter rows from January 2023, each showing the patient name, provider, diagnosis codes, and any procedures performed during that visit.

Seeing an error about the function?

If you see ERROR: function extract_encounters_by_date_range does not exist, the \i command may not have found your file. Make sure you ran psql from the same directory where views_and_procedures.sql is saved.

If you see a syntax error, double-check that the $$ delimiters and the closing ; after the last $$ are present.

Help me debug my stored function error.

Export data to CSV and JSON

Real-world data extraction rarely ends at a SQL result set. Downstream systems need data in portable formats like CSV (for spreadsheets and flat-file imports) or JSON (for APIs and web applications).

• Export the patient encounter summary to a CSV file by running this command in psql:

\copy (SELECT * FROM patient_encounter_summary) TO '/tmp/patient_summary.csv' WITH CSV HEADER;

What does this command do?

The \copy meta-command runs on the client side (your machine), not on the database server. It executes the query in parentheses and writes the results to the specified file path. The WITH CSV HEADER option adds column names as the first row.

• Verify the CSV file was created by running:

\! head -5 /tmp/patient_summary.csv

You should see the CSV header row followed by 4 rows of patient data with comma-separated values.

• Now export data to JSON format. Add this query to the bottom of views_and_procedures.sql and then run it in psql:

-- ============================================================
-- DATA EXPORT: CSV using COPY
-- ============================================================
-- Run from psql:
-- \copy (SELECT * FROM patient_encounter_summary) TO '/tmp/patient_summary.csv' WITH CSV HEADER;

-- ============================================================
-- DATA EXPORT: JSON using json_agg
-- ============================================================
SELECT json_agg(json_build_object(
    'patient_id', patient_id,
    'patient_name', patient_name,
    'total_encounters', total_encounters,
    'last_visit_date', last_visit_date,
    'emergency_visits', emergency_visits
))
FROM patient_encounter_summary
WHERE total_encounters > 3;

What does json_agg do?

The json_build_object() function constructs a JSON object from key-value pairs you specify. The json_agg() function then wraps all those objects into a single JSON array.

The WHERE total_encounters > 3 filter limits the export to high-utilization patients only. This is a common pattern when generating JSON payloads for APIs or dashboards that only need a subset of data.

• Run the JSON export query directly in psql to see the output:

SELECT json_agg(json_build_object(
    'patient_id', patient_id,
    'patient_name', patient_name,
    'total_encounters', total_encounters,
    'last_visit_date', last_visit_date,
    'emergency_visits', emergency_visits
))
FROM patient_encounter_summary
WHERE total_encounters > 3;

You should see a JSON array containing objects for each patient with more than 3 encounters.

• Save views_and_procedures.sql with the export section included.

CSV file not found or empty?

If you see a permission error, make sure the /tmp/ directory exists and is writable. On macOS, /tmp/ is always available.

If the file is empty, confirm the patient_encounter_summary view was created by running SELECT COUNT(*) FROM patient_encounter_summary; first. It should return 50.

Help me troubleshoot my CSV export.

✔️ Awesome, I've got everything!

Great. Double check you've saved views_and_procedures.sql with all the views, the function, and the export sections.

ⓧ I'd like to double check the full code

Here is the complete views_and_procedures.sql file:

-- ============================================================
-- REUSABLE VIEWS
-- ============================================================

-- VIEW 1: Patient Encounter Summary
CREATE OR REPLACE VIEW patient_encounter_summary AS
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    p.date_of_birth,
    p.gender,
    p.insurance_provider,
    COUNT(e.encounter_id) AS total_encounters,
    COUNT(CASE WHEN e.encounter_type = 'emergency' THEN 1 END) AS emergency_visits,
    MAX(e.encounter_date) AS last_visit_date,
    MIN(e.encounter_date) AS first_visit_date,
    COUNT(DISTINCT proc.procedure_id) AS total_procedures
FROM patients p
LEFT JOIN encounters e ON p.patient_id = e.patient_id
LEFT JOIN procedures proc ON e.encounter_id = proc.encounter_id
GROUP BY p.patient_id, p.first_name, p.last_name, p.date_of_birth, p.gender, p.insurance_provider;

-- VIEW 2: Active Diagnoses with Patient and Provider Info
CREATE OR REPLACE VIEW active_diagnoses AS
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name AS patient_name,
    dc.icd_code,
    dc.description AS diagnosis_description,
    dc.category,
    d.diagnosis_type,
    d.diagnosed_date,
    e.encounter_date,
    pr.first_name || ' ' || pr.last_name AS diagnosing_provider,
    pr.specialty
FROM diagnoses d
JOIN encounters e ON d.encounter_id = e.encounter_id
JOIN patients p ON e.patient_id = p.patient_id
JOIN providers pr ON e.provider_id = pr.provider_id
JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code;

-- ============================================================
-- STORED PROCEDURE: Date Range Extract
-- ============================================================
CREATE OR REPLACE FUNCTION extract_encounters_by_date_range(
    start_date DATE,
    end_date DATE
)
RETURNS TABLE (
    encounter_id INTEGER,
    patient_name TEXT,
    encounter_date TIMESTAMP,
    encounter_type VARCHAR(20),
    provider_name TEXT,
    icd_code VARCHAR(10),
    diagnosis_description VARCHAR(255),
    procedure_description VARCHAR(255),
    cpt_code VARCHAR(10)
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.encounter_id,
        (p.first_name || ' ' || p.last_name)::TEXT AS patient_name,
        e.encounter_date,
        e.encounter_type,
        (pr.first_name || ' ' || pr.last_name)::TEXT AS provider_name,
        dc.icd_code,
        dc.description AS diagnosis_description,
        proc.description AS procedure_description,
        proc.cpt_code
    FROM encounters e
    JOIN patients p ON e.patient_id = p.patient_id
    JOIN providers pr ON e.provider_id = pr.provider_id
    LEFT JOIN diagnoses d ON e.encounter_id = d.encounter_id
    LEFT JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
    LEFT JOIN procedures proc ON e.encounter_id = proc.encounter_id
    WHERE e.encounter_date >= start_date
      AND e.encounter_date < end_date + INTERVAL '1 day'
    ORDER BY e.encounter_date;
END;
$$;

-- ============================================================
-- DATA EXPORT: CSV using COPY
-- ============================================================
-- Run from psql:
-- \copy (SELECT * FROM patient_encounter_summary) TO '/tmp/patient_summary.csv' WITH CSV HEADER;

-- ============================================================
-- DATA EXPORT: JSON using json_agg
-- ============================================================
SELECT json_agg(json_build_object(
    'patient_id', patient_id,
    'patient_name', patient_name,
    'total_encounters', total_encounters,
    'last_visit_date', last_visit_date,
    'emergency_visits', emergency_visits
))
FROM patient_encounter_summary
WHERE total_encounters > 3;

You now have reusable database objects that can power recurring reports without rewriting queries. Next up, you'll connect a BI tool to this database and build a visual dashboard from your data.

Connect Metabase and Build a Dashboard

You have a fully populated EMR database with validated data, reusable views, and export capabilities. The last piece of a real-world data extraction workflow is delivering insights through a BI dashboard.

In this step, you'll launch Metabase (an open-source BI tool) via Docker, connect it to your emr_clinic database, and build three visualizations that turn raw query results into an interactive dashboard.

In this step, get ready to:

• Launch Metabase in a Docker container and complete initial setup.
• Connect Metabase to the local PostgreSQL EMR database.
• Build a dashboard with three visualizations: a bar chart, a pie chart, and a table.

Run Metabase via Docker and connect it to your EMR database

Metabase runs as a standalone web application. The fastest way to start it locally is through a Docker container that downloads and runs everything for you.

• Return to your terminal and start the Metabase container by running this command:

docker run -d -p 3000:3000 --name metabase metabase/metabase

What does this command do?

This pulls the official Metabase image and runs it as a background container named metabase. The -p 3000:3000 flag maps port 3000 inside the container to port 3000 on your machine, so you can access Metabase in your browser.

Metabase takes about 1-2 minutes to initialize on first launch. You can watch the startup logs to know when it is ready.

• Monitor the startup progress by running:

docker logs -f metabase

• Wait until you see a message indicating Metabase is ready, then press Ctrl+C to stop following the logs.
• Open your browser and navigate to http://localhost:3000.

You should see the Metabase welcome screen. Now you'll walk through the setup wizard to create an account and connect your database.

• Click Let's get started.
• Create an admin account by entering your name, email, and a password.
• Click Next.
• On the database connection screen, select PostgreSQL as the database type.
• Fill in the connection details using the values below.

• Display name: EMR Clinic.
• Host: host.docker.internal.
• Port: 5432.
• Database name: emr_clinic.
• Username: postgres.
• Password: leave blank (unless you set a password during PostgreSQL setup).

Why host.docker.internal instead of localhost?

Metabase is running inside a Docker container. From inside that container, localhost refers to the container itself, not your Mac.

The special hostname host.docker.internal tells Docker to route the connection to your host machine where PostgreSQL is running.

• Click Next to save the database connection.
• On the usage data preferences screen, choose your preference and click Next.
• Click Take me to Metabase to finish setup.

You should now see the Metabase home screen with your EMR Clinic database listed.

Can't connect to the database?

Make sure PostgreSQL is still running on your host machine. Run brew services list in your terminal and confirm postgresql@17 shows as started.

If the connection test fails, check that host.docker.internal is entered correctly as the host. Using localhost will not work from inside the Docker container.

Help me troubleshoot my Metabase connection to PostgreSQL.

Build 3 dashboard visualizations

Now you'll create three saved questions (Metabase's term for charts) and assemble them on a single dashboard. Each question uses a SQL query against your EMR data.

Start with the monthly encounter volume bar chart.

• Click + New in the top navigation bar.
• Select SQL query.
• Make sure EMR Clinic is selected as the database in the top-left dropdown.
• Paste the following query into the SQL editor:

-- Monthly encounter volumes
SELECT
    TO_CHAR(DATE_TRUNC('month', encounter_date), 'YYYY-MM') AS month,
    COUNT(*) AS encounter_count
FROM encounters
GROUP BY DATE_TRUNC('month', encounter_date)
ORDER BY month;

• Click the blue Run query button (or press Cmd+Return on macOS).
• Click the Visualization button at the bottom-left of the screen.
• Select the bar chart icon from the visualization sidebar.
• Click Save in the top-right corner.
• Name the question Monthly Encounter Volume and save it to the default collection.

What does this visualization show?

This bar chart groups all patient encounters by month across the two-year dataset. Each bar represents the total number of visits (outpatient, inpatient, emergency, telehealth) for that month. It gives a quick view of clinic utilization trends over time.

Next, create the top-10 diagnoses pie chart.

• Click + New in the top navigation bar.
• Select SQL query.
• Paste this query into the editor:

-- Top 10 diagnoses by frequency
SELECT
    dc.description,
    COUNT(*) AS diagnosis_count
FROM diagnoses d
JOIN diagnosis_codes dc ON d.icd_code = dc.icd_code
GROUP BY dc.description
ORDER BY diagnosis_count DESC
LIMIT 10;

• Click Run query.
• Click the Visualization button at the bottom-left.
• Select the pie chart (donut) icon.
• Click Save and name it Top 10 Diagnoses.

What does this visualization show?

This pie chart shows the relative frequency of the 10 most common diagnoses in your EMR data. It joins the diagnoses table to diagnosis_codes to display human-readable descriptions instead of raw ICD codes.

Finally, create the patient demographics table.

• Click + New in the top navigation bar.
• Select SQL query.
• Paste this query:

-- Patient demographics breakdown
SELECT
    patient_name,
    gender,
    insurance_provider,
    total_encounters,
    emergency_visits,
    last_visit_date
FROM patient_encounter_summary
ORDER BY total_encounters DESC;

• Click Run query.
• Leave the visualization as a table (the default for this query shape).
• Click Save and name it Patient Demographics.

Why use the patient_encounter_summary view?

This query pulls from the view you created in Step 5. The view pre-aggregates encounter counts and last visit dates per patient, so Metabase can display a ready-made summary table without re-running complex JOINs each time the dashboard loads.

Now assemble all three questions into a single dashboard.

• Click + New in the top navigation bar.
• Select Dashboard.
• Name the dashboard EMR Analytics and click Create.
• Metabase opens the empty dashboard in edit mode. Click the + icon to add a question.
• Search for and select Monthly Encounter Volume. The bar chart card appears on the dashboard.
• Click + again and add Top 10 Diagnoses.
• Click + once more and add Patient Demographics.
• Drag the cards to arrange them. Place the bar chart across the top, the pie chart bottom-left, and the table bottom-right.
• Click Save in the top-right corner.

Your dashboard now displays all three visualizations. You should see monthly encounter bars, a pie chart of the most common diagnoses, and a patient summary table sorted by visit count.

Charts not showing data?

If a card displays an error, click into it to re-run the query. Check that EMR Clinic is selected as the database and that the patient_encounter_summary view exists (it was created in Step 5).

If Metabase shows a connection error, verify the Docker container is still running with docker ps in your terminal.

Help me fix my Metabase dashboard cards that are not loading data.

Your EMR data extraction workflow is now complete. You've gone from a raw clinical schema all the way through to an interactive BI dashboard. In the Secret Mission, you'll build one more powerful stored procedure that brings it all together.

Secret mission

Build a Patient 360 Stored Procedure

In real healthcare environments, clinicians need a single command to pull a patient's complete clinical history. Can you build a stored procedure that generates a full clinical narrative for any patient in the database?

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:

• Local PostgreSQL database (emr_clinic).
• Docker container running Metabase (metabase).
• PostgreSQL 17 installed via Homebrew.

Keep everything running

No action needed. Choose this if you're still actively building or want to keep practicing SQL queries against the EMR database.

• PostgreSQL continues running in the background and uses minimal resources.
• The Metabase container stays accessible at http://localhost:3000.
• You can stop and restart Metabase any time with docker stop metabase and docker start metabase.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your data and configuration stay intact for when you return.

• Stop the Metabase container by running:

docker stop metabase

• Stop PostgreSQL by running:

brew services stop postgresql@17

• To resume later, start PostgreSQL with brew services start postgresql@17 and Metabase with docker start metabase.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes the database, container, and PostgreSQL installation.

• Stop the Metabase container and remove it by running:

docker stop metabase
docker rm metabase

• Drop the EMR database by running:

dropdb emr_clinic

• Stop and uninstall PostgreSQL 17 by running:

brew services stop postgresql@17
brew uninstall postgresql@17

• Delete your local SQL files (schema.sql, seed_data.sql, queries.sql, validation.sql, views_and_procedures.sql, patient_360.sql) by deleting the project folder you created on your Desktop.

Nice Work!

Nice Work!

You've built a complete EMR data extraction workflow from the ground up. This project demonstrates the exact skills needed for an EMR Data Extraction Specialist role: querying clinical databases, validating data quality, creating reusable extracts, and delivering results through BI tools.

You've learned how to:

• Build a realistic EMR database schema with 7 interconnected clinical tables in PostgreSQL, and populate it with sample patient, encounter, diagnosis, procedure, and medication data.
• Write complex SQL extraction queries using JOINs, CTEs, and window functions to analyze clinical data, validate data quality, and create reusable views and stored procedures for recurring reporting needs.
• Connect Metabase to a live PostgreSQL database and build an interactive BI dashboard with visualizations for encounter volumes, top diagnoses, and patient demographics.
• Secret Mission: Built a get_patient_360 stored procedure that generates complete clinical summaries for any patient, including demographics, encounters, diagnoses, procedures, and medications in chronological order.

Ready to quiz yourself?