# 800 Phone Puzzle

For a given 800 phone number (like 1-800-123-4567) find all number-letter representations.

• Use a single SELECT statement only.
• Only last 4 digits of the phone number have to be replaced with letters.
• Exactly 1 letter (out of 4) must be vowel,  the rest – consonant
• The following table shows all possible mappings:
Digit Maps to
1 1
2 A, B, C
3 D, E, F
4 G, H, I
5 J, K, L
6 M, N, O
7 P, Q, R, S
8 T, U, V
9 W, X, Y, Z
0 0

# Solutions:

Essentially, all solutions below share the same idea of generating the letter based phone numbers. The differences are in a way the mapping CTE is created and a way to limit the number of vowels to 1.

### Solution #1. Compact form of creating the map CTE with recursive check for the vowels:

``````WITH map AS (
SELECT digit, letter, '4357' phone
FROM TABLE(sys.odcivarchar2list('00','11','2ABC','3DEF','4GHI',
'5JKL','6MNO','7PQRS','8TUV','9WXYZ')) t,
LATERAL(SELECT SUBSTR(t.column_value,1,1) digit,
SUBSTR(t.column_value,1+LEVEL,1) letter
FROM dual
CONNECT BY SUBSTR(t.column_value,1+LEVEL,1) IS NOT NULL) x
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1``````

### Solution #2. Using more efficient way of creating the map CTE :

```WITH x AS (
SELECT ROWNUM-1 digit,COLUMN_VALUE letters
FROM TABLE(sys.odcivarchar2list('0','1','ABC','DEF','GHI','JKL',
'MNO','PQRS','TUV','WXYZ'))
), map AS (
SELECT digit, SUBSTR(letters, level, 1) letter, '4357' phone
FROM x
CONNECT BY SUBSTR(letters, level, 1) IS NOT NULL
AND PRIOR digit = digit
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1```

### Solution #3. Much more efficient way of creating the map CTE and using Regular Expression to limit the vowels :

```WITH d AS (
SELECT LEVEL+1 n, CASE WHEN LEVEL+1 IN (7,9) THEN 4 ELSE 3 END cnt,
'4357' phone
FROM dual
CONNECT BY LEVEL<=8
), a AS (
SELECT CHR(ASCII('A')+LEVEL-1) letter, ROWNUM rn
FROM dual
CONNECT BY CHR(ASCII('A')+LEVEL-1)<='Z'
), x AS (
SELECT n,
1+NVL(SUM(cnt) OVER(ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING),0) c1,
SUM(cnt) OVER(ORDER BY n) c2,
phone
FROM d
), map AS (
SELECT n digit, letter, x.phone
FROM x JOIN a ON a.rn BETWEEN x.c1 AND x.c2
UNION
SELECT ROWNUM-1, TO_CHAR(ROWNUM-1), x.phone
FROM x
WHERE ROWNUM<=2
), res(str, lvl) AS (
SELECT letter, 1
FROM map
WHERE SUBSTR(map.phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1
FROM res JOIN map ON SUBSTR(map.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(map.phone)
AND REGEXP_COUNT(res.str || letter,'[AEIOU]')<=1
)
SELECT str phone
FROM res
WHERE lvl=4
AND REGEXP_COUNT(str,'[AEIOU]')=1```

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

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.

# 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.

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.

# Second Top Employee as of the Start of Employment

List all employees who were 2nd top paid in the entire company as of the time their employment started

• Use a single SELECT statement only.
• At the time of employment start the rank of the employee by salary should be 2.
• Show the top salary at the time when the employee started with the company.
• We assume that no employees have ever been terminated since day 1.

Expected Result:

ENAME JOB SAL HIREDATE MAX_SAL
WARD SALESMAN 1250 22-FEB-81 1600
BLAKE MANAGER 2850 01-MAY-81 2975
FORD ANALYST 3000 03-DEC-81 5000
SCOTT ANALYST 3000 19-APR-87 5000

# Solutions:

### Solution #1. Using LATERAL view, RANK and cumulative MAX analytic functions (Oracle 12g+):

``````SELECT e.ename, e.job, e.hiredate, e.sal, r.max_sal
FROM scott.emp e, LATERAL(SELECT a.empno,
RANK() OVER(ORDER BY a.sal DESC) rk,
MAX(a.sal) OVER() max_sal
FROM scott.emp a
WHERE a.hiredate<=e.hiredate) r
WHERE e.empno=r.empno
AND rk=2
ORDER BY e.hiredate``````

### Solution #2. Using CTE, cumulative MAX analytic function and a correlated subquery with COUNT to mimic the filter by RANK:

``````WITH x AS (
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp a
)
SELECT *
FROM x
WHERE 1=(SELECT COUNT(*)
FROM scott.emp
WHERE hiredate<=x.hiredate
AND sal>x.sal)
ORDER BY hiredate``````

### Solution #3. Using CTE, cumulative MAX analytic function and an in-line scalar subquery in SELECT to mimic the RANK:

``````WITH x AS (
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal,
(SELECT COUNT(*)+1
FROM scott.emp
WHERE sal>e.sal
AND hiredate<=e.hiredate) rk
FROM scott.emp e
)
SELECT ename, job, hiredate, sal, max_sal
FROM x
WHERE rk=2
ORDER BY hiredate``````

### Solution #4. Using self-join and Cartesian Product with aggregation:

``````SELECT a.ename, a.job, a.hiredate, a.sal, MAX(b.sal) max_sal
FROM scott.emp a JOIN scott.emp b ON b.hiredate<=a.hiredate
AND b.sal>a.sal
GROUP BY a.ename, a.job, a.hiredate, a.sal
HAVING COUNT(DISTINCT b.empno)=1
ORDER BY a.hiredate``````

### Solution #5. Using CTE and cumulative MAX analytic function (twice):

``````WITH x AS (
SELECT ename, job, hiredate, sal,
MAX(sal) OVER(ORDER BY hiredate) max_sal
FROM scott.emp
), y  AS (
SELECT ename, job, hiredate, sal, max_sal, MAX(sal) OVER(ORDER BY hiredate) max_sal2
FROM x
WHERE sal<max_sal
)
SELECT ename, job, hiredate, sal, max_sal
FROM y
WHERE sal=max_sal2
ORDER BY hiredate``````

### Solution #6. Using regular and recursive CTEs, ROWNUM, GREATEST, and CASE functions (no Analytic functions!):

``````WITH e AS (
SELECT ename, job, sal, hiredate
FROM scott.emp
ORDER BY hiredate
), x AS (
SELECT ename, job, sal, hiredate, ROWNUM rn
FROM e
), y(max_sal, sal2, rn) AS (
SELECT sal, 0, 1
FROM x
WHERE rn=1
UNION ALL
SELECT GREATEST(x.sal, y.max_sal) AS max_sal,
CASE WHEN x.sal>y.max_sal THEN y.max_sal
WHEN x.sal>y.sal2 AND x.sal<=y.max_sal THEN x.sal
ELSE y.sal2
END AS sal2,
x.rn
FROM x JOIN y ON x.rn=y.rn+1
)
SELECT x.ename, x.job, x.sal, x.hiredate, y.max_sal
FROM y JOIN x ON y.rn=x.rn AND y.sal2=x.sal``````

### Solution #7. Using CTE and MODEL clause to mimic Solution #6:

``````WITH x AS (
SELECT *
FROM scott.emp
MODEL
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY hiredate) rn)
MEASURES(ename, job, sal, hiredate, sal max_sal, 0 sal2)
RULES(
max_sal[rn>1] = GREATEST(max_sal[CV()-1], sal[CV()]),
sal2[rn>1] = CASE WHEN sal[CV()]> max_sal[CV()-1] THEN max_sal[CV()-1]
WHEN sal[CV()]> sal2[CV()-1]
AND sal[CV()]<=max_sal[CV()-1] THEN sal[CV()]
ELSE sal2[CV()-1]
END
)
)
SELECT ename, job, sal, hiredate, max_sal
FROM x
WHERE sal=sal2``````

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

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.

## Puzzle of the Week #20:

Produce the historical highest/lowest salary report that should comply with the following requirements:

• Use Single SELECT statement only
• Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
• Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
• If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
• The query should work in Oracle 11g.

### Expected Result:

#1. Using Common Table Expression (CTE) or Recursive WITH clause

```WITH y AS (
SELECT ename, job, deptno, hiredate, sal,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY hiredate) rn
FROM emp
), x (ename, job, deptno, hiredate, sal, min_sal, max_sal, min_flag, max_flag, rn) AS (
SELECT ename, job, deptno, hiredate, sal, sal, sal, 1, 1, 1
FROM y
WHERE rn=1
UNION ALL
SELECT y.ename, y.job, y.deptno, y.hiredate, y.sal,
LEAST(x.min_sal, y.sal), GREATEST(x.max_sal, y.sal),
CASE WHEN y.sal<x.min_sal THEN 1 END,
CASE WHEN y.sal>x.max_sal THEN 1 END, y.rn
FROM y JOIN x ON y.deptno=x.deptno AND y.rn=x.rn+1
)
SELECT ename, job, deptno, hiredate, sal, min_flag, max_flag
FROM x
WHERE 1 IN (min_flag, max_flag)
ORDER BY deptno, hiredate;```

#2. Using Cumulative Analytic Functions MIN, MAX, and ROW_NUMBER

```WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate) min_sal,
MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate) max_sal,
ROW_NUMBER()OVER(PARTITION BY deptno, sal ORDER BY hiredate) rn
FROM emp
)
SELECT ename, job, deptno, hiredate, sal,
DECODE(sal, min_sal, 1) min_flag,
DECODE(sal, max_sal, 1) max_flag
FROM x
WHERE sal IN (min_sal, max_sal)
AND rn=1;```

#3. Using Cumulative Analytic Functions MIN, MAX, and COUNT

```WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
CASE WHEN MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1
END min_flag,
CASE WHEN MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1
END max_flag
FROM emp
)
SELECT *
FROM x
WHERE 1 IN (min_flag, max_flag);```

### 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.