Roche Interview Questions

A biomedical research lab is studying treatment effectiveness across different brain cancer categories. Each patient is associated with one cancer type and may undergo zero or more treatments. Write a query to perform an aggregated analysis across all cancer categories.

For each cancer type, compute:

1. Total number of patients
2. Percentage of patients who survived ≥ 12 months
   • Round to 2 decimal places
   • A “1-year survivor” is defined as survival_months >= 12
3. Average number of treatments per patient
   • Round to 2 decimal places
   • Include patients with zero treatments

Sort the results in descending order by survival rate.

Example 1

Input :

Table patients

Table treatments

Output :

Example 2

Input:

patients table

treatments table

Output:

Explanation:

Astrocytoma

• Total patients: 1
• Survivors: ¹⁄₁ → 100.00%
• Treatments: 0 total → 0.00 per patient

Meningioma

• Total patients: 2
• Survivors: patient 2 (24 months) → 50.00%
• Treatments: 1 total (patient 2) → 0.50 per patient

Glioblastoma

• Total patients: 2
• Survivors: patient 4 (14 months) → 50.00%
• Treatments: 3 total (2 for patient 1, 1 for patient 4) → 1.00 per patient