Most Prescribed Drug in Each Department — SQL Problem
The pharmacy committee reviews each department's prescribing habits. A prescription belongs to the department of the doctor who wrote it.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #208
Problem statement
The pharmacy committee reviews each department's prescribing habits. A prescription belongs to the department of the doctor who wrote it.
For every department, return the drug (or drugs) prescribed most often, with columns department, drug_name and prescriptions. If several drugs tie for the top count, return all of them. Return the rows in any order.
Tables
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| name | varchar |
| department | varchar |
Primary key: doctor_id.
Table: Prescription
| Column | Type |
|---|---|
| rx_id | int |
| doctor_id | int |
| drug_name | varchar |
| rx_date | date |
Primary key: rx_id.
One row per drug prescribed; doctor_id always exists in Doctor.
Examples
Example 1
Doctor
| doctor_id | name | department |
|---|---|---|
| 1 | Kavya Menon | Cardiology |
| 2 | Rohan Gupta | Cardiology |
| 3 | Aisha Khan | General Medicine |
| 4 | Ira Bose | Paediatrics |
Prescription
| rx_id | doctor_id | drug_name | rx_date |
|---|---|---|---|
| 1 | 1 | Atorvastatin 10mg | 2025-02-01 |
| 2 | 2 | Atorvastatin 10mg | 2025-02-02 |
| 3 | 1 | Metformin 500mg | 2025-02-03 |
| 4 | 3 | Paracetamol 500mg | 2025-02-03 |
| 5 | 3 | Pantoprazole 40mg | 2025-02-04 |
| 6 | 3 | Paracetamol 500mg | 2025-02-05 |
| 7 | 3 | Pantoprazole 40mg | 2025-02-06 |
| 8 | 4 | ORS Sachet | 2025-02-06 |
| 9 | 2 | Atorvastatin 10mg | 2025-02-07 |
Output
| department | drug_name | prescriptions |
|---|---|---|
| Cardiology | Atorvastatin 10mg | 3 |
| General Medicine | Paracetamol 500mg | 2 |
| General Medicine | Pantoprazole 40mg | 2 |
| Paediatrics | ORS Sachet | 1 |
How to solve Most Prescribed Drug in Each Department
There are two levels of aggregation. The first is a plain count: join each prescription to its doctor to learn the department, then GROUP BY department, drug_name and COUNT(*). The second asks, inside each department, which counts are the largest.
A window function can work directly on the grouped rows: RANK() OVER (PARTITION BY department ORDER BY COUNT(*) DESC) numbers each department's drugs by count, and drugs with equal counts receive the same rank. Keeping rnk = 1 therefore returns every tied top drug, which is what the statement demands. ROW_NUMBER would pick one of the tied drugs arbitrarily, and DENSE_RANK = 1 would give the same answer as RANK here.
Without windows, put the counts in a CTE and keep the rows whose count equals the department's maximum — either with a correlated MAX subquery or by joining to a grouped table of maxima. Both read the counts twice; the window version sorts them once per department.
Reference solution (MySQL)
SELECT department, drug_name, prescriptions
FROM (
SELECT d.department, p.drug_name, COUNT(*) AS prescriptions,
RANK() OVER (PARTITION BY d.department ORDER BY COUNT(*) DESC) AS rnk
FROM Prescription p
JOIN Doctor d ON d.doctor_id = p.doctor_id
GROUP BY d.department, p.drug_name
) ranked
WHERE rnk = 1Another way
WITH counts AS (
SELECT d.department, p.drug_name, COUNT(*) AS prescriptions
FROM Prescription p JOIN Doctor d ON d.doctor_id = p.doctor_id
GROUP BY d.department, p.drug_name
)
SELECT department, drug_name, prescriptions FROM counts c
WHERE prescriptions = (SELECT MAX(prescriptions) FROM counts x WHERE x.department = c.department)Another way
WITH counts AS (
SELECT d.department, p.drug_name, COUNT(*) AS prescriptions
FROM Prescription p JOIN Doctor d ON d.doctor_id = p.doctor_id
GROUP BY d.department, p.drug_name
)
SELECT c.department, c.drug_name, c.prescriptions FROM counts c
JOIN (SELECT department, MAX(prescriptions) AS top FROM counts GROUP BY department) m
ON m.department = c.department AND m.top = c.prescriptions← Patients Readmitted Within 30 Days of Discharge · Cashless Claims Settled per Insurer per Month →