# Solutions to 2018 Oracle SQL Puzzle of the Week #14

• Script Name Solutions to 2018 Oracle SQL Puzzle of the Week #14
• Visibility Unlisted - anyone with the share link can access
• Description 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.
• Area SQL General / SQL Query
• Contributor Zahar Hilkevich
• Created Wednesday April 25, 2018
• Statement 1
``````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``````
EMPNOENAMEJOBSALStart DateEnd DateDays on Top
7566JONESMANAGER297502-APR-8103-DEC-81245
• Statement 2
``````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', LEAD(hiredate) OVER(ORDER BY hiredate)) end_date
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)``````
EMPNOENAMEJOBSALStart DateEnd DateDays on Top
7566JONESMANAGER297502-APR-8103-DEC-81245
• Statement 3
``````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,
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)``````
EMPNOENAMEJOBSALStart DateEnd DateDays on Top
7566JONESMANAGER297502-APR-8103-DEC-81245
• Statement 4
``````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', LEAD(hiredate) OVER(ORDER BY hiredate)) end_date,
LEAST(date'1981-12-31', LEAD(hiredate) OVER(ORDER BY hiredate))-hiredate days_top
FROM x
WHERE sal=max_sal
ORDER BY days_top DESC NULLS LAST, hiredate
)
SELECT *
FROM y
WHERE ROWNUM=1``````
EMPNOENAMEJOBSALSTART_DATEEND_DATEDAYS_TOP
7566JONESMANAGER297502-APR-8103-DEC-81245