Patient Throughput & Scheduling Bottleneck Analysis

A SQL + Power BI analysis of synthetic clinic encounter data, identifying which encounter types, days, and hours are associated with the longest visit durations and the most schedule strain i.e., where patient flow is bottlenecking.

The business question

A clinic wants to know where its schedule is actually straining: which visit types consistently run long, whether certain days/hours compound the problem (high volume and high duration at the same time), and what a schedule-template change could do about it. This project treats "bottleneck" as an operational definition, not a vibe: a (encounter class, day, hour) combination only counts if it ranks poorly on duration and carries real visit volume, since a slow slot with three visits a week isn't a scheduling problem worth acting on.

Data

Synthea synthetic patient records: 1,171 patients, 53,346 encounters, 5,855 providers, 1,119 organizations. This is fully synthetic data (no real PHI), which is why it's usable for a public portfolio project at all. Key file: encounters.csv (one row per visit, with START/STOP timestamps and ENCOUNTERCLASS).

Raw CSVs are included in data/raw/ in this repo (~18MB total, well under GitHub's size limits) so the SQL runs immediately after cloning, with no separate download or Synthea generation step required.

Tools

• DuckDB: chosen over Postgres/SQLite for this project because the entire task is analytical (aggregate, group, rank) with no need for a running server or multi-user access. DuckDB queries CSV files directly with no separate import step.
• Power BI (web/service): built entirely in the browser, no Desktop install. Free-tier "My workspace" is sufficient since this is a personal portfolio piece, not a shared team report.

Repo structure

.
├── README.md
├── sql/
│   ├── 03_data_quality_profile.sql   # nulls, date range, duration distribution, outliers
│   ├── 04_core_aggregation.sql       # avg/median duration + visit count by class/day/hour
│   ├── 05_ranked_bottlenecks.sql     # RANK() window function, worst slot per class
│   └── 06_export_for_powerbi.sql     # exports the aggregated (not raw) result to CSV
├── data/
│   ├── raw/               # Synthea source CSVs, included in this repo
│   └── processed/         # aggregated output, tracked; this is the actual deliverable
└── screenshots/           # Power BI visuals (see below)

File numbering follows the project's stage numbering (stages 1-2 were problem framing and data acquisition, no SQL produced there).

Running this yourself

1. Clone this repo (the Synthea CSVs are already included in data/raw/)
2. Install DuckDB (e.g. brew install duckdb on macOS).
3. Run the SQL from the repo root (paths are relative to it):

   duckdb < sql/03_data_quality_profile.sql
   duckdb < sql/04_core_aggregation.sql
   duckdb < sql/05_ranked_bottlenecks.sql
   duckdb < sql/06_export_for_powerbi.sql
   

4. Upload data/processed/encounter_bottleneck_summary.csv to Power BI (workspace → New item → Semantic model → CSV → Upload file → Create a report) to rebuild the visuals described below.

(Want a fresh/different Synthea dataset instead? Generate one with the Synthea generator, e.g. run_synthea -p 1200, and drop the four CSVs into data/raw/ in place of the included ones. Synthea generates new random data every run, so exact numbers won't match this repo's findings, but the SQL logic will run correctly against any Synthea-shaped export.)

Key finding

Across nearly all classes, the "worst" ranked day/hour slot turned out to be a small- sample artifact or (for urgentcare) impossible to detect at all, since every visit in this dataset is a fixed 15 minutes. The one credible, actionable pattern:

Wellness visits on Saturday afternoon (~4pm) show both above-average volume (177 visits vs. a typical 101) and above-average duration (26.2 min vs. a 22.4 min class average). Recommendation: extend the default wellness slot length specifically for that block, or cap/redistribute bookings in it.

Honest caveats: this is synthetic data, and at least one strong-looking statistical pattern turned out to be a data-generation artifact rather than a real finding.

Dashboard

the day x hour Matrix heatmap (colored by average duration, with an encounter-class slicer)

weighted-average bar chart by encounter class, both built in Power BI's web report editor.*