Walk me through the SQL logic you would use to identify the top three most frequent diagnoses in our pediatric outpatient clinic at Children’s Hospital of Philadelphia over the last year.
Use completed pediatric outpatient encounters and define frequency as the number of distinct encounters associated with each diagnosis. Exclude encounters outside the rolling one-year period.
Output
- One row per selected diagnosis with
diagnosis_name and encounter_count
- Return exactly three diagnoses, ordered by encounter count descending, then diagnosis name ascending to break ties
