Puzzle of the Week #20: Historical top/bottom paid employee report

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.

Comment: Apparently, the first employee in each department automatically qualifies for both, the lowest and the highest paid employee at the time of hiring.

Expected Result:

POW20ER

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

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.

Advertisements

9 thoughts on “Puzzle of the Week #20: Historical top/bottom paid employee report

  1. Taroon Ray August 11, 2016 / 4:10 am

    SELECT ENAME, DEPTNO, JOB, HIREDATE, SAL,
    CASE WHEN SAL = MXSAL THE 1 ELSE NULL END MAXFLAG,
    CASE WHEN SAL = MNSAL THEN 1 ELSE NULL END MINFLAG
    FROM
    (

    SELECT ENAME, DEPTNO, JOB, HIREDATE,
    (
    SELECT MAX(SAL) FROM EMP WHERE EMP.HIREDATE = E.HIREDATE
    ) MXSAL,
    (
    SELECT MIN(SAL) FROM EMP WHERE EMP.HIREDATE = E.HIREDATE
    ) MNSAL
    FROM EMP E
    );

  2. Krishna Jamal August 11, 2016 / 10:29 am

    WITH t AS (
    SELECT Ename, Job, Deptno, HireDate, Sal,
    MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mn_Sal,
    MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mx_Sal
    FROM Emp ),
    t1 AS
    (SELECT Deptno, Sal, MIN(HireDate) HireDate
    FROM Emp
    GROUP BY Deptno, Sal)
    SELECT * FROM
    (SELECT Ename, Job, Deptno, HireDate, Sal,
    CASE WHEN Mn_Sal = Mx_Sal THEN 1
    WHEN Mn_Sal = (SELECT MIN(t1.Sal)
    FROM t1
    WHERE t1.Deptno = t.Deptno
    AND t1.HireDate = t.HireDate) THEN 1 END Min_Flag,
    CASE WHEN Mn_Sal = Mx_Sal THEN 1
    WHEN Mx_Sal = (SELECT MAX(t1.Sal)
    FROM t1
    WHERE t1.Deptno = t.Deptno
    AND t1.HireDate = t.HireDate) THEN 1 END Max_Flag
    FROM t)
    WHERE Min_Flag=1 OR Max_Flag=1;

    • Zahar Hilkevich August 11, 2016 / 10:47 am

      This may work though it is more complicated than it should be

  3. Krishna Jamal August 12, 2016 / 12:55 pm

    WITH t AS
    (SELECT Ename, Job, Deptno, HireDate, Sal,
    CASE
    WHEN ROW_NUMBER() OVER(PARTITION BY Deptno ORDER BY HireDate) = 1
    THEN 1
    WHEN MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
    AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
    THEN 1
    END Mn_Flag,
    CASE
    WHEN ROW_NUMBER() OVER(PARTITION BY Deptno ORDER BY HireDate) = 1
    THEN 1
    WHEN MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
    AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
    THEN 1
    END MX_Flag
    FROM Emp)
    SELECT * FROM t
    WHERE Mn_Flag = 1 OR Mx_Flag = 1;

    • Krishna Jamal August 14, 2016 / 8:54 am

      WITH t AS
      (SELECT Ename, Job, Deptno, HireDate, Sal,
      CASE
      WHEN MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
      AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
      THEN 1
      END Mn_Flag,
      CASE
      WHEN MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
      AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
      THEN 1
      END MX_Flag
      FROM Emp)
      SELECT * FROM t
      WHERE Mn_Flag = 1 OR Mx_Flag = 1;

  4. Krishna Jamal August 14, 2016 / 8:53 am

    WITH tbl AS
    (
    SELECT Ename, Job, Deptno, Hiredate, Sal,
    DECODE(Sal, MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate), 1) Mn_Flag,
    DECODE(Sal, MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate), 1) Mx_Flag
    FROM Emp
    WHERE HireDate IN (SELECT MIN(Hiredate) FROM Emp GROUP BY Sal)
    )
    SELECT * FROM tbl
    WHERE Mn_Flag=1 OR Mx_Flag=1;

  5. Krishna Jamal August 15, 2016 / 6:55 am

    WITH x AS
    (
    SELECT Ename, Job, Deptno, HireDate, Sal,
    MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mn,
    MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mx
    FROM Emp
    WHERE HireDate IN (SELECT MIN(HireDate) FROM Emp GROUP BY Sal)
    )
    SELECT Ename, Job, Deptno, HireDate, Sal, DECODE(Sal, Mn, 1) Min_Flag, DECODE(Sal, Mx, 1) Max_Flag
    FROM x
    WHERE Sal IN (Mn, Mx);

  6. Krishna Jamal August 15, 2016 / 7:14 am

    WITH x AS
    (
    SELECT Ename, Job, Deptno, HireDate, Sal,
    MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mn,
    MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mx
    FROM Emp
    WHERE HireDate IN (SELECT MIN(HireDate) FROM Emp GROUP BY Sal)
    )
    SELECT Ename, Job, Deptno, HireDate, Sal,
    NVL2(NULLIF(Sal,Mn), NULL, 1) Min_Flag,
    NVL2(NULLIF(Sal,Mx), NULL, 1) Max_Flag
    FROM x
    WHERE Sal IN (Mn, Mx);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s