Задача B. Аудит безопасности дорог Владивостока ≡

Автор:	А. Судаков		Ограничение времени:	1 сек
Входной файл:	test.sql		Ограничение памяти:	256 Мб
Выходной файл:	test.log

Условие

В рамках программы развития интеллектуальных транспортных систем г. Владивостока проводится аудит безопасности дорожной сети в зимний период. Аналитикам поставлена задача выявить опасные участки инфраструктуры и определить виновность водителей, попавших в инцидент.

Необходимо написать SQL-запрос, который сформирует выборку записей, удовлетворяющих одновременно двум критериям:

1. Доказанная вина: водитель официально признан виновником ДТП (is_guilty = 1).

2. Опасный участок: инцидент зафиксирован на локации, где за весь анализируемый период произошло строго более 2-х ДТП.

Итоговая таблица должна содержать следующие колонки:

driver_name — ФИО водителя;
accident_location — адрес, по которому произошло ДТП;
accident_date — дата происшествия (YYYY-MM-DD);
days_since_prev — количество дней, прошедших с предыдущего ДТП на том же месте. Для первого зафиксированного инцидента на участке выводится NULL. (NULL возвращается оконной функцией LAG для первой строки раздела.)

Для вычисления количества дней между датами использовать функцию JULIANDAY.

Результат отсортировать по адресу ДТП (по алфавиту), внутри одного адреса — по дате происшествия (по возрастанию).

CREATE TABLE drivers (
    driver_id INTEGER PRIMARY KEY,
    full_name TEXT
);

CREATE TABLE traffic_incidents (
    incident_id      INTEGER PRIMARY KEY,
    location_address TEXT,
    event_date       TEXT
);

CREATE TABLE incident_participants (
    incident_id INTEGER,
    driver_id   INTEGER,
    is_guilty   INTEGER,
    FOREIGN KEY (incident_id) REFERENCES traffic_incidents(incident_id),
    FOREIGN KEY (driver_id)   REFERENCES drivers(driver_id)
);

Схема БД в UML-нотации:

Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.

Формат входного файла

Пример тестовой БД.

Формат выходного файла

Для тестовой базы корректный запрос вернёт следующее: Edmund McMillen|Lugovaya Sq., interchange|2025-12-20| Anna Smirnova|Lugovaya Sq., interchange|2025-12-21|1 Matt Jolly|Lugovaya Sq., interchange|2026-01-25|35 Semyon Seryoznov|Lugovaya Sq., interchange|2026-02-14|20 Goro Majima|Verkhneportovaya St., 40|2025-12-10| Leon Scott Kennedy|Verkhneportovaya St., 40|2025-12-11|1 Ivan Ivanov|Verkhneportovaya St., 40|2026-01-15|35 Kazuma Kiryu|Verkhneportovaya St., 40|2026-02-05|21

Ограничения

Предполагается, что для работы с базой данных используется SQLite3.

---