Условие

Бывает несколько мини-задач для Junior Data Analyst в фарме.

Задача 1 (SQL). Дано две таблицы:

clinical_trials(trial_id, drug_id, phase, start_date, end_date, status) — клинические исследования.
drugs(drug_id, name, type) — препараты.

Вывести список всех препаратов, по которым есть минимум 3 завершённых клинических исследования (status = 'completed') в фазе III, отсортированных по числу исследований по убыванию.

Задача 2. По таблице patients(patient_id, age, gender, disease_id, enrollment_date) найти возрастные распределения по полу для каждой disease_id в виде «средний возраст, медиана, IQR».

Задача 3. Описать как бы вы построили дашборд для отслеживания прогресса клинических испытаний.

Решение

Задача 1

SELECT d.drug_id, d.name, COUNT(*) AS completed_phase3
FROM clinical_trials ct
JOIN drugs d ON d.drug_id = ct.drug_id
WHERE ct.status = 'completed'
  AND ct.phase  = 'III'
GROUP BY d.drug_id, d.name
HAVING COUNT(*) >= 3
ORDER BY completed_phase3 DESC;

Задача 2

SELECT disease_id,
       gender,
       COUNT(*)                                          AS n_patients,
       AVG(age)                                          AS mean_age,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age)  AS median_age,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY age) AS q1_age,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY age) AS q3_age,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY age)
       - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY age) AS iqr
FROM patients
GROUP BY disease_id, gender
ORDER BY disease_id, gender;

Задача 3 — дашборд клинических испытаний

Block 1: Portfolio overview:

Активные / завершённые / провалившиеся trials (по фазам).
Distribution по drug type (small molecule, antibody, biologic).
Map по странам исследований.

Block 2: Recruitment:

Кол-во enrolled patients по неделям.
Target vs actual recruitment (бар-чарт).
Drop-out rate по фазам.

Block 3: Status / SLA:

Trials with end_date < today + 30 (приближаются дедлайны).
Trials в delay (start_date в прошлом, status = 'planned').
Adverse event rate.

Block 4: Outcomes:

Phase III success rate за последние 5 лет.
Time-to-market median (от phase I до approval).

Filters: phase, disease, country, year.

Проверка / интерпретация

Sanity: COUNT(DISTINCT drug_id) в результате task 1 ≤ COUNT(DISTINCT drug_id) в drugs.
Распределение возраста должно быть осмысленным (medians 30–70 для большинства болезней).

Подводные камни

HAVING vs WHERE: фильтр по status = 'completed' идёт в WHERE, фильтр по числу исследований — в HAVING.
PERCENTILE_CONT работает в Postgres / Snowflake; в MySQL — придётся через subquery.
disease_id × gender может иметь маленькую выборку — медиана/IQR недостоверны. Добавьте threshold n >= 30.
Active trials: status может быть NULL для незарегистрированных — фильтруйте.
End date == start date для trials, которые провалились моментально — выкл.

Эталонный ответ

Task 1 — JOIN + GROUP BY + HAVING. Task 2 — GROUP BY + percentile_cont для medianа и IQR. Task 3 — 4 блока дашборда: portfolio, recruitment, status/SLA, outcomes; filters по фазе, болезни, году.

БИОКАД — SQL и аналитические задачи Junior Data Analyst