Join Patients and Appointments | Dataford Interview Questions

The University of Kansas Medical Center needs a patient appointment summary for operational reporting. Write a PostgreSQL query that joins the patient and appointment data and returns one row for every patient, including patients who have never had an appointment.

Requirements

Join patients to appointments using the patient identifier.
Calculate the number of appointments for each patient.
Return patients with zero appointments, and sort by appointment count descending, then patient name ascending.

Schema

patients

Column	Type	Description
patient_idPK	INTEGER	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name
email	VARCHAR(255)	Contact email when available

appointments

Column	Type	Description
appointment_idPK	INTEGER	Unique appointment identifier
patient_id	INTEGER	Patient associated with the appointment
appointment_date	DATE	Scheduled appointment date
status	VARCHAR(30)	Appointment status

Your question is Join Patients and Appointments. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem