Отбор на стажировку в Яндекс в самом разгаре. Специально для наших подписчиков выкладываем разбор одной из задач из контеста на позицию аналитика

Условие
Артемий занимается регламентными работами на кластере, ему дали данные о последних проведенных работах в виде таблицы nodebase, вот несколько строк:

node  parent  diagnostics
7     5       2025-07-01
5     …       2025-06-30
1     …       2025-06-10
10    7       2024-12-08
11    7       2024-12-04
…     …       2025-07-06
2     11      2025-03-26
4     3       2024-11-11
6     3       2025-02-19
3     …       2025-02-17
8     3       2024-12-31

где node — id ноды, parent — id родительской ноды, diagnostics — дата диагностики ноды (если нода диагностировалась несколько раз за последние 5 лет, в таблице будет несколько строк с одинаковыми node).

Помогите Артемию подготовить отчет, в котором необходимо вывести номер ноды, ее положение в иерархии (начальная — root, внутренняя — inner или конечная — leaf) и время ее последней диагностики. Важно сначала вывести начальные ноды, у которых прошло наибольшее время с дня последней диагностики, затем ноды inner и в конце leaf. Необходимо написать запрос на sqlite, который поможет собрать нужную информацию в правильной сортировке.

Решение
Структура итоговой таблицы будет такой:
- node номер ноды
- position её роль в иерархии: root (нет родителя), inner (есть родитель и есть дети), leaf (есть родитель, но детей нет)
- last_date дата последней диагностики

1) В итоговой таблице нужно для каждой ноды вывести дату последней диагностики (нода могла диагностироваться несколько раз за последние 5 лет), поэтому сначала напишем cte, чтобы создать временную таблицу с нодой и её последней диагностикой

with last_date as (
select node, max(diagnostics) as last_date
from nodebase
group by node
),

2) Чтобы определить позицию в таблице для каждой ноды, нужно понять есть ли у неё родитель или нет. То есть можно узнать id её родителя или вывести NULL, если его нет. Сделаем также как в прошлом пункте, с помощью функции max(..).

parent_node as (
select node, max(parent) as parent
from nodebase
group by node
),

P. S. Можно объединить cte из 1 и 2 пункта, наше решение в таком виде написано для наглядности

3) Чтобы определить позицию в таблице, для каждой ноды нужно понять есть ли у неё дети или нет. Опять напишем cte, будем группировать по колонке parent, предварительно отбросив все строки где parent = NULL. Узнаем сколько детей у каждого родителя с помощью count(*) (либо можно сделать также как в прошлом пункте, с помощью функции max(node) — ведь нам нужно просто знать есть ли они или нет).

child_node as (
select parent as node, count(*) as child
from nodebase
where parent is not null
group by parent
),

4) Определяем положение каждой ноды
Соединим 2 таблицы из предыдущих 2 пунктов child_node и parent_node с помощью left join, а затем каждой ноде присвоим один из типов (root, inner, leaf)

position as (
select p.node, 
  case 
    when p.parent is null then 'root'
    when c.child is not null then 'inner'
    else 'leaf'
  end as position
from parent_node as p
left join child_node as c on c.node = p.node
)

5) Соединяем информацию о позиции и последней дате и сортируем сначала по позиции (для этого расставим приоритеты в сортировке root — 0, inner — 1, leaf — 2), а потом по самой старой диагностике

select p.node, p.position, dt.last_date
from position as p
left join last_date as dt on p.node = dt.node
order by 
  case p.position
    when 'root' then 0
    when 'inner' then 1
    else 2
  end,
dt.last_date asc

@ProdAnalysis