Build a cohort of patients from multiple tables of fake EMR data provided.
Use the provided EMR tables to identify adults with a qualifying outpatient diabetes encounter in 2024 and a follow-up encounter within 30 days. Include the latest HbA1c collected by the follow-up date and medications active on that date.
patient_id, patient_name, index_date, followup_date, diagnosis_count, latest_hba1c, and active_medication_count.index_date, then patient_id.| Column | Type | Description |
|---|---|---|
| patient_idPK | INT | Unique patient identifier |
| patient_name | VARCHAR(100) | Patient display name |
| date_of_birth | DATE | Patient birth date |
| sex | VARCHAR(20) | Recorded sex |
| Column | Type | Description |
|---|---|---|
| encounter_idPK | INT | Unique encounter identifier |
| patient_id | INT | Patient associated with the encounter |
| encounter_date | DATE | Date of encounter |
| encounter_type | VARCHAR(30) | Encounter setting |
| status | VARCHAR(20) | Encounter completion status |
| Column | Type | Description |
|---|---|---|
| diagnosis_idPK | INT | Unique diagnosis record identifier |
| encounter_id | INT | Encounter containing the diagnosis |
| diagnosis_code | VARCHAR(20) | ICD diagnosis code |
| Column | Type | Description |
|---|---|---|
| lab_idPK | INT | Unique laboratory result identifier |
| patient_id | INT | Patient associated with the result |
| lab_name | VARCHAR(50) | Laboratory test name |
| collected_at | TIMESTAMP | Specimen collection timestamp |
| result_value | DECIMAL(5,2) | Numeric laboratory result |
| Column | Type | Description |
|---|---|---|
| medication_idPK | INT | Unique medication record identifier |
| patient_id | INT | Patient receiving the medication |
| medication_name | VARCHAR(80) | Medication name |
| start_date | DATE | Medication start date |
| end_date | DATE | Medication end date |