SQL & querying

In one line

SQL is the most durable skill in health analytics — it has outlived every framework that promised to replace it. And its defining trait in clinical work is that the query that silently returns the wrong rows looks exactly like the one that doesn't.

The joins are where patients disappear

Everyone learns INNER JOIN first, and it is the single most common source of wrong answers in health analytics.

-- "Our diabetic patients and their latest HbA1c"
SELECT p.patient_id, o.value
FROM patient p
JOIN observation o ON o.patient_id = p.patient_id   -- INNER
WHERE o.code = 'hba1c';

This silently drops every diabetic who never had an HbA1c — which is precisely the cohort you should be worried about. The query runs. It returns rows. Nobody errors. And your report on diabetic control now describes only the patients who were being monitored.

That's the same denominator problem as everywhere else in this domain, expressed as a keyword. LEFT JOIN and then count your NULLs — the NULLs are the finding.

The general habit: after every join, check the row count. Went up? You have a fan-out (one-to-many you didn't expect — probably duplicate records). Went down? You dropped people. Both are answers about your data before they're bugs in your query.

Window functions — the ones that matter clinically

Almost every real clinical question is "per patient, ordered by time" — and that is exactly what window functions do.

-- Each patient's most recent HbA1c, keeping patients who have none
SELECT DISTINCT ON (p.patient_id) p.patient_id, o.value, o.effective_time
FROM patient p
LEFT JOIN observation o
  ON o.patient_id = p.patient_id AND o.code = 'hba1c'
ORDER BY p.patient_id, o.effective_time DESC NULLS LAST;

-- Time since the previous encounter, per patient
SELECT patient_id, encounter_time,
       encounter_time - LAG(encounter_time) OVER (
         PARTITION BY patient_id ORDER BY encounter_time
       ) AS gap
FROM encounter;

LAG, LEAD, ROW_NUMBER, FIRST_VALUE, running totals — these turn "readmission within 30 days", "time to first treatment", and "did the follow-up happen?" from a nightmare of self-joins into a readable query. If you learn one advanced SQL topic for health, learn this one.

The traps specific to clinical data

• NULL is not a value. WHERE value != 'X' excludes NULLs. So the patients with no recorded result vanish from your "not X" cohort, which is usually not what you meant.
• COUNT(*) vs COUNT(column) — the second skips NULLs. That difference has produced a lot of confidently wrong denominators.
• BETWEEN on timestamps is inclusive, so BETWEEN '2026-01-01' AND '2026-01-31' misses most of the 31st. Use >= start AND < next_start.
• Timezones. A midnight-boundary query on a timestamp without a zone will assign admissions to the wrong day, and the error is worst at exactly the times wards are busiest.
• Averaging what you shouldn't. AVG(patient_id) will happily run. So will AVG(pain_score) — and pain scores are ordinal, so their mean is meaningless even though the database computes it politely.

Reproducibility

The habit that separates an analyst from a person who ran a query: the SQL is the method.

Save it, version it, and comment why — not what. -- LEFT JOIN because patients with no HbA1c are the cohort of interest is the comment that stops the next person "fixing" your query into a wrong one.

And profile before you query. Every trap above is visible in a profile and invisible in a data dictionary.