SQL Top Entities by Negative Outcome | Dataford Interview Questions

Your question is SQL Top Entities by Negative Outcome. Start with the requirements and the three 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

athenahealth uses encounter and outcome data to monitor operational performance across providers. Write a PostgreSQL query to identify the providers with the highest rate of negative encounter outcomes during the last completed calendar quarter.

Treat DENIED and DELAYED outcomes as negative. The rate is negative outcomes divided by all qualifying encounters, including encounters with no recorded outcome.

Requirements

Filter encounters to the last completed calendar quarter based on the current date.
Calculate total encounters, negative outcomes, and the negative outcome rate for each provider.
Return the top five providers, ordered by negative outcome rate descending. Break ties with total encounters descending, then provider_id ascending.
Exclude providers with no qualifying encounters.

Schema

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(100)	Provider display name

encounters

Column	Type	Description
encounter_idPK	INT	Unique encounter identifier
provider_id	INT	Provider associated with the encounter
encounter_date	DATE	Date the encounter occurred

encounter_outcomes

Column	Type	Description
encounter_idPK	INT	Encounter associated with the outcome
outcome_status	VARCHAR(20)	Recorded encounter outcome status