Basic SQL Examples for Healthcare Analytics

By /

Study Notes – SELECT, WHERE, ORDER BY, GROUP BY, HAVING & JOINs

Core SQL skills that are used every day in healthcare analytics: pulling claims data, filtering high-risk patients, calculating costs, and joining patient and claims tables.

1. Basic Query Structure

SELECT column1, column2, ...
FROM table
WHERE condition
ORDER BY column ASC/DESC;

Healthcare Example

-- High-cost claims for patients 65 and older
SELECT 
    claim_id,
    patient_id,
    claim_date,
    claim_amount
FROM claims
WHERE claim_amount > 5000 
  AND patient_age >= 65
ORDER BY claim_amount DESC;

2. GROUP BY + Aggregations

SELECT 
    grouping_column,
    COUNT(*) as claim_count,
    SUM(claim_amount) AS TotalCost,
    AVG(claim_amount) AS AverageCost,
    MAX(claim_amount) AS HeighsestCost
FROM claims
GROUP BY grouping_column;

Example – Cost by Patient Age Group

SELECT 
    age_group,
    COUNT(*) AS num_claims,
    ROUND(SUM(claim_amount), 2) AS total_cost,
    ROUND(AVG(claim_amount), 2) AS avg_cost
FROM claims
GROUP BY age_group
ORDER BY total_cost DESC;

3. HAVING Clause

WHERE filters individual rows before grouping.
HAVING filters groups after grouping.

SELECT 
    provider_id,
    COUNT(*) as claim_count,
    SUM(claim_amount) AS total_cost
FROM claims
GROUP BY provider_id
HAVING COUNT(*) >= 50 
   AND SUM(claim_amount) > 100000
ORDER BY total_cost DESC;

4. JOINs – Most Important Skill in Healthcare Analytics

SELECT *
FROM patients AS p
JOIN claims AS c 
    ON p.patient_id = c.patient_id

Most useful JOIN types:

  • INNER JOIN → only matching records (most common)
  • LEFT JOIN → all patients, even those with no claims

Practical Healthcare Example

SELECT 
    p.patient_id,
    p.first_name,
    p.last_name,
    p.age,
    p.hcc_risk_score,
    COUNT(c.claim_id) AS num_claims,
    ROUND(SUM(c.claim_amount), 2) AS total_spend
FROM patients AS p
LEFT JOIN claims AS c
   ON p.patient_id = c.patient_id
WHERE p.age >= 65
GROUP BY p.patient_id, p.first_name, p.last_name, p.age, p.hcc_risk_score
HAVING COUNT(c.claim_id) >= 3
ORDER BY total_spend DESC;

Practice Exercises

  1. Write a query that shows all claims over $10,000 in 2025, sorted by amount (highest first).
  2. Show total claim count and total cost by provider specialty.
  3. Find patients who have more than 5 claims and total cost greater than $50,000.
  4. Join the patients and claims tables to show patient name, age, and total spending.

Key Takeaways

  • SELECT – choose what columns you want to see
  • WHERE – filter raw rows
  • GROUP BY + aggregations (COUNT, SUM, AVG) – for summaries
  • HAVING – filter after grouping
  • JOINs – combine tables (this is where real insights happen)
  • ORDER BY – make results easy to read

Personal study notes while preparing for my SQL exam.

Scroll to Top