5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

4й в оракле можно так:

with Employee as  
(select  1 id, 'Joe' name, 70000 salary, 1 DepartmentId  from dual union all
select  2, 'Jim' , 90000, 1 from dual union all
select  3, 'Henry', 80000, 2 from dual union all
select  4, 'Sam' , 60000, 2 from dual union all
select  5, 'Max' , 90000, 1 from dual)

,Department  as
(select 1 id , 'IT' name from dual union all
select  2, 'Sales' from dual)

select 
dn
,en
,salary
 from
(select 
Employee.name en
,salary
,Department.name dn
,DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC NULLS LAST) m
from Employee, Department
where Employee.DepartmentId = Department.id
)
where m=1

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

5й в оракле бдут так =)

with t as
(select 1 id,'Abbot' student from dual union all
select 2,'Doris' from dual union all
select 3,'Emerson' from dual union all
select 4,'Green' from dual union all
select 5,'Jeames' from dual)

select 
    id,
    case when mod(id , 2) = 1 then nvl(lead (student,1) over (ORDER BY id),student) else nvl(lag (student,1) over (ORDER BY id),student) end
from t

Нуар-квест: раскрываем преступление по цепочке

+1 За картинки ставлю пять.

Верификация пользователей в Китае и социальный кредит

Любопытная статейка. Как говорится будущее за углом. Плюсую.