Healthcare data solutions: clinical notes and cohorts in one SQL query

docker run -d --name serenedb -p 7890:7890 \
    -e POSTGRES_PASSWORD=<password> serenedb/serenedb:26.11.2
psql -h localhost -p 7890 -U postgres

CREATE TABLE visit_occurrence (
    visit_occurrence_id INTEGER PRIMARY KEY,
    person_id INTEGER NOT NULL,
    care_site_name VARCHAR NOT NULL,
    visit_start_date DATE NOT NULL,
    visit_end_date DATE NOT NULL
);

CREATE TABLE note (
    note_id INTEGER PRIMARY KEY,
    person_id INTEGER NOT NULL,
    visit_occurrence_id INTEGER NOT NULL,
    note_date DATE NOT NULL,
    note_title VARCHAR NOT NULL,
    note_text VARCHAR NOT NULL
);

CREATE TABLE condition_occurrence (
    condition_occurrence_id INTEGER PRIMARY KEY,
    person_id INTEGER NOT NULL,
    visit_occurrence_id INTEGER NOT NULL,
    condition_source_value VARCHAR NOT NULL
);

INSERT INTO visit_occurrence VALUES
(1, 101, 'cardiology', '2026-09-02', '2026-09-05'),
(2, 102, 'cardiology', '2026-09-08', '2026-09-10'),
(3, 103, 'emergency', '2026-09-12', '2026-09-12'),
(4, 103, 'medicine', '2026-09-20', '2026-09-24'),
(5, 104, 'medicine', '2026-09-15', '2026-09-19'),
(6, 105, 'emergency', '2026-09-18', '2026-09-18'),
(7, 106, 'cardiology', '2026-09-22', '2026-09-26'),
(8, 106, 'cardiology', '2026-10-09', '2026-10-12'),
(9, 107, 'medicine', '2026-10-01', '2026-10-03'),
(10, 108, 'emergency', '2026-10-05', '2026-10-05'),
(11, 101, 'cardiology', '2026-10-20', '2026-10-22'),
(12, 109, 'medicine', '2026-10-14', '2026-10-17');

INSERT INTO note VALUES
(1, 101, 1, '2026-09-02', 'Admission note',
 '72-year-old man with a history of HTN and DM presents with chest pain radiating to the left arm. Troponin elevated.'),
(2, 101, 1, '2026-09-05', 'Discharge summary',
 'NSTEMI treated medically. Hypertension controlled on lisinopril. Discharged on aspirin, atorvastatin and metoprolol.'),
(3, 102, 2, '2026-09-08', 'Progress note',
 'New onset atrial fibrillation with rapid ventricular response. Started Eliquis. Denies chest pain.'),
(4, 102, 2, '2026-09-10', 'Discharge summary',
 'AFib rate controlled on metoprolol. Continue apixaban. History of CVA in 2021.'),
(5, 103, 3, '2026-09-12', 'ED note',
 'Hypertension poorly controlled, BP 182/110. Headache, no chest pain.'),
(6, 103, 4, '2026-09-20', 'Admission note',
 'Admitted with SOB and bilateral leg edema. Known HTN. Started furosemide.'),
(7, 104, 5, '2026-09-15', 'Progress note',
 'Type 2 diabetes with A1c 9.1. Metformin increased to 1000 mg twice daily.'),
(8, 105, 6, '2026-09-18', 'ED note',
 'Fall at home, distal radius fracture. No head injury.'),
(9, 106, 7, '2026-09-22', 'Admission note',
 'Dyspnea on exertion and orthopnea. HTN and heart failure with reduced ejection fraction.'),
(10, 106, 8, '2026-10-09', 'Admission note',
 'Readmitted with worsening dyspnea. Diuresed with IV furosemide. Hypertension stable.'),
(11, 107, 9, '2026-10-01', 'Progress note',
 'DM on metfomin, glucose 210. Reports polyuria.'),
(12, 108, 10, '2026-10-05', 'ED note',
 'Sudden left-sided weakness and slurred speech. Code stroke called, CT negative for bleed.'),
(13, 101, 11, '2026-10-20', 'Progress note',
 'Follow-up after NSTEMI. Chest pain on exertion, relieved by rest. HTN well controlled.'),
(14, 109, 12, '2026-10-14', 'Admission note',
 'Community-acquired pneumonia, started ceftriaxone. Telemetry shows atrial fibrillation, rate controlled.');

INSERT INTO condition_occurrence VALUES
(1, 101, 1, 'I21.4'), (2, 101, 1, 'I10'), (3, 101, 1, 'E11.9'),
(4, 102, 2, 'I48.91'),
(5, 103, 3, 'I10'),
(6, 103, 4, 'I50.9'), (7, 103, 4, 'I10'),
(8, 104, 5, 'E11.65'),
(9, 105, 6, 'S52.501A'), (10, 105, 6, 'W19.XXXA'),
(11, 106, 7, 'I50.9'), (12, 106, 7, 'I10'),
(13, 106, 8, 'I50.9'), (14, 106, 8, 'I10'),
(15, 107, 9, 'E11.65'),
(16, 108, 10, 'I63.9'),
(17, 101, 11, 'I10'),
(18, 109, 12, 'J18.9');

CREATE TEXT SEARCH DICTIONARY clinical_en AS
    split_text(case := 'lower')
    | expand_solr_synonyms('htn, hypertension
sob, dyspnea
dm, diabetes
cva, stroke
eliquis => apixaban')
    WITH (frequency, position, norm, offset);

CREATE INDEX note_idx ON note USING inverted (
    note_id, person_id, visit_occurrence_id, note_date,
    note_text clinical_en);

VACUUM (REFRESH_TABLE) note;

SELECT ts_lexize('clinical_en', 'htn') AS htn,
       ts_lexize('clinical_en', 'eliquis') AS eliquis;

-- A brand name finds its generic, ranked and marked
SELECT note_id,
       round(BM25(note_idx.tableoid)::numeric, 3) AS bm25,
       ts_highlight(note_idx.note_text) AS snippet
FROM note_idx
WHERE note_text @@ 'apixaban'
ORDER BY bm25 DESC, note_id;

-- A typo in the query, and one in a note
SELECT note_id, person_id, note_text
FROM note_idx
WHERE note_text @@ ts_levenshtein('metfromin', 1)
ORDER BY note_id;

-- Chest pain, unless the note denies it
SELECT note_id, person_id, note_text
FROM note_idx
WHERE note_text @@ (
    ts_phrase('chest pain')
    && !!ts_any([ts_phrase('denies chest pain'),
                 ts_phrase('no chest pain')]))
ORDER BY note_id;

WITH cohort AS (
    SELECT DISTINCT visit_occurrence_id
    FROM note_idx
    WHERE note_text @@ 'hypertension'
), visits AS (
    SELECT *,
           visit_end_date - visit_start_date AS los,
           lead(visit_start_date) OVER (
               PARTITION BY person_id
               ORDER BY visit_start_date
           ) - visit_end_date AS days_to_next
    FROM visit_occurrence
)
SELECT v.care_site_name AS unit,
       count(DISTINCT v.person_id) AS patients,
       count(*) AS visits,
       round(avg(v.los)::numeric, 1) AS avg_los_days,
       count(*) FILTER (WHERE v.days_to_next <= 30)
           AS back_in_30d
FROM cohort
JOIN visits v USING (visit_occurrence_id)
GROUP BY v.care_site_name
ORDER BY patients DESC, unit;