Разбор SQL-задачи с технического собеседования VK На собеседованиях в… — Next stop - Offer

Next stop - Offer@next_stopoffer · 1.2K subscribers Open in TelegramOpen

Разбор SQL-задачи с технического собеседования VK

На собеседованиях в бигтех очень любят задачи на оконные функции. Особенно те, где нужно посчитать топ-N внутри группы

Разберём одну из задач, которая встречалась мне на интервью во ВКонтакте

Формулировка задачи

Есть таблица logs:
platform — тип платформы
nav_screen — точка входа в видео
total_view_time — время просмотра с этого экрана

Задача

Для каждой платформы найти топ-5 экранов по времени просмотра

Если время у нескольких экранов одинаковое — вывести все

Решение через оконную функцию


with agg_data_rank as (
select
    platform,
    nav_screen,
    dense_rank() over(
    partition by platform  -- группируем по платформе
    order by sum(total_view_time) desc -- сортируем по суммарному времени просмотра
    ) as rn
from logs
group by 1,2
)

select
  *
from agg_data_rank
where rn <= 5

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

Подумайте как решить данную задачу без оконной функции


with agg_data as (
select
    platform,
    nav_screen,
    sum(total_view_time) as total_view_time_agg -- считаем total_time_view
from logs
group by 1,2
)

select
a1.platform,
a1.nav_screen,
count(a2.nav_screen) + 1 as rn -- считаем кол-во экранов у которых TTV выше чем у текущего()
from agg_data a1
left join agg_data a2 -- селф джоин смотрим, сколько есть экранов, у которых TTV выше, чем у текущей строки
    on a1.platform = a2.platform
    and a1.nav_screen != a2.nav_screen
    and a1.total_view_time_agg < a2.total_view_time_agg
group by 1,2
having count(a2.nav_screen) + 1 <= 5

Небольшой лайфхак:
Если хотите понять такие задачи по-настоящему, а не просто запомнить решение: возьмите мини-пример на 2 строки и посмотрите, как будет работать join, агрегация.

Посмотрите:

какие строки соединяются;

как работает агрегация

После этого такие задачи начинают читаться намного проще. Вообще на собеседованиях в бигтех такие задачи — классика

Если интересно, могу иногда выкладывать:

задачи с реальных интервью;

разборы решений;

и типичные ловушки, в которые попадают кандидаты