Before the patterns,
check the data.

What does this sample show?

The project examined diagnosis patterns, patient demographics, environmental variables and lesion characteristics in a supplied course dataset. The first task was to establish a dependable joined dataset before comparing groups.

This was an analytical capstone using PostgreSQL 18 and pgAdmin. It did not diagnose patients or train a prediction model. The source database is not distributed in the public repository.

Validate. Join. Summarise.

1. Check the source tables. Compare patient, lesion and joined row counts; identify unmatched records and repeated identifiers.
2. Create a temporary analytical view. Join the tables on patient_id while preserving the original source tables.
3. Handle unavailable measurements. Treat zero-coded skin type and diameters as unavailable, pending confirmation of their original meaning.
4. Describe the sample. Use aggregations, CTEs, window functions and filtered counts to examine diagnoses and group proportions.
5. Prepare traceable metadata. Retain identifiers for audit, while specifying their exclusion from future model predictors.

SELECT p.patient_id, l.lesion_id,
       p.age, p.gender, l.diagnostic
FROM public.table1 AS p
JOIN public.table2 AS l
  ON l.patient_id = p.patient_id;

A clean join is only the beginning.

Repeated lesion IDs were not automatically deleted: the affected records belonged to different patients. The combined patient and lesion identifier was unique in the export, so it could support traceability while the original identifier convention remained unresolved.

The brief described 1,089 instances; the restored data and export contained 1,088. The reason for that difference was unconfirmed. All supplied records were retained.

Diagnosis patterns in 1,088 records.

ACK was the most frequent recorded diagnosis label. BCC, SCC and MEL together accounted for 346 records (31.8%). This combined category was used for descriptive comparisons, not as a newly validated diagnostic label.

Age groups showed different proportions.

The proportion increased across the age bands in this dataset. These figures describe the supplied records; they do not estimate population incidence or an individual’s risk.

Useful patterns. Clear limits.

Measurement completeness differed substantially by diagnosis. Usable diameter pairs ranged from 14 of 137 SEK records to all 17 MEL records. Comparisons of lesion size could therefore reflect which records were measured as well as differences between groups.

The analysis was descriptive and unadjusted. Sampling, recruitment and the original label provenance were not established. Observed differences by age, symptoms or exposure do not establish causation or diagnostic accuracy. The other diagnosis labels should not all be grouped as “benign”.

Preparing a metadata export also does not make it ready for clinical modelling. Identifiers, biopsy status and related target labels require careful handling to avoid leakage.

Improve the evidence before extending the analysis.

• Clarify the record-count discrepancy and lesion identifier convention with the provider.
• Confirm the meaning of zero-coded values and investigate diagnosis-dependent missingness.
• Verify label provenance before supervised modelling.
• Use adjusted analysis and suitable independent data to investigate associations further.

What this project demonstrates

SQL joins and reusable views, explicit denominators, data-quality checks and cautious interpretation of health data. The central lesson is that reliable analysis depends on understanding the records before interpreting their patterns.