CREATE TABLE departments(  department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'Sales' FROM DUAL UNION ALL
SELECT 3, 'Marketing' FROM DUAL UNION ALL
SELECT 4, 'Finance' FROM DUAL;

CREATE TABLE employees (employee_id, first_name, last_name, hire_date, salary,  department_id) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 160000, 1 FROM DUAL UNION ALL
SELECT 2, 'Sandy', 'Herring', DATE '2011-08-04', 150200, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2019-03-05', 60700, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2007-11-11', 70125,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2004-09-17', 68525,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2020-05-10', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2008-12-10', 110000, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL UNION ALL
SELECT 10, 'Debra', 'Dante', DATE '2022-10-16', 102150,4 FROM DUAL UNION ALL
SELECT 11, 'Jerry', 'Torchiano', DATE '2022-10-30', 112660,4 FROM DUAL;

/* 3rd highest salary each dept */
select rnk, first_name, last_name, department_id, salary 
  from 
   ( 
    select first_name,  last_name, department_id, salary, 
    DENSE_RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk 
    from employees
   ) 
where rnk = 3

select rnk, first_name, last_name, department_id, department_name, salary 
  from                                           --- ^^ ADD ^^ ---
   ( 
    select first_name,  last_name, department_id, salary, 
    DENSE_RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk 
    from employees
   ) 
   join departments using (department_id)        -- <<<  ADD <<< -----
where rnk = 3

select rnk, first_name, last_name, d.department_id, department_name, salary 
  from                   --        ^^              --- ^^ ADD ^^ ---
   ( 
    select first_name,  last_name, department_id, salary, 
    DENSE_RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk 
    from employees
   ) q                                -- <<< ADD THE NAME q <<< -----
   join departments d on q.department_id = d.department_id        -- <<<  ADD <<< -----
where rnk = 3

select  3 rnk,
        m.first_name,
        m.last_name,
        m.department_id,
        d.department_name,
        m.salary
  from  employees
  match_recognize(
                  partition by department_id
                  order by salary desc
                  measures
                    match_number()   mn
                  all rows per match
                  pattern(
                          same_salary+
                         )
                  define same_salary as salary = first(salary) and match_number() <= 3
                 ) m,
        departments d
  where m.department_id = d.department_id
    and m.mn = 3
/

       RNK FIRST_ LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME     SALARY
---------- ------ --------- ------------- --------------- ----------
         3 Cheryl Ford                  1 IT                  110000
         3 Leslee Altman                1 IT                  110000
         3 Vicky  Palazzo               2 Sales                68525

SQL>