SQL Aggregates in One Row | Dataford Interview Questions

Your question is SQL Aggregates in One Row. 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

Exusia Care needs a compact patient activity summary for its reporting dashboard. Write a PostgreSQL query that returns the number of distinct male and female patients who had at least one completed encounter during 2025 in the same row.

Requirements

Join patients to encounters using patient_id.
Include only encounters with status completed and an encounter date from 2025-01-01 through 2025-12-31.
Count each qualifying patient once, even if the patient had multiple completed encounters.
Return one row with the columns male_patient_count and female_patient_count. Do not include other genders in either count.

Schema

patients

Column	Type	Description
patient_idPK	INTEGER	Unique patient identifier
patient_name	VARCHAR(100)	Patient's name
gender	VARCHAR(20)	Recorded gender

encounters

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