SQL project.sql

---create tables

CREATE TABLE appointments
(
appointment_id int primary key,
patient_id int,
doctor_id int,
appointment_date date,
status varchar,
visit_reason varchar
)

CREATE TABLE billing
(
bill_id int primary key,
appointment_id int,
amount numeric,
insurance_covered numeric,
patient_paid numeric
)		

CREATE TABLE diagnoses
(
diagnosis_id int primary key,
appointment_id int,
diagnosis_code varchar,
diagnosis_description varchar
)


CREATE TABLE doctors
(
doctor_id int primary key,
doctor_name varchar,
specialty varchar,
clinic_location varchar
)


CREATE TABLE medications
(
med_id int primary key,
patient_id int,
medication_name varchar,
dosage varchar,
start_date date,
end_date date
)				


CREATE TABLE patients
(
patient_id int primary key,
first_name varchar,
last_name varchar,
gender varchar,
date_of_birth date,
city varchar,
insurance_provider varchar
)

select * from appointments
select * from diagnoses
select * from billing
select * from doctors
select * from medications
select * from patients


---1. List all patients who live in Seattle.
SELECT CONCAT(first_name, ' ',last_name) AS patient_name, city 
FROM
patients
WHERE city = 'Seattle'

---2. Find all medications where the dosage is greater than 50mg.
SELECT medication_name, dosage
FROM medications 
WHERE dosage >= '50mg'
ORDER BY dosage ASC

---3. Get all completed appointments in February 2025.
SELECT * FROM appointments
WHERE status = 'Completed'
AND appointment_date >= '2025-02-01'
AND appointment_date < '2025-03-01';

--4. Show each doctor and how many appointments they completed.
SELECT d.doctor_name, count(a.appointment_id) AS appointments_completed
FROM appointments a
JOIN doctors d
ON a.doctor_id=d.doctor_id
WHERE a.status = 'Completed'
GROUP BY d.doctor_name
ORDER BY appointments_completed DESC

---5. Find the most common diagnosis in the database.
SELECT diagnosis_code, COUNT(*) AS diagnosis_count
FROM diagnoses 
GROUP BY diagnosis_code
ORDER BY diagnosis_count DESC
LIMIT 1

---6. List the total billing amount per patient.
SELECT 
    p.patient_id,
    CONCAT(p.first_name, ' ',p.last_name) AS patient_name,
    SUM(b.amount) AS total_billing
	FROM 
    patients p
	JOIN appointments a 
	ON p.patient_id = a.appointment_id
	JOIN 
    billing b ON a.appointment_id = b.appointment_id
	GROUP BY 
    p.patient_id, patient_name
	ORDER BY 
    total_billing DESC;

--7. Which clinic location has the highest number of appointments
SELECT d.clinic_location, COUNT(a.appointment_id) AS appointments_count
FROM doctors d
JOIN appointments a
ON d.doctor_id = a.doctor_id
GROUP BY d.clinic_location
ORDER BY appointments_count DESC
LIMIT 1

-- 8. Identify patients who have more than one diagnosis in 2024.
SELECT CONCAT(p.first_name, ' ',p.last_name) AS patient_name, COUNT (*) AS diagnosis_count
FROM patients p
JOIN appointments a
ON p.patient_id=a.patient_id
WHERE
EXTRACT(YEAR FROM appointment_date)= 2024 
GROUP BY patient_name
HAVING COUNT(*) > 1
ORDER BY diagnosis_count DESC





-- 9.Rank doctors by total revenue generated

SELECT d.doctor_id, d.doctor_name, SUM(b.amount) AS total_revenue,
RANK() OVER (ORDER BY SUM(b.amount) DESC) AS revenue_rank
FROM doctors d
JOIN 
appointments a
ON d.doctor_id=a.doctor_id
JOIN 
billing b 
ON a.appointment_id = b.appointment_id
GROUP BY d.doctor_id, d.doctor_name
ORDER BY total_revenue DESC;

-- 10. For each patient, show their most recent appointment.
WITH latest AS (SELECT a.*,
Row_Number() OVER (PARTITION BY patient_id ORDER BY appointment_date DESC) 
AS RN FROM Appointments a)
SELECT * FROM latest
WHERE RN = 1

-- 11. Identify patients whose insurance covered less than 70% of their bill.
SELECT p.patient_id, p.first_name, p.last_name, b.amount, b.insurance_covered,
      (b.insurance_covered / b.amount) * 100 AS insurance_percentage
FROM Billing b
JOIN Appointments a 
ON b.appointment_id = a.appointment_id
JOIN Patients p 
ON a.patient_id = p.patient_id
WHERE (b.insurance_covered / b.amount) < 0.70;

-- 12. Identify all diabetic patients and list their last medication renewal date.
SELECT 
   CONCAT(p.first_name,' ', p.last_name) AS patient_name,
    m.patient_id, MAX(m.end_date) AS last_medication_renewal_date
FROM diagnoses d
JOIN appointments a 
ON d.appointment_id = a.appointment_id
JOIN patients p 
ON a.patient_id = p.patient_id
JOIN medications m
ON p.patient_id = m.patient_id
WHERE d.diagnosis_code ='E11'     
GROUP BY p.first_name, p.last_name, m.patient_id;

--- 13. Which doctor has the lowest no‑show rate? ----
SELECT d.doctor_name, count(a.appointment_id) AS No_show_appointments
FROM appointments a
JOIN doctors d
ON a.doctor_id=d.doctor_id
WHERE a.status = 'No-show'
GROUP BY d.doctor_name
ORDER BY No_show_appointments DESC
LIMIT 1;

--- 14. Which age group has the highest incidence of hypertension (I10)? ---
WITH patients_age AS (

SELECT
   CONCAT(p.first_name, ' ',p.last_name) AS patient_name,
	p.date_of_birth, d.diagnosis_code AS hypertention,
	EXTRACT(YEAR FROM AGE(current_date, p.date_of_birth)) AS age_in_years
    
FROM
    patients p 
	JOIN appointments a
	ON p.patient_id = a.patient_id
	JOIN diagnoses d
	ON d.appointment_id = a.appointment_id
	WHERE d.diagnosis_code = 'I10'
),
AgeGroups AS (
    SELECT *,
    CASE 
        WHEN age_in_years < 18 THEN '0–17'
        WHEN age_in_years BETWEEN 18 AND 34 THEN '18–34'
        WHEN age_in_years BETWEEN 35 AND 49 THEN '35–49'
        WHEN age_in_years BETWEEN 50 AND 64 THEN '50–64'
        ELSE '65+'
    END AS age_group
    FROM patients_age
)
SELECT 
    age_group,
    COUNT(*) hypertension_cases
FROM AgeGroups ag
GROUP BY age_group
ORDER BY hypertension_cases DESC;


-- 15. Which insurance provider covers the highest average amount?
SELECT p.insurance_provider, avg(b.amount) AS average_amount
FROM patients p
JOIN appointments a
ON p.patient_id=a.patient_id
JOIN billing b
ON a.appointment_id=b.appointment_id
GROUP BY p.insurance_provider
ORDER BY average_amount DESC
LIMIT 1

-- 16. Determine peak days of the week for appointments.
SELECT 
    TO_CHAR(appointment_date, 'Day') AS day_of_week,
    COUNT(*) AS total_appointments
FROM appointments
GROUP BY day_of_week
ORDER BY total_appointments DESC;

/*
17. From this data set, write out 3 other queries based on what was taught in class but the
queries should not be part of what has been asked already
 
i. Which doctor attends to the highest number of patients? */
 
SELECT d.doctor_name, COUNT(a.patient_id) AS total_patients
FROM appointments a
JOIN doctors d 
ON a.doctor_id = d.doctor_id
GROUP BY d.doctor_name
ORDER BY total_patients DESC;
 
-- ii. What is the total billing amount generated in 2024?
SELECT SUM(b.amount) AS Total_billing
FROM appointments a
JOIN billing b
ON a.appointment_id = b.appointment_id
WHERE EXTRACT(YEAR FROM a.appointment_date) = 2024;
 
 
-- iii. what is the average billing amount per clinic location
 
SELECT d.clinic_location, ROUND(AVG(b.amount),2) AS avg_billing
FROM billing b
JOIN appointments a
ON b.appointment_id = a.appointment_id
JOIN doctors d
ON a.doctor_id = d.doctor_id
GROUP BY d.clinic_location
ORDER BY avg_billing DESC;

-- 18. Explain these queries and outcomes
/*

i. Which doctor attends to the highest number of patients?
 
Query Explanation:
The query joins appointments and doctors using doctor_id.
For each doctor, it counts how many patients they attended to.
GROUP BY d.doctor_name groups the records per doctor.
ORDER BY total_patients DESC arranges the result so the doctor with the most patients comes first.
 
Outcome:
You get a list of doctors and the number of patients each one attended.

The first row in the result is the doctor with the highest number of patients.
 
ii. What is the total billing amount generated in 2024?
Query Explanation:
The query joins appointments and billing through appointment_id.
It filters only appointments that happened in 2024 using

EXTRACT(YEAR FROM a.appointment_date) = 2024.
It then sums up all billing amounts for those appointments.
 
Outcome:
You get one value: the total money the hospital made in 2024 from all billings.
 
iii. What is the average billing amount per clinic location?
 
Query Explanation:
 
The query joins billing, appointments, and doctors so it can connect the billing amount to the doctor and the doctor to the clinic location.
 
It calculates the average billing (AVG(b.amount)) per location.
 
GROUP BY d.clinic_location ensures each location has its own average.
 
ORDER BY avg_billing DESC ranks locations from highest average to lowest.
 
Outcome:
 
You get a list of clinic locations with their average billing amounts.

This helps you see which location brings in more revenue on average.
 */