SQL Questions for Healthcare Data
Analysts & BI Engineers
1. Patients
Columns: patient_id, name, age, gender, admission_date, discharge_date
Question: Find the number of patients who have been admitted more than once in the last
year.
SELECT patient_id, COUNT(*) AS admission_count
FROM Patients
WHERE admission_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY patient_id
HAVING COUNT(*) > 1;
2. Appointments
Columns: appointment_id, patient_id, doctor_id, appointment_date, status
(Completed/Canceled)
Question: Identify the top 5 doctors with the highest number of completed appointments
in the last 6 months.
SELECT doctor_id, COUNT(*) AS total_appointments
FROM Appointments
WHERE status = 'Completed'
AND appointment_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY doctor_id
ORDER BY total_appointments DESC
LIMIT 5;
3. Medical_Records
Columns: record_id, patient_id, diagnosis_code, diagnosis_description, record_date
Question: Retrieve the most common diagnosis codes used in patient records in the last
year.
SELECT diagnosis_code, COUNT(*) AS occurrence
FROM Medical_Records
WHERE record_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY diagnosis_code
ORDER BY occurrence DESC
LIMIT 5;
4. Billing
Columns: billing_id, patient_id, department, amount, billing_date, payment_status
(Paid/Pending)
Question: Calculate the total revenue generated by each hospital department in the last
quarter.
SELECT department, SUM(amount) AS total_revenue
FROM Billing
WHERE billing_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY department
ORDER BY total_revenue DESC;
5. Prescriptions
Columns: prescription_id, patient_id, doctor_id, medication_name, dosage,
prescription_date, diagnosis_code
Question: Find the most prescribed medication for diabetic patients in the last year.
SELECT medication_name, COUNT(*) AS prescription_count
FROM Prescriptions
WHERE diagnosis_code = 'E11' -- Assuming 'E11' is the diagnosis code for diabetes
AND prescription_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY medication_name
ORDER BY prescription_count DESC
LIMIT 1;
6. Hospital_Admissions
Columns: admission_id, patient_id, admission_date, discharge_date, diagnosis_code,
department
Question: Determine the average length of stay for patients diagnosed with heart disease.
SELECT AVG(DATEDIFF(discharge_date, admission_date)) AS avg_length_of_stay
FROM Hospital_Admissions
WHERE diagnosis_code = 'I10'; -- Assuming 'I10' is the diagnosis code for heart
disease
7. Insurance_Claims
Columns: claim_id, patient_id, insurance_provider, claim_amount, claim_status
(Approved/Rejected), claim_date
Question: Identify the percentage of insurance claims that were rejected in the past 12
months.
SELECT
(COUNT(CASE WHEN claim_status = 'Rejected' THEN 1 END) * 100.0 / COUNT(*)) AS
rejection_rate
FROM Insurance_Claims
WHERE claim_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH);
8. Lab_Results
Columns: lab_id, patient_id, test_name, test_result, test_date, reference_range, age_group
Question: Retrieve the average cholesterol levels of patients grouped by age group.
SELECT age_group, AVG(test_result) AS avg_cholesterol
FROM Lab_Results
WHERE test_name = 'Cholesterol'
GROUP BY age_group
ORDER BY age_group;
9. Emergency_Visits
Columns: visit_id, patient_id, arrival_time, discharge_time, reason_for_visit, severity_level
Question: Find the busiest hour of the day for emergency room visits in the last 6 months.
SELECT HOUR(arrival_time) AS busy_hour, COUNT(*) AS visit_count
FROM Emergency_Visits
WHERE arrival_time >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY busy_hour
ORDER BY visit_count DESC
LIMIT 1;
10. Doctors
Columns: doctor_id, name, specialization, hospital_id, years_of_experience,
total_patients_treated
Question: List doctors who have treated more than 100 unique patients in the past year.
SELECT doctor_id, COUNT(DISTINCT patient_id) AS unique_patients
FROM Appointments
WHERE appointment_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY doctor_id
HAVING unique_patients > 100;
