Context

For this project, I analyzed the "Encounters" data for patients at a fictitious Massachusetts hospital. The encounters data was compiled in a CSV and included: the start and stop time, cost, copay, and reason for each visit.

The CSV included patient data for 3751 encounters from 1/1/2021 through 2/5/2022.

Questions

Using MySQL, I wanted to uncover trends in the data that the hospital could use to better serve its patients such as:

1. What was the average number of visits per patient? Were there any patients with unusually high numbers of visits?
2. What was the average time per visit? Were any visits noticeably longer or shorter than average?
3. What was the average cost per visit?
4. What were the most common encounter types?

Analysis

I. Number of Encounters per Patient

To get a general sense of the data, I first looked into the number of individual patients, and the number encounters for each patient.

a. Unique Patients

Using the COUNT and DISTINCT functions, I found that there were 650 unique patients in the dataset.

b. The Highest Number of Patient Visits

Using a preliminary search on count totals, I saw that 5 patients had over 100 encounters, and then the number waned significantly.

The most visits a patient had was 222 (56 more visits than the next closest patient).

I noted that it could be worth exploring the types of visits these patients had, and my guess would be that they were admitted for potentially very serious medical issues.

c. Typical Number of Patient Visits

Next, I wanted to see how the visit data was distributed. After doing a CTE and a query, I saw that the most common number of visits was 2, followed closely by 3 visits per patient, then 4, then 1 visit per patient.

I found this somewhat surprising, as I would have guessed that the most common number of visits would have been just 1 -- for an individuals routine yearly health check-up.

d. Average Number of Patient Visits

Finally, I wanted to see the average number of visits per patient. Using another CTE and the AVG function, I found that the average number of visits was 5.7.

This was higher than what I would have initially anticipated, and it could be thrown off by the patients with unusually high numbers of visits. A next point of inquiry could be to repeat this search while excluding either the tope 5 patients with the most numbers of visits, or just the patient with 222 visits.

II. Duration of Visits

Next, I felt it would be useful to explore the duration of visits. Using a CTE I queried the number of visits per length of visit. This showed that most visits were 15 minutes, followed by 1 hour-long visits, then 24 hour visits (1440 minutes).

It seemed logical that the most visits would be 15 minutes, as these might be routine office check-ups.

III. Cost Per Visit

a. Min, Max and Avg costs

To get a general sense of the costs of visits, I queried the MIN, MAX and AVG of the total_claim_cost.

The most expensive visit was $199, 655. This extremely high amount likely impacted the avg, and I would suspect that the typical visit is less than $2,631.

b. Distribution of costs

The min, max and avg costs were not particularly illuminating, so instead I looked into the most common costs by querying the number of visits per cost value. From here I could saw that the most common cost, by far, was $279.

c. $279 visits

Since the most common visit was $279, I decided to see what these visits were. It appeared there were two types of visits that were $279: "Administration of vaccine to produce active immunity (procedure)" and "Urgent care clinic (procedure."

d. Most expensive visits

Finally, I wanted to see the top 10 most expensive visits. The most expensive visit appeared to potentially be an outlier, with a cost of $199655. This was an intensive care unit visit that lasted almost one week. The rest of the top 10 were prenatal visits.

IV. Encounter Types

a. Most common encounter types

Finally, I wanted to see the most common types of visits. By far, "Administration of vaccine to produce active immunity" was the most common description. Considering that the time period of the visits was 2021 - 2022 this makes sense, as likely many patients came in for a Covid 19 vaccination.

Findings, Conclusion, and Next Steps

• The patients with the top 5 most patient visits had over 100 visits each. The highest number of visits was 222. The number of visits then decreased rapidly for all other patients. Accordingly, these 5 patients may be outliers and excluding them to get a better sense of the typical number of visits could be worthwhile.

• By far, the most common visit duration was 15 minutes. This tracks, as likely most visits are fairly routine check-ups. A next step could be to explore abnormally lengthy visits, or explore visits of 24 hours, as this was the third most common length of a visit, and it would be interesting to understand more about why.

• The cost of each visit fell between $0 - $199,655. Just one visit was $199,655 and this appears to be an outlier. The most common visit cost was $279, and most of these visits were for vaccine appointments. This was logical for the time period, as most were likely getting the covid vaccine.

• Aside from the visit costing $199,655 and a general examination that cost $65,000, then following 8 most expensive visits were for prenatal visits. A next step could be to delve further into why these visits are so expensive, and how these costs could be lowered.