Join Two Tables in SQL | Dataford Interview Questions

Your question is Join Two Tables in SQL. Start with the requirements and the two tables on the right.

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

Problem

HCA Healthcare needs a January 2025 operational summary showing completed patient encounters for every facility, including facilities with no qualifying encounters. Write a PostgreSQL query joining facilities to patient_encounters.

Requirements

Return every facility, even when it has no completed encounters during January 2025.
Count completed encounters and calculate the average charge amount for that period.
Sort results by facility name in ascending order.
Apply encounter filters without removing facilities that have no qualifying encounters.

Schema

facilities

Column	Type	Description
facility_idPK	INTEGER	Unique HCA Healthcare facility identifier
facility_name	VARCHAR(150)	Facility name
city	VARCHAR(80)	Facility city
state_code	CHAR(2)	Two-letter state code

patient_encounters

Column	Type	Description
encounter_idPK	INTEGER	Unique encounter identifier
facility_id	INTEGER	Associated facility identifier
encounter_date	DATE	Date of the encounter
status	VARCHAR(20)	Encounter status
encounter_type	VARCHAR(30)	Encounter classification
charge_amount	NUMERIC(10,2)	Recorded encounter charge