postgres window function with rank

Question

I have some records which show users salary and department. I'd like to know ranking and its diff.

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2

I'd like to know the difference of each ranks salary

  depname  | empno | salary | rank | diff
-----------+-------+--------+------+------
 develop   |     8 |   6000 |    1 | 800
 develop   |    10 |   5200 |    2 | 700
 develop   |    11 |   5200 |    2 | 700
 develop   |     9 |   4500 |    4 | 300
 develop   |     7 |   4200 |    5 | 
 personnel |     2 |   3900 |    1 | 400
 personnel |     5 |   3500 |    2 | 
 sales     |     1 |   5000 |    1 | 200
 sales     |     4 |   4800 |    2 | 
 sales     |     3 |   4800 |    2 | 

teach me the query which returns above.

Answer 1

SELECT depname,empno, salary, rank() 
OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk, salary-lag(salary) 
OVER (partition BY depname ORDER BY  salary ) AS diff 
FROM empsalary ORDER BY depname, salary DESC;

Answer 2

Just use lag():

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC) as rnk,
       (salary - lag(salary) over (partition by depname order by salary desc)) as diff
FROM empsalary;

EDIT:

I noticed that your data has duplicates -- hence the rank(). This is a bit more troublesome, because Postgres does not support full the range keyword. Here is one method that does not use a JOIN:

SELECT depname, empno, salary, rnk,
       (salary - MIN(prev_salary) OVER (PARTITION BY depname, rnk)) as diff
FROM (SELECT depname, empno, salary,
             rank() OVER (PARTITION BY depname ORDER BY salary DESC) as rnk,
             lag(salary) over (partition by depname order by salary desc) as prev_salary
      FROM empsalary
     ) e;