Healthcare: Appointments Per Patient

SQL coding challenge · Difficulty: easy · +50 XP

Problem

A hospital administrator wants to see how many appointments each patient has had. Include patients with zero appointments.

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 | appointment_count |
| --- | --- |
| Raj | 2 |
| Mohit | 2 |
| Prince | 1 |
| Rahul | 1 |

Return: patient_name, total_appointments
Patients with zero appointments must appear with count = 0
Sort by total_appointments descending