Interview Question Bank · LEFT JOIN with ON filters · MAX · NULLS FIRST
5. Patients overdue for a checkup
MediumHealthcareQuestion #137
Concept: LEFT JOIN with ON filters · MAX · NULLS FIRSTshow
To include people with no matching rows, LEFT JOIN and put the conditions on the joined table inside ON. In WHERE they would turn the NULL rows into failures and remove them. Filter the groups with HAVING on MAX(...) IS NULL OR the date test. Ascending sorts put NULLs last in Postgres, so add NULLS FIRST when they should lead.
Example (different data):
SELECT c.name, MAX(s.serviced_on) AS last_service FROM cars c LEFT JOIN services s ON s.car_id = c.id AND s.kind = 'annual' GROUP BY c.id, c.name HAVING MAX(s.serviced_on) IS NULL OR MAX(s.serviced_on) < DATE '2025-01-01' - 365 ORDER BY last_service NULLS FIRST, c.name;
Your task
Larkspur Health asks every patient to have an annual checkup. On 2025-06-30 the outreach team wants a call list of patients who are overdue: their last completed checkup was more than 365 days ago, or they've never had one.
Return name, last_checkup (date of the latest completed 'checkup' appointment, NULL if none) and days_since (2025-06-30 minus last_checkup) for every overdue patient. A checkup exactly 365 days ago is not overdue. Sort patients with no checkup first, then by last_checkup oldest first, then name.
Starting Postgres in your browser…
Checking your access…