SQLize Online / PHPize Online  /  SQLtest Online

create table employees ( id integer primary key, name varchar(50), city varchar(50), department varchar(50), salary integer ); insert into employees (id, name, city, department, salary) values (24, 'Марина', 'Москва', 'it', 104), (21, 'Елена', 'Самара', 'it', 84), (22, 'Ксения', 'Москва', 'it', 90), (25, 'Иван', 'Москва', 'it', 120), (23, 'Леонид', 'Самара', 'it', 104), (11, 'Дарья', 'Самара', 'hr', 70), (12, 'Борис', 'Самара', 'hr', 78), (31, 'Вероника', 'Москва', 'sales', 96), (33, 'Анна', 'Москва', 'sales', 100), (32, 'Григорий', 'Самара', 'sales', 96); select id, name, department, salary, first_value(salary) over(partition by department order by salary rows between 1 preceding and unbounded following) as prev_salary, max(salary) over(partition by department) as max_salary, count(*) over(order by department groups between current row and current row) as cnt, count(*) over(order by department groups between current row and 1 following) as cnt1 from employees