Database Design With SQL | Dataford Interview Questions

Describe how you would design a database and write the SQL queries needed to support the use cases described in the prompt.

Use the provided patient, encounter, and diagnosis entities. The report must include patients without completed encounters and count diagnoses associated only with each patient's latest completed encounter.

Output

One row per patient with patient_id, patient_name, latest_completed_encounter, diagnosis_count, and encounter_status.
Include all patients, use No completed encounter when applicable, and sort by patient_id ascending.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

encounters

Column	Type	Description
encounter_idPK	INT	Unique encounter identifier
patient_id	INT	Patient associated with the encounter
encounter_date	DATE	Date of the encounter
status	VARCHAR(20)	Encounter status

diagnoses

Column	Type	Description
diagnosis_idPK	INT	Unique diagnosis identifier
encounter_id	INT	Encounter associated with the diagnosis
diagnosis_code	VARCHAR(20)	Diagnosis classification code

Your question is Database Design With SQL. Start with the requirements and the three 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