7 SQL задач, которые задают на собеседовании в телеком-компании – Часть 2

Продолжаем разминать мозги и дорешиваем оставшиеся 3 из 7 задач на SQL. Первую часть разбора вы можете найти по ссылке.

1. Существует таблица tbl_terminal_device связи мобильного номера и устройства imei.

1select * from tbl_terminal_device

При каждой замене устройства абонентом фиксируется новая дата создания связки номера с этим устройством. На текущий момент актуальна одна последняя связка с последним устройством, т.е. последняя по дате создания created_dt.

По первым 8 символам в идентификаторе устройства imei можно определить модель устройства tac.

Необходимо отобрать всех абонентов, у которых последнее устройство Samsung, т.е. tac = 12345678.

Воспользуемся CTE с оконной функцией ROW_NUMBER() с партицированием по mob_num абонента и сортировкой по убыванию даты:

1WITH devices AS (2    SELECT3        mob_num,4        imei,5        created_dt,6        ROW_NUMBER() OVER (7            PARTITION BY mob_num8            ORDER BY created_dt DESC9        ) AS rn10    FROM11        tbl_terminal_device12)

Таким образом мы сможем впоследствии без труда извлекать последнюю дату и устройство для каждого абонента.

После, в основном запросе оставим только последнюю актуальную запись для каждого абонента (rn = 1) и воспользуемся функцией SUBSTRING, чтобы извлечь первые 8 символов IMEI и отфильтровать по ним:

1WITH devices AS (2    ...3)4SELECT5    mob_num,6    imei,7    created_dt8FROM9    devices10WHERE rn = 111    AND SUBSTRING(imei, 1, 8) = '12345678'

Готово: отобрали всех абонентов, у которых последнее устройство — Samsung.

2. Существует таблица EXAM_RESULTS с результатами последней завершенной сессии.

1select * from EXAM_RESULTS

Необходимо найти студентов, которые получили на всех зачетах + и 5 на всех экзаменах.

Для этой задачи явно требуется задать уточняющие вопросы интервьюеру. Мы же будем исходить из следующих условий:

• Актуальной оценкой по предмету считается наивысшая.
• У каждого студента может быть свое количество дисциплин.

Через CTE для каждого студента найдем общее количество дисциплин и сколько дисциплин было сдано на 5 или с зачетом:

1WITH students AS (2    SELECT3        student,4        count(DISTINCT subject) as subjects,5        count(CASE6            WHEN score = 5 or done = '+'7            THEN subject8            ELSE NULL9        END) AS subjects_passed10    GROUP BY student11)

Далее выберем тех студентов, у которых количество сданных на 5 и зачтенных предметов равно количеству доступных для студента дисциплин.

Итоговое решение выглядит так:

1WITH students AS (2    SELECT3        student,4        count(DISTINCT subject) as subjects,5        count(CASE6            WHEN score = 5 or done = '+'7            THEN subject8            ELSE NULL9        END) AS subjects_passed10    GROUP BY student11)12SELECT student13FROM students14WHERE subjects = subjects_passed

3. Существует таблица абонентов appn_history с историей изменений. Каждая запись по абоненту действует в пределах периода с valid_from_dt по valid_to_dt. По каждому абоненту, в момент времени, должна быть только одна действующая запись.

1select * from appn_history

Необходимо написать запрос, отбирающий записи, которые нарушают условие хранения данных в таблице.

Эту задачу можно решить огромным количеством способов с разной степенью оптимальности.

Предлагаю следующее решение:

Для каждого пользователя пронумеруем каждый период при помощи функции ROW_NUMBER():

1WITH sorted_history AS (2    SELECT3        appn,4        valid_from_dt,5        valid_to_dt,6        ROW_NUMBER() OVER (7            PARTITION BY appn8            ORDER BY valid_from_dt9        ) AS rn10    FROM appn_history11)

Для каждого абонента сравним каждую запись с предыдущей при помощи SELF JOIN и отфильтруем пересечения:

1WITH sorted_history AS (2    ...3)4SELECT5    t1.appn,6    t1.valid_from_dt,7    t1.valid_to_dt8FROM sorted_history t19INNER JOIN sorted_history t210ON t1.appn = t2.appn11AND t1.rn = t2.rn - 112AND t1.valid_to_dt >= t2.valid_from_dt13AND t1.valid_from_dt <= t2.valid_to_dt