Home » SQL & PL/SQL » SQL & PL/SQL » 25% lowest sal (Oracle, Oracle Database 11g Express Edition Release 11.2.0.2.0 ,Win 7 Pro)
25% lowest sal [message #639768] Wed, 15 July 2015 18:28 Go to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Hi All

In interview the following question had been asked, but I think I gave wrong answer , can somebody guide me?.

WITH datum AS
  ( SELECT 1 emp_id,1 dept_id,4999 sal FROM dual
  UNION ALL
  SELECT 2 ,2 , 4499 FROM dual
  UNION ALL
  SELECT 3 ,1 ,2299 FROM dual
  UNION ALL
  SELECT 4 ,2 ,5999 FROM dual
  )
SELECT *
FROM datum;



EMP_ID, DEPT_ID, SAL
1	1	5000
2	2	4500
3	1	2300
4	2	6000



Write a query that will provide the employee ID with lowest 25% salary in department.

I gave him the below answer but they told me that answer is not correct:

WITH datum AS
  ( SELECT 1 emp_id,1 dept_id,5000 sal FROM dual
  UNION ALL
  SELECT 2 ,2 , 4500 FROM dual
  UNION ALL
  SELECT 3 ,1 ,2300 FROM dual
  UNION ALL
  SELECT 4 ,2 ,6000 FROM dual
  )
SELECT *
FROM
  (SELECT emp_id,
    sal,
    dept_id ,
    SUM(sal) OVER (PARTITION BY dept_id) dept_wise_sum,
    (sal /SUM(sal) OVER (PARTITION BY dept_id)) *100 sal_per
  FROM datum
  )
WHERE 1      =1
AND sal_per <=25 ;

[Updated on: Wed, 15 July 2015 18:32]

Report message to a moderator

Re: 25% lowest sal [message #639773 is a reply to message #639768] Thu, 16 July 2015 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hint:
SQL> break on department_id dup skip 1
SQL> select employee_id, department_id, salary,
  2         ntile(4) over (partition by department_id order by salary) quartile
  3  from hr.employees
  4  where department_id in (50,80)
  5  order by department_id, salary
  6  /
EMPLOYEE_ID DEPARTMENT_ID     SALARY   QUARTILE
----------- ------------- ---------- ----------
        132            50       2100          1
        136            50       2200          1
        128            50       2200          1
        127            50       2400          1
        135            50       2400          1
        140            50       2500          1
        131            50       2500          1
        191            50       2500          1
        144            50       2500          1
        182            50       2500          1
        143            50       2600          1
        198            50       2600          1
        199            50       2600          2
        139            50       2700          2
        126            50       2700          2
        130            50       2800          2
        183            50       2800          2
        195            50       2800          2
        134            50       2900          2
        190            50       2900          2
        197            50       3000          2
        187            50       3000          2
        196            50       3100          2
        142            50       3100          3
        181            50       3100          3
        125            50       3200          3
        138            50       3200          3
        194            50       3200          3
        180            50       3200          3
        129            50       3300          3
        133            50       3300          3
        186            50       3400          3
        141            50       3500          3
        189            50       3600          3
        137            50       3600          4
        188            50       3800          4
        193            50       3900          4
        192            50       4000          4
        185            50       4100          4
        184            50       4200          4
        124            50       5800          4
        123            50       6500          4
        122            50       7900          4
        120            50       8000          4
        121            50       8200          4

        173            80       6100          1
        179            80       6200          1
        167            80       6200          1
        166            80       6400          1
        165            80       6800          1
        161            80       7000          1
        155            80       7000          1
        164            80       7200          1
        172            80       7300          1
        171            80       7400          2
        154            80       7500          2
        160            80       7500          2
        159            80       8000          2
        153            80       8000          2
        177            80       8400          2
        176            80       8600          2
        175            80       8800          2
        158            80       9000          2
        152            80       9000          3
        163            80       9500          3
        151            80       9500          3
        157            80       9500          3
        170            80       9600          3
        169            80      10000          3
        150            80      10000          3
        156            80      10000          3
        149            80      10500          4
        162            80      10500          4
        148            80      11000          4
        174            80      11000          4
        168            80      11500          4
        147            80      12000          4
        146            80      13500          4
        145            80      14000          4


Quote:
NTILE is an analytic function. It divides an ordered data set into a number of buckets [...] and assigns the appropriate bucket number to each row.


Note: To get the 25% lowest salaries in a department there should be at least 4 employees in the department otherwise it is quite pointless.

Re: 25% lowest sal [message #639776 is a reply to message #639768] Thu, 16 July 2015 01:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
orclz>
orclz> select ename,sal from emp order by sal fetch first 25 percent rows only;

ENAME             SAL
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1250

orclz>

Re: 25% lowest sal [message #639778 is a reply to message #639776] Thu, 16 July 2015 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Nice (but not in 11.2 Sad )
How do you do 25% first per department (I have no 12c to test)?

[Updated on: Thu, 16 July 2015 01:47]

Report message to a moderator

Re: 25% lowest sal [message #639779 is a reply to message #639778] Thu, 16 July 2015 01:39 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Oh, sorry Shumail, I missed the 11.2.x reference.

Michel, all th row limit clause does is use analytics:
orclz> select ename,sal from emp order by sal fetch first 25 percent rows only;

ENAME             SAL
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1250


Execution Plan
----------------------------------------------------------
Plan hash value: 4130734685

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   826 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |   826 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   140 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$
              _subquery$_002"."rowlimit_$$_total"*25/100))

orclz>
so I guess one would need a subquery per dept.
Re: 25% lowest sal [message #639781 is a reply to message #639779] Thu, 16 July 2015 02:37 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Really appreciate your replies and will get back to you after validation......
Re: 25% lowest sal [message #639824 is a reply to message #639781] Fri, 17 July 2015 04:39 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks to all.
Re: 25% lowest sal [message #639825 is a reply to message #639824] Fri, 17 July 2015 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So the solution for your question is?

Re: 25% lowest sal [message #639827 is a reply to message #639825] Fri, 17 July 2015 05:13 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
I think you already provided solution because if I use where clause in your provided query then I can get 25% lower salary , something like below:
Select employee_id
from
(
select employee_id, department_id, salary,
        ntile(4) over (partition by department_id order by salary) quartile
from hr.employees
where department_id in (50,80)
order by department_id, salary
)
where quartile=1;



Regards
Muzz
Re: 25% lowest sal [message #639829 is a reply to message #639827] Fri, 17 July 2015 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, I gave hints but the forum guide states that you have to post the solution when you found one to complete the topic. Smile
This will allow future readers to get an answer from a search and this also allows people finding another, maybe better, solution to post it.

Re: 25% lowest sal [message #639830 is a reply to message #639773] Fri, 17 July 2015 06:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not so sure NTILE is right function. OP asked for "lowest 25% salary in department". NTILE simply orders rows and split in N tiles. While "lowest 25% salary in department" is salary less or equal to min salary + (max salary - min salary) / 4. NTILE will probably give close enough or even correct results since department employee salary is relatively evenly distributed, otherwise:

SQL> with t as (
  2             select 1 val from dual union all
  3             select 2 val from dual union all
  4             select 3 val from dual union all
  5             select 4 val from dual union all
  6             select 10 val from dual union all
  7             select 15 val from dual union all
  8             select 20 val from dual
  9            )
 10  select  val
 11    from  (
 12           select  val,
 13                   ntile(4) over(order by val) tile
 14             from  t
 15          )
 16    where tile = 1
 17  /

       VAL
----------
         1
         2

SQL> with t as (
  2             select 1 val from dual union all
  3             select 2 val from dual union all
  4             select 3 val from dual union all
  5             select 4 val from dual union all
  6             select 10 val from dual union all
  7             select 15 val from dual union all
  8             select 20 val from dual
  9            )
 10  select  val
 11    from  (
 12           select  val,
 13                   min(val) over() + (max(val) over() - min(val) over()) / 4 to_val
 14             from  t
 15          )
 16    where val <= to_val
 17  /

       VAL
----------
         1
         2
         3
         4

SQL> 


SY.
Re: 25% lowest sal [message #639832 is a reply to message #639830] Fri, 17 July 2015 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Not so sure NTILE is right function. OP asked for "lowest 25% salary in department". NTILE simply orders rows and split in N tiles. While "lowest 25% salary in department" is salary less or equal to min salary + (max salary - min salary) / 4.


This is surely a language mistake from my part, NTILE indeed gives the 25% employees having the lowest salaries.
Your query gives the employees with a salary between the minimum one and a quarter of the difference between the maximum and the minimum up this later.
Could be, "give the employees with a salary in the 25% lowest ones among all the salaries". In this case, in your example, you will have only 1 (as 7/4=1, or 1 and 2 if you round).

Re: 25% lowest sal [message #639833 is a reply to message #639830] Fri, 17 July 2015 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Either could be right since the wording in the original post isn't really clear.
ntile will give the quarter of employees with the lowest salary.
Your approach will give all the employees whose salary is less than 25% of the total spread for the department.
I think the real point in all this is that the OP needed to clarify exactly what the interviewer wanted and think about the maths involved before thinking about the SQL.
OPs attempt - which was to get all the employees whose salary was less than 25% of the total salary for the dept - would result in getting all emps back for any large department (unless one of the employees is Bill Gates).
Re: 25% lowest sal [message #639843 is a reply to message #639832] Fri, 17 July 2015 11:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, NTILE doesn't give the 25% employees having the lowest salaries. Each tile in NTILE(4) will give corresponding 25% of employees in order of their salary. If, for example, all/most employees earn same salary employees with same salary can end up in different tiles.

SY.
Re: 25% lowest sal [message #639845 is a reply to message #639843] Fri, 17 July 2015 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, this is what I meant but badly expressed, I removed from my post, to avoid complicating and adding confusion, "even salaries can lead to weird results" (as I first warned about the number of rows in the sets).
Now I see I should not.

As I also suggested "lowest 25% salary" is not a clear specification.
In you example, if you add a row with 100, your query returns all employees but this one. I doubt this is what the interviewer wanted (and knowing interviewers I bet first 25 percent is what they think with such questions which does not mean we can't talk about them Smile ).

Re: 25% lowest sal [message #639846 is a reply to message #639845] Fri, 17 July 2015 17:05 Go to previous message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks for your replies..
Previous Topic: Wrong results with combination of Boolean, Left Join and To_Char
Next Topic: copy one table to another table
Goto Forum:
  


Current Time: Fri Mar 29 05:20:08 CDT 2024