Healthcare episodes
On a two-patient encounter log, merge overlapping stays into episodes of care, flag 30-day readmissions, and follow a troponin trend that already reflects a late lab correction.
This advanced tutorial assumes RELATED() and array bindings from E-commerce . The episode logic combines familiar SQL window functions with TRANSFORM() over each patient's encounter array. Run Data and Model before the episode and lab queries.
Data
Two patients. Alex has an ED→inpatient handoff that should merge, and a later inpatient stay within 30 days of discharge. Blake has a similar ED→inpatient merge but no readmission. Alex’s first troponin was corrected from 2.5 to 2.8 (value already applied in the orders table).
FeatureQL
/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_hc;
--
DROP TABLE IF EXISTS tutorial_hc.orders;
--
DROP TABLE IF EXISTS tutorial_hc.encounters;
--
DROP TABLE IF EXISTS tutorial_hc.patients;
--
CREATE TABLE tutorial_hc.patients (
id BIGINT,
name VARCHAR
);
--
INSERT INTO tutorial_hc.patients VALUES
(1, 'Alex'),
(2, 'Blake');
--
CREATE TABLE tutorial_hc.encounters (
id BIGINT,
patient_id BIGINT,
encounter_type VARCHAR,
admit_date DATE,
discharge_date DATE
);
--
INSERT INTO tutorial_hc.encounters VALUES
-- Alex: outpatient, ED+inpatient merge, outpatient, inpatient, readmit inpatient
(1, 1, 'outpatient', DATE '2024-01-05', DATE '2024-01-05'),
(2, 1, 'emergency', DATE '2024-01-12', DATE '2024-01-12'),
(3, 1, 'inpatient', DATE '2024-01-12', DATE '2024-01-18'),
(4, 1, 'outpatient', DATE '2024-02-01', DATE '2024-02-01'),
(5, 1, 'inpatient', DATE '2024-02-10', DATE '2024-02-15'),
(6, 1, 'inpatient', DATE '2024-03-05', DATE '2024-03-08'),
-- Blake: outpatient, ED+inpatient merge, outpatient
(7, 2, 'outpatient', DATE '2024-01-08', DATE '2024-01-08'),
(8, 2, 'emergency', DATE '2024-02-20', DATE '2024-02-21'),
(9, 2, 'inpatient', DATE '2024-02-21', DATE '2024-02-26'),
(10, 2, 'outpatient', DATE '2024-03-10', DATE '2024-03-10');
--
CREATE TABLE tutorial_hc.orders (
id BIGINT,
encounter_id BIGINT,
patient_id BIGINT,
item VARCHAR,
ordered_at TIMESTAMP,
result_value DOUBLE,
status VARCHAR
);
--
INSERT INTO tutorial_hc.orders VALUES
-- Alex troponin on Feb inpatient (first value already corrected 2.5 → 2.8)
(1, 5, 1, 'troponin', TIMESTAMP '2024-02-10 06:00:00', 2.8e0, 'final'),
(2, 5, 1, 'troponin', TIMESTAMP '2024-02-10 12:00:00', 1.8e0, 'final'),
-- Alex troponin on March readmit
(3, 6, 1, 'troponin', TIMESTAMP '2024-03-05 05:00:00', 3.1e0, 'final'),
(4, 6, 1, 'troponin', TIMESTAMP '2024-03-05 11:00:00', 2.4e0, 'final'),
-- Blake HbA1c (context only; not queried in this tutorial)
(5, 7, 2, 'hba1c', TIMESTAMP '2024-01-08 09:00:00', 8.9e0, 'final');
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_hc.patients;
Result
| Count BIGINT |
|---|
| 2 |
Model
Patients are the binding entity. Encounters and orders hang off patient_id so each patient’s timeline is an array you can window over.
FeatureQL
CREATE OR REPLACE FEATURES IN FM.HC AS
SELECT
patients := ENTITY(),
patient_id := INPUT(BIGINT#patients)
;Result
| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.HC.PATIENTS | CREATED | Feature created as not exists |
| FM.HC.PATIENT_ID | CREATED | Feature created as not exists |
FeatureQL
CREATE OR REPLACE FEATURES IN FM.HC AS
SELECT
tables.patients := EXTERNAL_COLUMNS(
id BIGINT#patients BIND TO patient_id,
name VARCHAR
FROM TABLE(tutorial_hc.patients)
),
tables.patient_encounters := EXTERNAL_COLUMNS(
id BIGINT,
patient_id BIGINT#patients BIND TO patient_id,
encounter_type VARCHAR,
admit_date DATE,
discharge_date DATE
FROM TABLE(tutorial_hc.encounters)
),
tables.patient_orders := EXTERNAL_COLUMNS(
id BIGINT,
encounter_id BIGINT,
patient_id BIGINT#patients BIND TO patient_id,
item VARCHAR,
ordered_at TIMESTAMP,
result_value DOUBLE,
status VARCHAR
FROM TABLE(tutorial_hc.orders)
)
;Result
| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.HC.TABLES.PATIENTS | CREATED | Feature created as not exists |
| FM.HC.TABLES.PATIENT_ENCOUNTERS | CREATED | Feature created as not exists |
| FM.HC.TABLES.PATIENT_ORDERS | CREATED | Feature created as not exists |
Episodes of care
When one encounter’s admit falls on or before the previous discharge, they belong to the same episode. Assign an episode_num with a cumulative sum over a gap check, then roll up min admit / max discharge.
FeatureQL
WITH
patient := tables.patients[name],
all_enc := patient_id.RELATED(
ARRAY_AGG(
ROW(
tables.patient_encounters[id] AS enc_id,
tables.patient_encounters[admit_date] AS admit_date,
tables.patient_encounters[discharge_date] AS discharge_date
)
)
GROUP BY tables.patient_encounters[patient_id]
),
enc_episodes := all_enc.TRANSFORM(
SELECT
ENC_ID, ADMIT_DATE, DISCHARGE_DATE,
SUM(CASE
WHEN ADMIT_DATE > COALESCE(
LAG(DISCHARGE_DATE) OVER (ORDER BY ADMIT_DATE),
DATE '0001-01-01'
) THEN 1 ELSE 0
END) OVER (ORDER BY ADMIT_DATE) AS EPISODE_NUM
ORDER BY ADMIT_DATE
),
episodes := enc_episodes.TRANSFORM(
SELECT
EPISODE_NUM,
MIN(ADMIT_DATE) GROUP BY EPISODE_NUM AS EPISODE_START,
MAX(DISCHARGE_DATE) GROUP BY EPISODE_NUM AS EPISODE_END,
COUNT(1) GROUP BY EPISODE_NUM AS N_ENCOUNTERS
ORDER BY EPISODE_NUM
)
SELECT
patient,
episodes
FROM FM.HC
FOR
patient_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY patient
;Result
| PATIENT VARCHAR | EPISODES VARBINARY |
|---|---|
| Alex | [{episode_num: 1, episode_start: 2024-01-05, episode_end: 2024-01-05, n_encounters: 1}, {episode_num: 2, episode_start: 2024-01-12, episode_end: 2024-01-18, n_encounters: 2}, {episode_num: 3, episode_start: 2024-02-01, episode_end: 2024-02-01, n_encounters: 1}, {episode_num: 4, episode_start: 2024-02-10, episode_end: 2024-02-15, n_encounters: 1}, {episode_num: 5, episode_start: 2024-03-05, episode_end: 2024-03-08, n_encounters: 1}] |
| Blake | [{episode_num: 1, episode_start: 2024-01-08, episode_end: 2024-01-08, n_encounters: 1}, {episode_num: 2, episode_start: 2024-02-20, episode_end: 2024-02-26, n_encounters: 2}, {episode_num: 3, episode_start: 2024-03-10, episode_end: 2024-03-10, n_encounters: 1}] |
Alex: ED Jan 12 + inpatient Jan 12–18 → one episode (n_encounters: 2). Blake: ED Feb 20–21 + inpatient Feb 21–26 → likewise merged.
Episode counts
Same logic; keep only the count per patient.
FeatureQL
WITH
patient := tables.patients[name],
all_enc := patient_id.RELATED(
ARRAY_AGG(
ROW(
tables.patient_encounters[id] AS enc_id,
tables.patient_encounters[admit_date] AS admit_date,
tables.patient_encounters[discharge_date] AS discharge_date
)
)
GROUP BY tables.patient_encounters[patient_id]
),
enc_episodes := all_enc.TRANSFORM(
SELECT
ENC_ID, ADMIT_DATE, DISCHARGE_DATE,
SUM(CASE
WHEN ADMIT_DATE > COALESCE(
LAG(DISCHARGE_DATE) OVER (ORDER BY ADMIT_DATE),
DATE '0001-01-01'
) THEN 1 ELSE 0
END) OVER (ORDER BY ADMIT_DATE) AS EPISODE_NUM
ORDER BY ADMIT_DATE
),
episodes := enc_episodes.TRANSFORM(
SELECT
EPISODE_NUM,
MIN(ADMIT_DATE) GROUP BY EPISODE_NUM AS EPISODE_START,
MAX(DISCHARGE_DATE) GROUP BY EPISODE_NUM AS EPISODE_END,
COUNT(1) GROUP BY EPISODE_NUM AS N_ENCOUNTERS
ORDER BY EPISODE_NUM
),
n_episodes := ARRAY_COUNT(episodes)
SELECT
patient,
n_episodes
FROM FM.HC
FOR
patient_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY patient
;Result
| PATIENT VARCHAR | N_EPISODES BIGINT |
|---|---|
| Alex | 5 |
| Blake | 3 |
Alex 5 episodes, Blake 3.
30-day readmission
Among inpatient stays only: is the next inpatient admit within 30 days of this discharge?
FeatureQL
WITH
patient := tables.patients[name],
all_encounters := patient_id.RELATED(
ARRAY_AGG(
ROW(
tables.patient_encounters[id] AS enc_id,
tables.patient_encounters[encounter_type] AS etype,
tables.patient_encounters[admit_date] AS admit,
tables.patient_encounters[discharge_date] AS discharge
)
)
GROUP BY tables.patient_encounters[patient_id]
),
inpatient_only := all_encounters.TRANSFORM(
SELECT * WHERE ETYPE = 'inpatient'
ORDER BY ADMIT ASC
),
inpatient_flags := inpatient_only.TRANSFORM(
SELECT
ENC_ID,
ADMIT,
DISCHARGE,
READMIT_30D :=
LEAD(ADMIT, 1) OVER (ORDER BY ADMIT ASC) IS NOT NULL
AND DATE_SUBTRACT(
LEAD(ADMIT, 1) OVER (ORDER BY ADMIT ASC)::TIMESTAMP,
DISCHARGE::TIMESTAMP,
'day'
) BETWEEN 0 AND 30
),
readmit_agg := inpatient_flags.TRANSFORM(SELECT BOOL_OR(readmit_30d)),
has_readmission_30d := COALESCE(UNWRAP_ONE(readmit_agg), FALSE)
SELECT
patient,
has_readmission_30d
FROM FM.HC
FOR
patient_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY patient
;Result
| PATIENT VARCHAR | HAS_READMISSION_30D BOOLEAN |
|---|---|
| Alex | true |
| Blake | false |
Alex true (e.g. discharged Feb 15, back Mar 5). Blake false (only one inpatient stay).
Troponin trend (with correction)
Final troponin values for Alex, ordered in time. The first result is the corrected 2.8, not the original 2.5.
FeatureQL
WITH
patient := tables.patients[name],
all_orders := patient_id.RELATED(
ARRAY_AGG(
ROW(
tables.patient_orders[item] AS item,
tables.patient_orders[ordered_at] AS ordered_at,
tables.patient_orders[result_value] AS result_value,
tables.patient_orders[status] AS status
)
)
GROUP BY tables.patient_orders[patient_id]
),
troponin_trend := all_orders.TRANSFORM(
SELECT ORDERED_AT, RESULT_VALUE
WHERE ITEM = 'troponin' AND STATUS = 'final'
ORDER BY ORDERED_AT ASC
)
SELECT
patient,
troponin_trend
FROM FM.HC
FOR
patient_id := BIND_VALUE(1)
;Result
| PATIENT VARCHAR | TROPONIN_TREND VARBINARY |
|---|---|
| Alex | [{ordered_at: 2024-02-10T06:00:00, result_value: 2.8}, {ordered_at: 2024-02-10T12:00:00, result_value: 1.8}, {ordered_at: 2024-03-05T05:00:00, result_value: 3.1}, {ordered_at: 2024-03-05T11:00:00, result_value: 2.4}] |
2.8 → 1.8 (February stay), then 3.1 → 2.4 (March readmission). Both legs trend down.
What's next
- Analytics overview — concept map for this series
- Product analytics — TTV, retention, funnels, rage clicks
- Marketing attribution — credit models over event sequences
- Supply inventory — as-of state from signed events