SQL

Let's consider a healthcare database that consists of the following tables:

Patients (PatientId, FirstName, LastName, DateOfBirth, Gender)
Visits (VisitId, PatientId, VisitDate)
Doctors (DoctorId, FirstName, LastName, Specialization)
Treatments (TreatmentId, VisitId, DoctorId, Diagnosis, Treatment)

Now, you're asked to write a SQL query that finds the top 3 doctors in each specialization who have seen the most number of unique patients in 2023.

-- Create a Common Table Expression (CTE) to encapsulate the complex part of the query
WITH CTE AS (
SELECT
-- Select the first name, last name, and specialization of each doctor
D.FirstName,
D.LastName,
D.Specialization,

-- Count the unique number of patients each doctor has seen
COUNT(DISTINCT V.PatientId) AS UniquePatientsSeen,

-- Generate a ranking of doctors within each specialization based on the count of unique patients seen
ROW_NUMBER() OVER(PARTITION BY D.Specialization ORDER BY COUNT(DISTINCT V.PatientId) DESC) as Rank

-- From the Doctors table
FROM Doctors D

-- Join the Treatments table on the DoctorId
INNER JOIN Treatments T ON D.DoctorId = T.DoctorId

-- Join the Visits table on the VisitId
INNER JOIN Visits V ON T.VisitId = V.VisitId

-- Filter to include only visits from 2023
WHERE V.VisitDate BETWEEN '2023-01-01' AND '2023-12-31'

-- Group by doctor's first name, last name, and specialization
GROUP BY D.FirstName, D.LastName, D.Specialization
)
-- Then, we select from the CTE
SELECT
-- Select the doctor's first name, last name, specialization, and the number of unique patients seen
FirstName,
LastName,
Specialization,
UniquePatientsSeen

-- From the CTE
FROM CTE

-- Only include doctors who are in the top 3 in terms of unique patients seen within their specialization
WHERE Rank <= 3;

This SQL script demonstrates a complex healthcare database schema, utilizes advanced SQL techniques such as common table expressions, joins, and aggregate functions to answers real-world healthcare-related questions.