Every query tells a story. Every join reveals a secret.
CASE FILE #612-HO-2025
The HealthOne Prescription Conspiracy
Detective, we've got a situation at HealthOne Medical Network. The board received an anonymous tip about suspicious prescription activity. Patients are reporting medications they never requested. Insurance claims are through the roof. Someone on the inside is gaming the system.
Your mission: Use SQL to investigate the database, follow the trail of evidence, and identify the culprit. The data doesn't lie — but you'll need the right queries to make it talk.
0 / 5 Clues
HealthOne Database Schema
PATIENTS
patient_id (PK)
name
address
phone
email
insurance_id (FK)
primary_doctor (FK)
policy_holder (FK)
DOCTORS
doctor_id (PK)
name
address
phone
specialization
PRESCRIPTIONS
prescription_id (PK)
patient_id (FK)
doctor_id (FK)
drug_id (FK)
date_prescribed
dosage
refillable (Y/N)
number_of_refills
DRUGS
drug_id (PK)
name
side_effects
benefits
OFFICE_VISITS
patient_id (PK, FK)
doctor_id (PK, FK)
date_of_visit (PK)
symptoms
diagnosis
blood_pressure
INSURANCES
insurance_company_id (PK)
insurance_company
phone
1
The Unusual Volume
"The anonymous tipster mentioned one doctor has been writing far more prescriptions than anyone else. We need to find out who's leading the pack. Count the prescriptions per doctor and show me the top prescriber."
🎯 Your Task:
Write a query to count prescriptions per doctor (show doctor_id and prescription count). Order by count descending to find the top prescriber.
Think GROUP BY on the doctor_id column, COUNT(*) for totals, and ORDER BY ... DESC to sort highest first.
2
The Phantom Visits
"Interesting... Dr. Marcus Chen (doctor_id = 3) is our top prescriber. But here's what's strange — some of these prescriptions don't have matching office visits. A real doctor sees a patient before prescribing. Let's find prescriptions with NO corresponding visit."
🎯 Your Task:
Find all prescriptions from doctor_id = 3 where there's no matching office visit (same patient_id and doctor_id). Use a LEFT JOIN and check for NULL.
LEFT JOIN prescriptions with office_visits ON patient_id and doctor_id. WHERE the visit's patient_id IS NULL means no matching visit exists.
3
The Repeat Offender
"Those phantom prescriptions are damning. But we need to dig deeper. Which specific drug is being prescribed most often in these suspicious cases? Follow the money — expensive specialty drugs are the usual target for fraud."
🎯 Your Task:
Join PRESCRIPTIONS with DRUGS to find the most commonly prescribed drug by doctor_id = 3. Show drug name and count, ordered by count descending.
JOIN prescriptions p with drugs d ON drug_id. WHERE doctor_id = 3. GROUP BY drug name. ORDER BY COUNT(*) DESC.
4
The Insurance Trail
"Oxycodone... a controlled substance with high street value. This is bigger than we thought. Now let's see which insurance company is footing the bill for most of Dr. Chen's patients. Fraudsters often target specific insurers with looser auditing."
🎯 Your Task:
Find which insurance company has the most patients whose primary_doctor is doctor_id = 3. Join PATIENTS with INSURANCES and count by insurance company.
JOIN patients p with insurances i ON insurance_id. WHERE primary_doctor = 3. GROUP BY insurance_company. ORDER BY COUNT(*) DESC.
5
The Final Connection
"We've got motive (expensive drugs), means (prescription access), and opportunity (phantom visits). One final query to seal the case: we need the full picture. Get me a report of Dr. Chen's prescriptions showing patient names, drug names, and whether a matching visit exists."
🎯 Your Task:
Create a comprehensive report: Join PRESCRIPTIONS, PATIENTS, DRUGS, and LEFT JOIN OFFICE_VISITS. Show patient name, drug name, date_prescribed, and a column indicating if a visit exists (visit date or 'NO VISIT'). Filter for doctor_id = 3.
Multiple JOINs: prescriptions → patients, prescriptions → drugs, LEFT JOIN office_visits. Use COALESCE or CASE to show 'NO VISIT' when visit date is NULL.
🎉 CASE CLOSED 🎉
🏆
Outstanding detective work! You've cracked the HealthOne Prescription Conspiracy.
📋 CASE SUMMARY
Suspect:Dr. Marcus Chen
Crime:Prescription Fraud
Primary Drug:Oxycodone
Targeted Insurer:Blue Cross
Method:Phantom Prescriptions (No Visit Records)
"The data never lies, Detective. You just need to know the right questions to ask."