Healthcare: Total Fees Collected Per Doctor

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The billing department wants to see how much each doctor has earned in total consultation fees.

Tables

Table: Patients

| patient_id | name | age | gender |
| --- | --- | --- | --- |
| 1 | Raj | 35 | Male |
| 2 | Mohit | 42 | Male |
| 3 | Prince | 29 | Male |
| 4 | Rahul | 31 | Male |

Table: Doctors

| doctor_id | name | specialization |
| --- | --- | --- |
| 101 | Dr. Mehta | Cardiology |
| 102 | Dr. Sharma | Neurology |
| 103 | Dr. Verma | Orthopedics |

Table: Appointments

| appt_id | patient_id | doctor_id | appt_date | reason | fees |
| --- | --- | --- | --- | --- | --- |
| 1001 | 1 | 101 | 2024-05-01 | Chest Pain | 800 |
| 1002 | 2 | 102 | 2024-05-02 | Headache | 700 |
| 1003 | 3 | 103 | 2024-05-03 | Knee Pain | 600 |
| 1004 | 1 | 103 | 2024-05-05 | Back Pain | 600 |
| 1005 | 4 | 101 | 2024-05-06 | Regular Checkup | 800 |
| 1006 | 2 | 101 | 2024-05-07 | Follow Up | 800 |

Expected Output

| name | total_fees |
| --- | --- |
| Dr. Mehta | 2400 |
| Dr. Verma | 1200 |
| Dr. Sharma | 700 |

Return: doctor_name, total_fees
Only include doctors with at least one appointment
Sort by total_fees descending