Brain Cancer Treatment Outcomes
Start Timer
0:00:00
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:
- Total number of patients
- Percentage of patients who survived ≥ 12 months
- Round to 2 decimal places
- A “1-year survivor” is defined as
survival_months >= 12
- 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
| Column | Type |
|---|---|
| patient_id | INTEGER |
| cancer_type | VARCHAR |
| survival_months | INTEGER |
Table treatments
| Column | Type |
|---|---|
| treatment_id | INTEGER |
| patient_id | INTEGER |
| treatment_name | VARCHAR |
| treatment_date | DATE |
Output :
| Column | Type |
|---|---|
| cancer_type | VARCHAR |
| total_patients | INTEGER |
| pct_one_year_survivors | DECIMAL |
| avg_treatments_per_patient | DECIMAL |
Example 2
Input:
patients table
| patient_id | cancer_type | survival_months |
|---|---|---|
| 1 | Glioblastoma | 10 |
| 2 | Meningioma | 24 |
| 3 | Astrocytoma | 15 |
| 4 | Glioblastoma | 14 |
| 5 | Meningioma | 7 |
treatments table
| treatment_id | patient_id | treatment_name | treatment_date |
|---|---|---|---|
| 201 | 1 | Radiation | 2024-03-01 |
| 202 | 1 | Chemotherapy | 2024-06-01 |
| 203 | 2 | Surgery | 2024-02-01 |
| 204 | 4 | Chemotherapy | 2024-08-01 |
Output:
| cancer_type | total_patients | pct_one_year_survivors | avg_treatments_per_patient |
|---|---|---|---|
| Astrocytoma | 1 | 100.00 | 0.00 |
| Meningioma | 2 | 50.00 | 0.50 |
| Glioblastoma | 2 | 50.00 | 1.00 |
Explanation:
Astrocytoma
- Total patients: 1
- Survivors: 1⁄1 → 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
.
.
.
.
.
.
.
.
.
Comments