SQL Join Query Writing | Dataford Interview Questions

Write SQL queries that involve joins.

Use the supplied relational tables to return one result for every patient record, including patients without encounters. Include only the requested output columns and sort the results as specified.

Output

One row per patient, including patients with no matching encounters.
Columns: patient_id, patient_name, encounter_count, total_charge, and last_encounter_date.
Sort by patient_name ascending, then patient_id ascending.

Schema

patient_records

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(120)	Patient's full name
birth_date	DATE	Patient's date of birth

encounters

Column	Type	Description
encounter_idPK	INT	Unique encounter identifier
patient_id	INT	Referenced patient identifier
encounter_date	DATE	Date of the encounter
charge	NUMERIC(10,2)	Charge associated with the encounter
encounter_status	VARCHAR(30)	Current encounter status

Your question is SQL Join Query Writing. 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