Electronic Health Records Dataset

• visits.patient_id →references patients.patient_idmany to one: each Visits row points to one Patients row
• diagnoses.visit_id →references visits.visit_idmany to one: each Diagnoses row points to one Visits row

1. List the top 5 most frequent diagnosis codes and their descriptions for patients in California.

   Join patients, visits, and diagnoses tables. Filter by state='CA'. Group by diagnosis_code and diagnosis_description, count occurrences, and order by count descending.

   tables: patients, visits, diagnoses

2. Calculate the average number of visits per patient for each gender.

   Join patients and visits tables. Group by gender and count visits per patient_id, then calculate the average of these counts.

   tables: patients, visits

3. Identify patients who had an 'Inpatient' visit and were diagnosed with a 'Pneumonia' (or similar code).

   Join patients, visits, and diagnoses tables. Filter visits.visit_type = 'Inpatient' and diagnosis_code LIKE '%J18%' (or similar pneumonia codes).

   tables: patients, visits, diagnoses

4. Create a dashboard showing the count of visits per month, broken down by visit type.

   Extract month from visit_date. Group by month and visit_type, counting the number of visits.

   tables: visits

Table names match the SQLite file and the SQL script.

SELECT d.diagnosis_code, d.diagnosis_description, COUNT(d.diagnosis_id) AS diagnosis_count
FROM diagnoses d
JOIN visits v ON d.visit_id = v.visit_id
JOIN patients p ON v.patient_id = p.patient_id
WHERE p.state = 'CA'
GROUP BY d.diagnosis_code, d.diagnosis_description
ORDER BY diagnosis_count DESC
LIMIT 20;

SELECT p.gender, AVG(visits_per_patient.visit_count) AS average_visits
FROM patients p
JOIN (
  SELECT patient_id, COUNT(visit_id) AS visit_count
  FROM visits
  GROUP BY patient_id
) AS visits_per_patient ON p.patient_id = visits_per_patient.patient_id
GROUP BY p.gender
LIMIT 20;

SELECT STRFTIME('%Y-%m', visit_date) AS visit_month, visit_type, COUNT(visit_id) AS visit_count
FROM visits
GROUP BY visit_month, visit_type
ORDER BY visit_month, visit_type
LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join tables based on foreign keys (e.g., patient_id, visit_id). Create a PivotTable from the joined data to analyze visit counts by month and type.

Power BI

Load tables into Power BI. Create relationships: patients[patient_id] -> visits[patient_id] and visits[visit_id] -> diagnoses[visit_id]. Create measures like 'Total Visits' = COUNT(visits[visit_id]) and 'Visits by Type' = CALCULATE(COUNT(visits[visit_id]), ALLEXCEPT(visits, visits[visit_type])).

SQL

Load the SQLite or SQL relational download. The tables are already structured with primary and foreign keys. Use JOIN clauses on patient_id and visit_id to link related data.

Python

Use pandas to load CSV or other formats. Join dataframes using pd.merge(). Perform aggregations and analysis using pandas functions.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file