How would you use SQL window functions to compare each patient's lab result to their previous visit and calculate a running average over time at Abbott?
Use the patient and lab result data provided. Include all recorded lab visits, including visits with a missing result.
patient_id, patient_name, test_name, visit_date, lab_result, previous_lab_result, change_from_previous, and running_average.patient_id, test_name, visit_date, and visit_id.| Column | Type | Description |
|---|---|---|
| patient_idPK | INT | Unique patient identifier |
| patient_name | VARCHAR(100) | Patient's name |
| date_of_birth | DATE | Patient date of birth |
| sex | VARCHAR(20) | Recorded sex |
| Column | Type | Description |
|---|---|---|
| visit_idPK | INT | Unique lab visit identifier |
| patient_id | INT | References patients.patient_id |
| test_name | VARCHAR(80) | Name of the laboratory test |
| visit_date | DATE | Date of the laboratory visit |
| lab_result | NUMERIC(10,2) | Numeric laboratory measurement |