Most Prescribed Drug in Each Department

Medium
208.

Most Prescribed Drug in Each Department

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

ColumnType
doctor_idint
namevarchar
departmentvarchar

Primary key: doctor_id.

Table: Prescription

ColumnType
rx_idint
doctor_idint
drug_namevarchar
rx_datedate

Primary key: rx_id.

One row per drug prescribed; doctor_id always exists in Doctor.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Doctor

doctor_idnamedepartment
1Kavya MenonCardiology
2Rohan GuptaCardiology
3Aisha KhanGeneral Medicine
4Ira BosePaediatrics

Prescription

rx_iddoctor_iddrug_namerx_date
11Atorvastatin 10mg2025-02-01
22Atorvastatin 10mg2025-02-02
31Metformin 500mg2025-02-03
43Paracetamol 500mg2025-02-03
53Pantoprazole 40mg2025-02-04
63Paracetamol 500mg2025-02-05
73Pantoprazole 40mg2025-02-06
84ORS Sachet2025-02-06
92Atorvastatin 10mg2025-02-07

Output

departmentdrug_nameprescriptions
CardiologyAtorvastatin 10mg3
General MedicineParacetamol 500mg2
General MedicinePantoprazole 40mg2
PaediatricsORS Sachet1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
doctor_idint
namevarchar
departmentvarchar

Primary key: doctor_id.

Table: Prescription

ColumnType
rx_idint
doctor_idint
drug_namevarchar
rx_datedate

Primary key: rx_id.

One row per drug prescribed; doctor_id always exists in Doctor.

Examples

Example 1

Doctor

doctor_idnamedepartment
1Kavya MenonCardiology
2Rohan GuptaCardiology
3Aisha KhanGeneral Medicine
4Ira BosePaediatrics

Prescription

rx_iddoctor_iddrug_namerx_date
11Atorvastatin 10mg2025-02-01
22Atorvastatin 10mg2025-02-02
31Metformin 500mg2025-02-03
43Paracetamol 500mg2025-02-03
53Pantoprazole 40mg2025-02-04
63Paracetamol 500mg2025-02-05
73Pantoprazole 40mg2025-02-06
84ORS Sachet2025-02-06
92Atorvastatin 10mg2025-02-07

Output

departmentdrug_nameprescriptions
CardiologyAtorvastatin 10mg3
General MedicineParacetamol 500mg2
General MedicinePantoprazole 40mg2
PaediatricsORS Sachet1

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 = 1

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 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 →