Healthcare analytics

Medication Adherence Analysis

medium3–4 hours3 datasets

Company: CVS Health
Job positions: Data AnalystBusiness AnalystData Scientist
Topics: Pharmacy analyticsDate logicData cleaningCohort metricsSQL

The scenario

CVS Health: Measure medication adherence from pharmacy refill records, and list the patients an outreach call could still help.

Adherence means whether patients keep taking a medication they were prescribed, and it is counted from refill records: a patient who refills late has days with nothing to take. Medicare's Star Ratings score a drug plan on three adherence measures, for diabetes, blood pressure and cholesterol, and the 2025 score closes on December 31. You're an analyst on CVS Health's pharmacy quality team.

Your task

Measure the proportion of days covered for every patient in each of the three adherence measures, report the rate for each, and list the patients worth calling.

Instructions

1Load the prescription refills and join them to the drug reference, so every refill carries the measure it counts toward. Clean the drug names until none fail to match.
2Decide which refills count toward a patient's days. Look at the status and the days of supply on each, and explain what you left out.
3Put a patient in a measure only if they have two or more counting refills of it during 2025, and keep a patient who switched drugs inside a measure in that measure.
4Measure them from their first counting refill to the earlier of December 31 and the end of their enrollment, then count the days they had medication in hand over that period.
5Refills overlap, because people collect the next one before the last runs out, and a day can only count once. Push each refill to start the day after the previous one ran out.
6Divide days covered by the length of the period to get the proportion of days covered, or PDC, and treat a patient as adherent at 0.80 or above. Save patient_adherence.csv, and the rate per measure as adherence_summary.csv.
7List the patients below the line who are close enough to still cross it in outreach_list.csv, then write a short summary of which measure has the lowest adherence rate, whether mail order patients do better than retail, and whether higher copays go with lower adherence.

Datasets

All files come in one download.

prescription_fills.csv

About 25,000 prescription refills during 2025, as the pharmacy system recorded them.

25,279 rows · 10 columns · 1.7 MB

Column	Type	Description
fill_id	integer	Unique ID of the refill.
patient_id	text	The patient the prescription belongs to.
filled_on	date	Date the prescription was filled.
drug_name	text	Name of the drug as the pharmacy recorded it.
days_supply	integer	Days of medication the refill provides.
quantity	integer	Units dispensed.
pharmacy_id	text	The pharmacy that filled it.
copay_usd	decimal	What the patient paid, in US dollars.
fill_channel	text	retail or mail_order.
fill_status	text	filled, or reversed where the claim was reversed and the prescription never collected.

Preview the first 5 rows

fill_id	patient_id	filled_on	drug_name	days_supply	quantity	pharmacy_id	copay_usd	fill_channel	fill_status
5200001	P430000	2025-01-24	SIMVASTATIN	30	30	RX1268	7.5	retail	filled
5200002	P430000	2025-04-05	Simvastatin	30	30	RX1198	2.6	retail	filled
5200003	P430000	2025-07-11	rosuvastatin	30	30	RX1246	39.96	retail	filled
5200004	P430000	2025-09-25	rosuvastatin	30	30	RX1435	36.26	retail	filled
5200005	P430000	2025-12-05	rosuvastatin	30	30	RX1307	40.17	retail	filled

patients.csv

The 2,200 patients in the plan, with the period each was enrolled for.

2,200 rows · 7 columns · 117 KB

Column	Type	Description
patient_id	text	Unique ID of the patient.
birth_year	integer	Year the patient was born.
sex	text	F or M.
state	text	US state the patient lives in.
plan_type	text	medicare_part_d or commercial.
enrolled_from	date	Date the patient's enrollment started.
enrolled_through	date	Date the patient's enrollment ends. Earlier than December 31 for patients who left during the year.

Preview the first 5 rows

patient_id	birth_year	sex	state	plan_type	enrolled_from	enrolled_through
P430000	1961	M	CA	commercial	2023-06-01	2025-12-31
P430001	1952	M	GA	medicare_part_d	2023-06-01	2025-12-31
P430002	1966	M	LA	commercial	2023-06-01	2025-12-31
P430003	1954	F	MA	medicare_part_d	2023-06-01	2025-12-31
P430004	1960	M	NY	medicare_part_d	2023-06-01	2025-12-31

drug_reference.csv

Which drug belongs to which class, and which of the three scored measures the class counts toward.

14 rows · 4 columns · 1 KB

Column	Type	Description
drug_name	text	Canonical drug name, in lowercase.
drug_class	text	The class the drug belongs to.
measure	text	The adherence measure the class counts toward: diabetes, hypertension_ras for blood pressure, or cholesterol_statins.
is_generic	text	Whether the drug is a generic: yes or no.

Preview the first 5 rows

drug_name	drug_class	measure	is_generic
metformin	biguanides	diabetes	yes
glipizide	sulfonylureas	diabetes	yes
glimepiride	sulfonylureas	diabetes	yes
sitagliptin	dpp4_inhibitors	diabetes	no
empagliflozin	sglt2_inhibitors	diabetes	no

Hints

Walk a patient's refills in date order, carrying the day their current supply runs out. Each refill then starts at the later of its own date and that day, which stops a day being counted twice.

cursor = period_start
for filled_on, supply in refills:
    start = max(filled_on, cursor)
    cursor = start + supply

A patient already above the line does not need a call, and one far below it cannot be brought back before the year ends. The useful list sits between the two.

Deliverable

A public GitHub repo with your analysis (a notebook, scripts or SQL), the three CSV outputs, and a README with your cleaning decisions, the rate per group and what you found.

When you're done, post your repo in the Solutions tab to share it with other learners.

What grading checks

Use this checklist to review your own work before you post and share it.

Submitted GitHub repo is public and reachable.
Repo contains at least one notebook, script or SQL file.
patient_adherence.csv has patient_id, measure, days_covered, pdc and adherent for the right patient and measure pairs, with nobody who has fewer than two counting refills.
The proportion of days covered matches the expected value within 0.02 for at least 95 percent of patient and measure pairs.
adherence_summary.csv reports measure, patients_in_measure, adherent_patients and adherence_rate within one point of expected, and outreach_list.csv holds only patients below the line with how many days they are short.
Overlapping and early refills are handled so a day is never counted twice, and the README says how.
Refills that should not count are excluded and drug names are cleaned before joining, and the outreach list is argued for rather than being everybody below the line.