Skip to content
Tutorials Center
My learning

Topics

Lesson 117 of 125 ยท Projects

Project: Healthcare Data and KPIs

Build hospital KPIs in SQL. Compute billing and length of stay, count diagnoses, protect small groups, check data quality, calculate 30-day readmission and publish a dashboard view.

A hospital network wants a weekly dashboard. Executives ask how much is being billed and how much of it is paid, medical directors ask which conditions fill the beds and how long patients stay, and quality teams ask the question that matters most: how many patients come back within 30 days?

In this project you build those answers from the clinic sample database: 3 hospitals, 12 doctors, 70 patients, 12 diagnoses and 155 visits with billing, all for 2025. Every person in it is synthetic; patients are only codes like P0007. You will compute KPIs, check the data before trusting it, calculate a readmission rate with a window function, and finish by publishing a view that a dashboard tool can query without knowing any of the joins.

You'll use joins, aggregation, date functions, LEAD and LAG, CTEs and views.

Real patient data is regulated (HIPAA in the United States, GDPR in Europe, and similar laws elsewhere). Analysts usually work on de-identified extracts, never copy patient lists into spreadsheets, and suppress very small groups in reports. You'll practice the last habit in Stage 3.

The tables

Table One row is...
hospitals a site, with a rate_factor that scales its prices
doctors a physician and the hospital they work at
patients a person (code, gender, birth year, city)
diagnoses a condition with a category such as Respiratory
visits one stay: patient, hospital, doctor, diagnosis, admitted_on, discharged_on, visit_type
billing the bill for one visit: billed_cents, insurance_cents and paid_on (NULL means unpaid)

Every visit has exactly one bill, linked by visit_id. Money is in integer cents. Start with a look at the sizes:

SQL Table sizes and the date range ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

The 155 visits run from 3 January to 14 December 2025, and the three visit types are emergency (73), scheduled (47) and follow-up (35).

Stage 1: billing KPIs by hospital

Three numbers answer most of the finance questions: total billed, the insurance share (how much of the billed amount insurers cover) and unpaid bills. A fourth, average length of stay (ALOS), is the operations team's favorite: the average number of days between admission and discharge. In SQLite julianday() converts a date to a number, so subtracting two of them gives days.

SQL Hospital scorecard ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

Northgate Clinic is the busiest (71 visits) and bills the most overall, 138,596.48. St. Anselm Medical has far fewer visits (50) but the highest average bill, 2,643.81, which fits its higher rate_factor. Insurers cover about 71% to 73% of every hospital's bills, and 13 bills are unpaid in total (5, 4 and 4). SUM(b.paid_on IS NULL) works because a comparison is 1 or 0 in SQLite; elsewhere use SUM(CASE WHEN b.paid_on IS NULL THEN 1 ELSE 0 END).

Notice that the total ranks the hospitals differently from the average. Always show a volume next to a rate, or a small hospital with a few expensive patients looks like the problem.

Stage 2: which conditions fill the beds?

The medical director wants to know the most common diagnoses and how long each takes. Grouping by diagnosis gives the counts, and grouping by category gives the broader picture:

SQL Diagnoses and length of stay ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

Heart failure is the most common diagnosis (23 visits, 6.5 days on average), followed by stroke (22 visits, but a long 10.2 days) and the two respiratory conditions, COPD flare-ups and pneumonia (19 each). By category, the bed days tell the real story: they multiply visits by stay length, so they show where capacity actually goes. Respiratory conditions have the most visits (46), but neurological ones use more beds (232 bed days against 213) because strokes stay 10.2 days. A category with fewer visits but long stays matters more to capacity planning than its visit count suggests.

Stage 3: demographics, with small-group suppression

Reports are often split by gender and age. Healthcare adds a rule: don't publish counts below a minimum, usually 5 or 10, because in a small group a count can identify a person. Instead of showing 2, show <5. A CASE expression does that, and a derived column groups birth years into age bands:

SQL Gender and age bands with suppression ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

The 28 female and 29 male patients are shown normally, but the two patients with gender O (3 visits) appear as <5, so nobody can be singled out. The age bands show the oldest group (65 and over) has both the most visits (51) and the longest average stay (5.7 days). One caution: the '<5' text and the numbers sit in the same column, which is fine for a report but means you can't add that column up. When you suppress a small group in a table that also shows a total, you may need to suppress a second group (or round) so the hidden number cannot be recovered by subtraction.

Stage 4: check the data before you trust it

A KPI built on bad data is worse than no KPI. Before calculating readmissions, test an assumption: a patient cannot be in two stays at once. Join visits to itself and look for pairs for the same patient whose date ranges overlap:

SQL Overlapping stays for the same patient ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

The two ranges overlap when each one starts before the other ends. The v2.id > v1.id condition makes sure each pair is reported once. There are seven overlapping pairs among six patients: for example patient 48 has an admission on 27 April that runs to 5 May and a second admission on 3 May. In a real hospital that might be a transfer between wards that was recorded as two visits, or a data entry mistake. It matters for the next stage: a readmission is a return after discharge, so overlapping rows must not be counted as returns. The query below only counts a next visit that starts on or after the discharge date. Say so in your report notes, and send the list of affected visits to whoever owns the data.

Stage 5: the 30-day readmission rate

A readmission is an unplanned return soon after a discharge, and 30 days is the usual window. Hospitals are measured on it. Here we define it as: the patient's next visit is an emergency visit that starts 0 to 30 days after the discharge. Planned scheduled and follow-up visits don't count.

LEAD looks at the next row in a patient's history. Partition by patient, order by admission, and fetch the next admission date and type next to each visit. Then compare dates. Discharges in the last 30 days of the data are excluded, because their window is not complete yet (we stop at 1 December for data that ends in mid-December):

SQL Readmission rate, overall and by category ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

Of 154 discharges with a complete 30-day window, 36 were followed by an emergency return: a readmission rate of 23.4%. Respiratory conditions stand out at 33.3% (15 of 45), against 13.0% for digestive problems. That is the kind of finding that starts a conversation about discharge planning for COPD and pneumonia patients. Three cautions apply: (1) small categories like Metabolic (6 discharges) are too noisy to rank, so show the discharge count next to every rate; (2) the CASE gives NULL-safe behavior because a comparison with a NULL next_type is not true, so the patient's last visit counts as not readmitted; and (3) this synthetic data deliberately has a high rate, so don't compare it with real-world benchmarks.

Stage 6: publish a dashboard view

Dashboard tools such as Metabase, Power BI or Looker Studio are happiest with one flat table. You don't want each chart author to rewrite five joins and the readmission logic, since that is how two dashboards end up showing two different rates. Put the logic in a view once: a flat row per visit with everything a chart might need.

SQL A fact view and the queries it makes easy ยท database: clinic

Press Run (or Ctrl + Enter) to see the result here.

With the view in place, a chart needs only a GROUP BY. The hospital comparison now includes readmissions: Northgate Clinic has the highest rate (28.2%), St. Anselm Medical 22.0% and Riverside General 15.2%. AVG(readmitted_30d) is the rate directly, because the flag is 1 or 0, and visits with a NULL flag (still inside their 30-day window) are skipped by AVG, so there's no need for a filter. Northgate's high rate sits next to the highest volume and the longest average stay, so a manager might look at its case mix before drawing conclusions.

Give each dashboard its own view, and treat the view's columns as a contract. If the readmission rule changes, you change one CREATE VIEW and every chart updates. In PostgreSQL a materialized view can store the result when the underlying query is slow.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why show the number of discharges next to every readmission rate?

    A category with 6 discharges and one readmission shows 16.7%, which is far less reliable than 33.3% from 45 discharges. Always pair a rate with its denominator.
  2. 2. What does LEAD(admitted_on) OVER (PARTITION BY patient_id ORDER BY admitted_on) return?

    LEAD reads the following row within the partition. Partitioning by patient keeps one patient's history separate from another's, and the last row has no next row, so the value is NULL.
  3. 3. Why are discharges in the last 30 days of the data excluded from the readmission rate?

    A patient discharged yesterday has had one day to return. Counting them as 'not readmitted' would understate the rate, so incomplete windows are left out (the view returns NULL for them).
  4. 4. Why show '<5' instead of the real count for a very small group?

    Small-cell suppression is a common privacy rule in health reporting. With only two patients in a group, publishing the count can identify them when combined with other columns.

Summary

  • Put a volume next to every rate, and compute averages such as length of stay with AVG(julianday(end) - julianday(start)).
  • Group by category as well as by item, and multiply visits by stay length to get bed days when capacity is the question.
  • Suppress small groups (for example under 5) in anything people will read, and remember that a total can undo it.
  • Test assumptions with a self join (for example overlapping stays) before building KPIs on the data.
  • Compute a readmission rate with LEAD partitioned by patient, count only complete windows, and only count the kinds of return your definition names.
  • Put the shared logic in a view so every dashboard shows the same number.

Next: Project: Music Streaming Analytics, where the data is a play log and the questions are about listening sessions.