# 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,
LEAD(hiredate) OVER(ORDER BY hiredate) end_date
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',
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)

### 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,
NVL(LEAD(hiredate)OVER(ORDER BY hiredate),
date'1981-12-31')-hiredate diff,
NVL(LEAD(hiredate)OVER(ORDER BY hiredate),
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',
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

You can execute the above SQL statements in Oracle Live SQL environment.
My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Further Reading:

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

# Recent employment Puzzle

For each location, show 2 most recently hired employees

• Use a single SELECT statement only.
• ename1 and hiredate1 columns should correspond the latest hired employee while ename1 and hiredate1 columns – the previous one

Expected Result:

LOC ENAME1 HIREDATE1 ENAME2 HIREDATE2
NEW YORK MILLER 23-JAN-82 KING 17-NOV-81
CHICAGO JAMES 03-DEC-81 MARTIN 28-SEP-81
DALLAS ADAMS 23-MAY-87 SCOTT 19-APR-87

# Solutions:

### Solution #1. Using Self-Join and MAX functions

SELECT d.loc,
MAX(e1.ename) KEEP(DENSE_RANK FIRST ORDER BY e1.hiredate DESC) ename1,
MAX(e1.hiredate) hiredate1,
MAX(e2.ename) KEEP(DENSE_RANK FIRST ORDER BY e2.hiredate DESC) ename2,
MAX(e2.hiredate) hiredate2
FROM scott.emp e1 JOIN scott.emp e2 ON e1.deptno=e2.deptno
AND e1.hiredate>=e2.hiredate
AND e1.ROWID!=e2.ROWID
JOIN scott.dept d ON e1.deptno=d.deptno
GROUP BY d.loc;

### Solution #2. Using LISTAGG and REGEXP_SUBSTR functions

SELECT d.loc,
MAX(ename) KEEP(DENSE_RANK FIRST ORDER BY hiredate DESC) ename1,
MAX(hiredate) hiredate1,
REGEXP_SUBSTR(LISTAGG(ename, '|') WITHIN GROUP (ORDER BY hiredate DESC),
'[^|]+',1,2) ename2,
REGEXP_SUBSTR(LISTAGG(hiredate, '|') WITHIN GROUP (ORDER BY hiredate DESC),
'[^|]+',1,2) hiredate2
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
GROUP BY d.loc
ORDER BY 1;

### Solution #3. Using CTE, ROW_NUMBER, and Self-Join

WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT a.loc, a.ename ename1, a.hiredate hiredate1,
b.ename ename2, b.hiredate hiredate2
FROM x a JOIN x b ON a.loc=b.loc AND a.rn=1 AND b.rn=2;

### Solution #4. Using Pivot

WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, e1_ename AS ename1, e1_hdate AS hiredate1,
e2_ename AS ename2, e2_hdate AS hiredate2
FROM x
PIVOT (
MAX(ename) ename, MAX(hiredate) hdate FOR rn IN (1 AS e1, 2 AS e2)
)
ORDER BY 1;

### Solution #5. Simulating Pivot with MAX and DECODE functions

WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc,
MAX(DECODE(rn,1,ename)) ename1,
MAX(DECODE(rn,1,hiredate)) hiredate1,
MAX(DECODE(rn,2,ename)) ename2,
MAX(DECODE(rn,2,hiredate)) hiredate2
FROM x
GROUP BY loc
ORDER BY 1;

### Solution #6. Using CONNECT BY

WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, PRIOR ename ename1, PRIOR hiredate hiredate1,
ename ename2, hiredate hriedate2
FROM x
WHERE rn=2
START WITH rn=1
CONNECT BY loc=PRIOR loc
AND rn=PRIOR rn+1;

### Solution #7. Using LEAD and ROW_NUMBER Analytic functions

WITH x AS (
SELECT d.loc, e.ename ename1, e.hiredate hiredate1,
LEAD(e.ename,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) ename2,
LEAD(e.hiredate,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) hiredate2,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, ename1, hiredate1, ename2, hiredate2
FROM x
WHERE rn=1
ORDER BY 1;

### Solution #8. Using Model Clause:

SELECT loc, ename1, hiredate1, ename2, hiredate2
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
MODEL
RETURN UPDATED ROWS
PARTITION BY (d.loc)
DIMENSION BY (
ROW_NUMBER()OVER(PARTITION BY d.loc ORDER BY e.hiredate DESC) AS rn
)
MEASURES(
ename AS ename1, hiredate AS hiredate1,
ename AS ename2, hiredate AS hiredate2
)
RULES(
ename2[1]   =ename1[2],
hiredate2[1]=hiredate1[2]
)
ORDER BY 1;

You can execute the above SQL statements in Oracle Live SQL environment.
My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Namesake Puzzle

Show groups of employees having the same last name.

• Use a single SELECT statement only
• Use hr.employees table

# Solutions:

### Solution #1: Using Subquery with HAVING clause:

SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
WHERE last_name IN (SELECT last_name
FROM hr.employees
GROUP BY last_name
HAVING COUNT(*)>1)
ORDER BY 2,1

### Solution #2: Using Multi-Column Subquery with NO HAVING clause

SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
WHERE (last_name, 1) IN (SELECT last_name, SIGN(COUNT(*)-1)
FROM hr.employees
GROUP BY last_name)
ORDER BY 2,1

### Solution #3: Using Subquery with IN operator

SELECT first_name, last_name, department_id, employee_id
FROM hr.employees a
WHERE last_name IN (SELECT b.last_name
FROM hr.employees b
WHERE a.employee_id!=b.employee_id)
ORDER BY 2,1

### Solution #4: Using Self-Join with duplicate elimination in GROUP BY

SELECT a.first_name, a.last_name, a.department_id, a.employee_id
FROM hr.employees a JOIN hr.employees b ON a.last_name=b.last_name
AND a.employee_id!=b.employee_id
GROUP BY a.first_name, a.last_name, a.department_id, a.employee_id
ORDER BY 2,1

### Solution #5: Using a filter by COUNT analytic function with PARTITION BY

WITH x AS (
SELECT first_name, last_name, department_id, employee_id,
COUNT(*) OVER(PARTITION BY last_name) cnt
FROM hr.employees
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE cnt>1
ORDER BY 2,1

### Solution #6: Mimicking COUNT analytic function with MODEL clause

(credit to Naoto Katayama)

WITH x AS (
SELECT first_name, last_name, department_id, employee_id, cnt
FROM hr.employees
MODEL
RETURN UPDATED ROWS
DIMENSION BY (last_name, employee_id)
MEASURES(first_name, department_id, 0 AS cnt)
RULES (cnt[ANY, ANY]=COUNT(*)[CV(), ANY])
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE cnt>1
ORDER BY 2,1

### Solution #7: Filtering by LEAD and LAG analytic functions

WITH x AS (
SELECT first_name, last_name, department_id, employee_id,
LAG (last_name,1) OVER(ORDER BY last_name) lag_name,
LEAD(last_name,1) OVER(ORDER BY last_name) lead_name
FROM hr.employees
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE last_name IN (lag_name, lead_name)
ORDER BY 2,1

### Solution #8: Using MODEL clause with dummy measure for SIGN over analytic function expression

SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
MODEL
RETURN UPDATED ROWS
PARTITION BY (last_name)
DIMENSION BY (SIGN(COUNT(*) OVER(PARTITION BY last_name)-1) AS n,
employee_id)
MEASURES(first_name, department_id, 0 AS dummy)
RULES (dummy[1, ANY]=1)
ORDER BY 2,1

### Solution #9: Using UNPIVOT with DISTINCT option over CONNECT BY with PRIOR

WITH x AS (
SELECT first_name curr_first, last_name, department_id curr_dept, employee_id curr_id,
PRIOR first_name prior_first, PRIOR department_id prior_dept, PRIOR employee_id prior_id
FROM hr.employees
WHERE level=2
CONNECT BY last_name=PRIOR last_name AND employee_id>PRIOR employee_id
)
SELECT DISTINCT first_name, last_name, department_id, employee_id
FROM x
UNPIVOT(
(first_name, department_id, employee_id)  for dummy IN ((curr_first, curr_dept, curr_id),
(prior_first,prior_dept,prior_id))
)
ORDER BY 2,1;

You can execute the above SQL statements in Oracle Live SQL environment.
My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

### Expected Result:

Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

## Solutions

### #1: Using GROUP BY Over ROWNUM expression

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT MIN(empno) || ' - ' || MAX(empno) "Avail. Emp Numbers"
FROM y
GROUP BY empno-ROWNUM
ORDER BY empno-ROWNUM;

Avail. Emp Numbers
--------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

### #2: Using MATCH_RECOGNIZE (Oracle 12c and up;  credit to Zohar Elkayam)

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM y
MATCH_RECOGNIZE (
ORDER BY empno
MEASURES
A.empno firstemp,
LAST(empno) lastemp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B*)
DEFINE B AS empno = PREV(empno)+1
);

### #3: Using LEAD Analytic function (credit to Krishna Jamal)

WITH x AS
(
SELECT empno, LEAD(empno,1) OVER(ORDER BY empno) lead_empno
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x
WHERE empno+1!=lead_empno;

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Simulate LEAD and LAG functions using other analytic functions

Puzzle: Simulate LEAD and LAG functions using other analytic functions

Level: Intermediate

Solution:

Let’s say that we need to see every employee’s name and 2 more employees who were hired right before. The expected result may look like this:

ENAME      HIREDATE  EMPL1      EMPL2
---------- --------- ---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81 SMITH
WARD       22-FEB-81 ALLEN      SMITH
JONES      02-APR-81 WARD       ALLEN
BLAKE      01-MAY-81 JONES      WARD
CLARK      09-JUN-81 BLAKE      JONES
TURNER     08-SEP-81 CLARK      BLAKE
MARTIN     28-SEP-81 TURNER     CLARK
KING       17-NOV-81 MARTIN     TURNER
JAMES      03-DEC-81 KING       MARTIN
FORD       03-DEC-81 JAMES      KING
MILLER     23-JAN-82 FORD       JAMES
SCOTT      19-APR-87 MILLER     FORD
ADAMS      23-MAY-87 SCOTT      MILLER

It is a no-brainer task if we employ LAG function:

SELECT ename, hiredate,
LAG(ename,1) OVER(ORDER BY hiredate) empl1,
LAG(ename,2) OVER(ORDER BY hiredate) empl2
FROM emp;

One of the possible approaches is to use ROWS window attribute with MIN/MAX analytic functions:

SELECT ename, hiredate,
MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) empl1,
MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) empl2
FROM emp;

Common rule is:

LAG(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause
ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default)

and

LEAD(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause
ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)

This substitution becomes essential in other RDBMS where MIN/MAX analytic functions are supported while LEAD/LAG are not. Teradata is one of the examples.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## List all employees in 2 columns based on the salary ranking.

Problem: List all employee names and their respective salaries in 2 columns based in the salary ranking (from the highest to the lowest).

Expected Result:

ID LEFT_NAME      LEFT_SAL RIGHT_NAME    RIGHT_SAL
--- ------------ ---------- ------------ ----------
1 KING               5000 FORD               3000
2 SCOTT              3000 JONES              2975
3 BLAKE              2850 CLARK              2450
4 ALLEN              1600 TURNER             1500
5 MILLER             1300 WARD               1250
6 MARTIN             1250 ADAMS              1100
7 JAMES               950 SMITH               800

Problem Level: Intermediate/Advanced

Solution:
I have picked 5 best performing methods to solve this problem. The idea behind each method can be found in my book: “Oracle SQL Tricks and Workarounds”

Method/Workaround #1: Using Hierarchical Query (Level: Advanced)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) RN
FROM EMP
)
SELECT  rn/2 AS id, PRIOR ename left_name, PRIOR sal left_sal, ename right_name, sal right_sal
FROM X
WHERE MOD(level,2)=0
START WITH rn=1
CONNECT BY rn=1+PRIOR rn

Method/Workaround #2: Using Analytical Function (Level: Advanced)

WITH X AS (
SELECT ename left_name, sal left_sal,
LEAD(ename, 1) OVER(ORDER BY sal DESC) AS right_name,
LEAD(sal, 1) OVER(ORDER BY sal DESC) as right_sal,
ROW_NUMBER() OVER(ORDER BY sal DESC) rn
from emp
)
SELECT (rn+1)/2 AS ID, left_name, left_sal,
right_name, right_sal
FROM X
WHERE MOD(rn,2)=1
ORDER BY rn

Method/Workaround #3: Using PIVOT Clause (Level: Advanced)

SELECT *
FROM (SELECT CEIL(rn/2) AS ID, ename, sal, 2-MOD(rn,2) AS col_no
FROM (SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
FROM emp
)
)
PIVOT (MAX(ename) AS name,
MAX(sal)   AS sal
FOR (col_no) IN (1 AS left, 2 AS right)
)
ORDER BY 1

Method/Workaround #4: Using MAX function on concatenated column expression (Level: Advanced)

WITH X AS (
SELECT LPAD(sal, 5, '0') || ename as sname, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT CEIL(rn/2) ID, SUBSTR(MAX(SNAME), 6) left_name,  TO_NUMBER(SUBSTR(MAX(SNAME), 1, 5)) left_sal,
SUBSTR(MIN(SNAME), 6) right_name, TO_NUMBER(SUBSTR(MIN(SNAME), 1, 5)) right_sal
FROM X
GROUP BY CEIL(rn/2)
ORDER BY 1

Method/Workaround #5: Using Self-Join (Level: Intermediate)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT B.rn/2 AS ID, a.ename AS left_name, a.sal AS left_sal,
b.ename AS right_name, b.sal AS right_sal
FROM x a LEFT JOIN x b ON a.rn+1=b.rn
WHERE mod(a.rn,2)=1

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.