🔹 1. Total Number of Patients
SELECT COUNT(*) AS total_patients
FROM patients;
What it does:
Counts all records in the patients table, giving you the total number of patients in the system.
Use case:
Baseline metric for patient population size. Useful for dashboards and population trend analysis.
Key concept:
- COUNT(*) returns the number of rows in a table — a standard aggregate function.
🔹 2. Patient Count by Insurance Payer
SELECT payer_name, COUNT(*) AS patient_count
FROM insurances
GROUP BY payer_name
ORDER BY patient_count DESC;
What it does:
Counts the number of patients associated with each payer (insurance company) in the insurances table.
Use case:
- Identifies which insurance companies have the largest patient base
- Helpful for billing strategy, payer performance analysis, and contract negotiations
Key concepts:
- GROUP BY clusters the data by payer_name
- COUNT(*) counts how many rows are associated with each payer
- ORDER BY DESC ranks the results by volume (most common payer at the top)
🔹 3. Number of Visits Per Patient
SELECT patient_id, first_name, last_name, COUNT(*) AS visit_count
FROM patients
GROUP BY patient_id;
What it does:
Counts how many visits each patient has made by grouping records in the patients table by patient.
Use case:
Identify frequent flyers, chronic patients, or gaps in care follow-up.
Intermediate concept:
- GROUP BY to calculate per-patient aggregates
- Good for bar charts or detailed patient summaries
🔹4. Appointments Within the Past 955 Days
SELECT appointment_id, patient_id, appointment_date
FROM appointments
WHERE appointment_date >= CURDATE() – INTERVAL 955 DAY;
What it does:
Lists all appointments scheduled or completed within the last 955 days.
Use case:
- Monitor recent patient activity
- Track reengagement or follow-up trends
- Useful for KPI dashboards and operational reports
Concepts used:
- CURDATE() gets today’s date (in MySQL)
- INTERVAL 90 DAY defines a rolling time window
- Simple WHERE clause filters based on the date field
🔹5. Most Frequently Claimed Billing Codes
SELECT description, COUNT(*) AS claim_count
FROM claims
GROUP BY description
ORDER BY claim_count DESC
LIMIT 5;
What it does:
Returns the top 5 most frequently claimed billing codes, grouped by their description.
Use case:
- Identify the most common procedures or services billed
- Useful for care pattern analysis, billing optimization, or service line reporting
Key Concepts:
- GROUP BY description: groups claims by procedure/service description
- COUNT(*): counts the frequency of each code
- ORDER BY … DESC LIMIT 5: sorts and limits results to the top 5
🔹 6. Average Length of Stay for Admitted Patients
SELECT
ROUND(AVG(DATEDIFF(discharge_date, admission_date)), 2) AS avg_length_of_stay
FROM admissions
WHERE discharge_date IS NOT NULL;
What it does:
Calculates the average number of days between a patient’s admission and discharge — only includes patients who have been discharged.
Use case:
- Key operational metric in hospitals and clinics
- Helps evaluate efficiency, patient flow, and resource usage
- Can be extended to group by reason or department in future queries
Concepts used:
- DATEDIFF: calculates the number of days between two dates
- AVG: gets the average across all patients
- ROUND: formats the result to two decimal places
- WHERE discharge_date IS NOT NULL: avoids counting still-admitted patients
🔹 7. Monthly Appointment Volume
SELECT
DATE_FORMAT(appointment_date, ‘%Y-%m’) AS month,
COUNT(*) AS appointment_count
FROM appointments
GROUP BY month
ORDER BY month;
What it does:
Counts how many appointments occurred each month — useful for understanding clinic workload over time.
Concepts:
- DATE_FORMAT() to group by month
- GROUP BY + COUNT(*) for aggregation
🔹8. Total Claim Amount by Claim Status
SELECT claim_status, ROUND(SUM(claim_amount), 2) AS total_claims
FROM claims
GROUP BY claim_status
ORDER BY total_claims DESC;
What it does:
Calculates the total dollar value of claims by their status — for example: Approved, Pending, Denied, or Submitted.
Use case:
- Financial visibility into claim lifecycle
- Helps identify bottlenecks in reimbursement
- Can help prioritize denied or pending claims for follow-up
Concepts:
- SUM(claim_amount) totals money per status
- GROUP BY to summarize results
- ORDER BY to highlight the most valuable buckets
🔹 9. Total Payments Received by Month
SELECT
DATE_FORMAT(payment_date, ‘%Y-%m’) AS month,
ROUND(SUM(amount_paid), 2) AS total_payments
FROM payments
GROUP BY month
ORDER BY month;
What it does:
Calculates monthly revenue from all payments received — perfect for cash flow monitoring.
Concepts:
- Time-based grouping with DATE_FORMAT()
- SUM() + ROUND() for totals
🔹 10. Patients Without Any Appointments
SELECT p.patient_id, p.patient_name
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;
What it does:
Finds patients who are in the system but have never scheduled an appointment.
Concepts:
- LEFT JOIN to include all patients
- WHERE … IS NULL to detect non-matches
- A classic anti-join pattern