Healthcare episodes

This tutorial turns raw encounters into episodes of care, checks for 30-day readmission, and reads a corrected laboratory trend.

The three questions share a patient timeline but require different techniques: interval merging, a lead-window comparison, and ordered values from an event series.

This advanced tutorial assumes RELATED() and array bindings from E-commerce . Inside TRANSFORM(), read the nested query as SQL scoped to one patient's array. Run Data and Model before the episode and lab queries.

Meet the two patient histories

Alex and Blake each have several encounters:

Alex has an emergency-department visit that hands off to an inpatient stay, plus another inpatient admission within 30 days of a later discharge.
Blake has a similar ED-to-inpatient handoff but no later readmission.
Alex also has four troponin results. The first was corrected from 2.5 to 2.8, and the table already contains the corrected value.

FeatureQL

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

The data is intentionally small, but the event order matters. Keep the admit, discharge, and lab timestamps visible as you read the queries.

Build patient-scoped timelines

Declare patients, encounters, and orders as separate entities:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.HC AS
PERSIST
    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

Map encounters and lab orders back to their patient:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.HC AS
PERSIST
    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

Patients define the outer grain. RELATED() collects their child rows, and TRANSFORM() performs ordered calculations inside each patient's array without producing one outer row per event.

Merge encounters into episodes of care

Two adjacent encounters belong to the same episode when the next admit occurs on or before the previous discharge. The query:

orders encounters by admit time;
marks where a new gap begins;
cumulatively numbers the episodes; and
rolls each episode up to its earliest admit and latest discharge.

FeatureQL

DEFINE
    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(
        RETURN
            enc_id,
            admit_date,
            discharge_date,
            episode_num := 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)
        ORDER BY admit_date
    ),
    episodes := enc_episodes.TRANSFORM(
        RETURN
            episode_num,
            episode_start := MIN(admit_date) GROUP BY (episode_num),
            episode_end := MAX(discharge_date) GROUP BY (episode_num),
            n_encounters := COUNT() GROUP BY (episode_num)
        ORDER BY episode_num
    )
RETURN
    patient,
    episodes
FROM FM.HC
FOR
    patient_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY patient
;

Result

PATIENT VARCHAR	EPISODES ARRAY<ROW<episode_num BIGINT, episode_start DATE, episode_end DATE, n_encounters BIGINT>>
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}]

PATIENT VARCHAR

EPISODES ARRAY<ROW<episode_num BIGINT, episode_start DATE, episode_end DATE, n_encounters BIGINT>>

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}]

For Alex, the January 12 ED visit and January 12–18 inpatient stay become one episode with n_encounters: 2. Blake's February 20–21 ED visit and February 21–26 inpatient stay merge in the same way.

Reduce the timeline to one count

Reuse the episode logic, but return only the number of episodes per patient:

FeatureQL

DEFINE
    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(
        RETURN
            enc_id,
            admit_date,
            discharge_date,
            episode_num := 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)
        ORDER BY admit_date
    ),
    episodes := enc_episodes.TRANSFORM(
        RETURN
            episode_num,
            episode_start := MIN(admit_date) GROUP BY (episode_num),
            episode_end := MAX(discharge_date) GROUP BY (episode_num),
            n_encounters := COUNT() GROUP BY (episode_num)
        ORDER BY episode_num
    ),
    n_episodes := ARRAY_COUNT(episodes)
RETURN
    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 has 5 episodes and Blake has 3. The count is based on merged care episodes, not raw encounter rows.

Flag a 30-day readmission

Now narrow the timeline to inpatient stays. For each discharge, look at the next inpatient admission and ask whether it occurs within 30 days.

FeatureQL

DEFINE
    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(
        RETURN *
        WHERE etype = 'inpatient'
        ORDER BY admit
    ),
    inpatient_flags := inpatient_only.TRANSFORM(
        RETURN
            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(RETURN BOOL_OR(readmit_30d)),
    has_readmission_30d := COALESCE(UNWRAP_ONE(readmit_agg), FALSE)
RETURN
    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 returns on March 5 after a February 15 discharge, so the result is true. Blake has only one inpatient stay, so there is no later admission and the result is false.

The ordering is essential: this is a comparison with the next stay, not any later row joined by patient ID.

Read the corrected troponin trend

Finally, filter Alex's lab orders to troponin and sort them by time. Because the source table already contains the late correction, the first value should be 2.8, not the original 2.5.

FeatureQL

DEFINE
    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(
        RETURN
            ordered_at,
            result_value
        WHERE item = 'troponin' AND status = 'final'
        ORDER BY ordered_at
    )
RETURN
    patient,
    troponin_trend
FROM FM.HC
FOR
    patient_id := BIND_VALUE(1)
;

Result

PATIENT VARCHAR	TROPONIN_TREND ARRAY<ROW<ordered_at TIMESTAMP(3), result_value DOUBLE>>
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}]

The ordered series is 2.8 → 1.8, then 3.1 → 2.4 during the March readmission. Both pairs trend down.

This last example is simpler than episode merging, but it reinforces the same rule: build the metric from the ordered, corrected patient history rather than from storage order.

What's next

You have converted encounter rows into episodes, compared consecutive stays, and inspected an ordered lab series at the patient grain.

Continue with Product analytics for cohorts, funnels, and behavioral windows, or Marketing attribution for weighted event sequences.

Last update at: 2026/10/13 08:32:21

On this page

Meet the two patient histories Build patient-scoped timelines Merge encounters into episodes of care Reduce the timeline to one count Flag a 30-day readmission Read the corrected troponin trend What's next