Yet Another Top Employee Puzzle

Find the employee who remained the top paid employee (excluding the president) the longest period of time between 1980 and 1981

• Use a single SELECT statement only.
• President should be excluded from the analysis.
• Show the number of days the employee remained the top paid person as well as Start Date (hiredate) and End Date (the date when another top employee started)
• The End Date for the last top paid employee in the interval should be 31-DEC-1981.

Expected Result:

EMPNO ENAME JOB SAL Start Date End Date Days on Top
7566 JONES MANAGER 2975 02-APR-81 03-DEC-81 245

Solutions:

Solution #1. Using RANK to filter the top employee:

``````WITH x AS (
SELECT empno, ename, job, sal, hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE job!='PRESIDENT'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date, max_sal,
FROM x
WHERE sal=max_sal
), z AS (
SELECT y.*, LEAST(end_date, date'1981-12-31')-start_date days_on_top,
RANK()OVER(ORDER BY LEAST(end_date, date'1981-12-31')-start_date DESC) rk
FROM y
WHERE EXTRACT(YEAR FROM start_date) IN (1980, 1981)
)
SELECT empno,ename,job,sal, start_date "Start Date",
end_date "End Date", days_on_top	"Days on Top"
FROM z
WHERE rk=1``````

Solution #2. Using Subquery to filter the top employee:

``````WITH x AS (
SELECT empno, ename, job, sal, hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE job!='PRESIDENT'
AND hiredate>=date'1980-01-01'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date,
LEAST(date'1981-12-31',
FROM x
WHERE sal=max_sal
)
SELECT empno,ename,job,sal, start_date "Start Date",
end_date "End Date", end_date-start_date "Days on Top"
FROM y
WHERE end_date-start_date=(SELECT MAX(end_date-start_date) FROM y)``````

Solution #3. Using MODEL with RETURN UPDATED ROWS to filter the top employee:

``````WITH e AS (
SELECT empno, ename, sal, job, LEAST(hiredate, date'1981-12-31') hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE hiredate>=date'1980-01-01'
AND job!='PRESIDENT'
), x AS (
SELECT empno, ename, job, sal, hiredate,
date'1981-12-31')-hiredate diff,
date'1981-12-31') end_date
FROM e
WHERE sal=max_sal
)
SELECT empno, ename, job, sal, hiredate "Start Date",
end_date "End Date", diff "Days on Top"
FROM x
MODEL RETURN UPDATED ROWS
DIMENSION BY (empno, RANK()OVER(ORDER BY diff DESC) rk)
MEASURES(ename,job,sal, hiredate, end_date, diff, 0 dummy)
RULES(dummy[ANY, 1]=1)``````

The following query will only work as long as there is only 1 top paid employee who stayed on top the longest. In case if we had more than 1 it would only list one of those:

```WITH x AS (
SELECT empno, ename, job, sal, hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE job!='PRESIDENT'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date,
LEAST(date'1981-12-31',
LEAST(date'1981-12-31',
FROM x
WHERE sal=max_sal
ORDER BY days_top DESC NULLS LAST, hiredate
)
SELECT *
FROM y
WHERE ROWNUM=1```

You can execute the above SQL statements in Oracle Live SQL environment.