Top Providers by Region | Dataford Interview Questions

Covera Health wants to monitor provider utilization across regions using patient visit data. Write a PostgreSQL query that identifies the highest-volume providers for each region and calendar month.

Requirements

Join providers to patient_visits using provider_id.
Consider only visits with status = 'completed' and a non-null patient_id.
Calculate monthly patient volume as the number of distinct patients per provider.
Rank providers independently within each region and month, returning the top three providers. Break ties by the lower provider_id.

Schema

providers

Column	Type	Description
provider_idPK	INTEGER	Unique provider identifier
provider_name	VARCHAR(100)	Provider display name
region	VARCHAR(50)	Covera Health service region

patient_visits

Column	Type	Description
visit_idPK	INTEGER	Unique visit identifier
provider_id	INTEGER	Provider associated with the visit
patient_id	INTEGER	Patient associated with the visit
visit_date	DATE	Date on which the visit occurred
status	VARCHAR(20)	Visit completion status

Your question is Top Providers by Region. 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