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.
Requirements
- Include doctors with zero activity during the period, if they are assigned to a specialty.
- Count only events from 2025-01-17 through 2025-02-15, inclusive.
- Rank doctors independently within each specialty by activity count descending.
- Break ties deterministically using
doctor_id ascending and return no more than three doctors per specialty.
- Return specialty, doctor, activity count, and rank, ordered by specialty and rank.
