How I Used SQL to Uncover Skin Cancer Risk Patterns in a Simulated Patient Dataset

A SQL analysis of skin cancer risk factors, from age and smoking to sanitation access, using a simulated patient dataset.

Dermalife Oncology and Research Institute manages thousands of patient records across multiple regions. Late-stage skin cancer diagnoses were rising, especially among elderly and underserved populations, and nobody could say why. I used SQL to digthrough patient demographics, lesion data, and environmental factors to find out.

Insights revealed who’s actually at highest risk, and it wasn’t only age driving it.

Key Analytical Questions

1. Identify high-risk patients using demographic, lifestyle and medical history data

Goal — To understand which demographic groups are most affected by skin cancer conditions.

2. Analyse lesion symptoms and diagnosis patterns to improve early detection

Goal — To analyse lesion characteristics and identify indicators of dangerous skin conditions

3. Assess how environmental and regional factors influence skin cancer prevalence and severity

Goal — To understand how environmental conditions influence diagnosis patterns

4. Generate data-driven recommendations to support preventive healthcare strategies and clinical decision-making

Get Omerenyo Obaze’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Goal — To evaluate how lifestyle habits contribute to skin cancer risks and lesion severity

About the Dataset

The dataset contains patient and diagnosis records

Table Used

Patient Table

Diagnosis Table

Data Cleaning

Checked for null values in the patient table.

SELECT *
FROM table1
WHERE patient_id IS NULL;

Data Analysis

Which age group has the highest number of skin cancer diagnoses?

SELECT 
 CASE
  WHEN age < 18 THEN 'children'
  WHEN age between 18 and 35 THEN 'young adult'
  WHEN age between 35 and 50 THEN 'Adult'
  WHEN age between 51 and 65 THEN 'Elder'
  ELSE 'Senior'
 END AS Age_Group,
COUNT (t2.diagnostic)as diagnosis_count
FROM table1 t1
join table2 t2
on t1.patient_id = t2.patient_id
GROUP BY Age_Group
ORDER BY diagnosis_count DESC
LIMIT 2;

Which body regions record the highest number of patients with malignant diagnoses?

SELECT region, COUNT(diagnostic) AS malignant_diagnoses
FROM table2
WHERE diagnostic IN ('BCC', 'SCC', 'MEL')
GROUP BY region
ORDER BY malignant_diagnoses DESC;

Which diagnosis category appears most frequently?

SELECT diagnostic, COUNT (region) AS frequency
FROM table2
GROUP BY diagnostic
ORDER BY frequency DESC;

Which symptoms are most commonly associated with lesions?

SELECT 'itch' AS symptom, COUNT (*) AS total_cases
FROM table2 WHERE itch = TRUE
UNION ALL
SELECT 'grew', COUNT (*) AS total_cases FROM table2
WHERE grew = TRUE
UNION ALL
SELECT 'hurt', COUNT (*) AS total_cases FROM table2
WHERE hurt = TRUE
UNION ALL
SELECT 'changed', COUNT(*) AS total_cases FROM table2
WHERE changed = TRUE
UNION ALL
SELECT 'bleed', COUNT(*) AS total_cases FROM table2
WHERE bleed = TRUE
UNION ALL
SELECT 'elevation', COUNT(*) AS total_cases
FROM table2
WHERE elevation = TRUE
ORDER BY total_cases DESC;

How many patients lack access to piped water?

SELECT has_piped_water, COUNT(patient_id) AS patients_without_water
FROM table1
WHERE has_piped_water = false
GROUP BY has_piped_water;

Is there a relationship between poor sanitation access and severe diagnosis outcomes?

SELECT
    CASE
        WHEN t1.has_sewage_system = FALSE
          OR t1.has_piped_water = FALSE
        THEN 'Poor_Sanitation'
        ELSE 'Good_Sanitation'
    END AS sanitation_status,
 CASE
        WHEN t2.diagnostic IN ('BCC', 'SCC', 'MEL')
        THEN 'Severe'
        ELSE 'Non-Severe'
    END AS diagnosis_severity,
 COUNT(*) AS number_of_lesions
 FROM table1 AS t1
JOIN table2 AS t2
    ON t1.patient_id = t2.patient_id
 GROUP BY sanitation_status, diagnosis_severity
 ORDER BY sanitation_status, diagnosis_severity;

Insights

• The Senior age group, those over 65, and male patients are most at risk of developing skin cancer.
• Smoking has a strong correlation with malignant skin cancers, including basal cell carcinoma, squamous cell carcinoma, and melanoma.
• Patients who lack access to proper sanitation, clean water and sewage disposal, show significant risk of both severe and non-severe diagnoses.
• Itching, elevation, and growth of the skin are the three most common symptoms associated with malignancy.

Recommendation

• Target lifestyle modification programmes addressing smoking and alcohol use, given their strong link to severe disease outcomes.
• Introduce a smoking cessation programme to reduce risk in this patient group.
• Increase awareness of the red-flag symptoms for skin cancer, such as itching, elevation, and changes in colour of a lesion.
• Reduce exposure to harmful pesticides, particularly among outdoor and farm workers, and encourage sunscreen use to lower UV exposure.