SQL Joins Across Tables

MediumSQL · PostgreSQL

Problem

Health Catalyst's Population Health workflows need a patient-level list of active patients with currently open care gaps. Write a PostgreSQL query that joins patient, care gap, and outreach action data.

Requirements

Include only active patients with open care gaps due between January 1 and March 31, 2025.
Return the patient name, region, number of distinct open care gaps, number of non-cancelled outreach actions, and most recent action date.
Classify patients with two or more qualifying care gaps as high-touch; classify all others as standard.
Include patients even when they have no qualifying outreach actions, then sort by care gap count descending and patient name ascending.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name
region	VARCHAR(50)	Care delivery region
is_active	BOOLEAN	Whether the patient is actively enrolled
enrollment_date	DATE	Date the patient entered the program

care_gaps

Column	Type	Description
gap_idPK	INT	Unique care gap identifier
patient_id	INT	Patient associated with the gap
gap_type	VARCHAR(50)	Clinical or preventive care gap type
status	VARCHAR(20)	Current gap status
due_date	DATE	Date by which the gap should be addressed
priority	VARCHAR(20)	Operational priority

outreach_actions

Column	Type	Description
action_idPK	INT	Unique outreach action identifier
gap_id	INT	Care gap targeted by the action
action_status	VARCHAR(20)	Status of the outreach action
action_date	DATE	Date the action occurred
action_note	VARCHAR(200)	Brief operational note

Tablespatientscare_gapsoutreach_actions