Your question is SQL: Top Active Doctors by Specialty. Start with the requirements and the four 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.
Doximity wants to identify the most active doctors in each medical specialty based on activity recorded in the Doximity platform. Assume the evaluation date is 2025-02-15.
Write a PostgreSQL query to return the top three doctors in every specialty based on the number of activity events during the 30-day period ending on the evaluation date.
doctor_id ascending and return no more than three doctors per specialty.| Column | Type | Description |
|---|---|---|
| doctor_idPK | INT | Unique Doximity doctor identifier |
| full_name | VARCHAR(150) | Doctor's full name |
| Column | Type | Description |
|---|---|---|
| specialty_idPK | INT | Unique medical specialty identifier |
| specialty_name | VARCHAR(100) | Medical specialty name |
| Column | Type | Description |
|---|---|---|
| doctor_id | INT | References doctors.doctor_id |
| specialty_id | INT | References specialties.specialty_id |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique activity event identifier |
| doctor_id | INT | References doctors.doctor_id; null for unattributed activity |
| occurred_at | TIMESTAMP | Timestamp when the activity occurred |
| event_type | VARCHAR(50) | Type of Doximity activity |