25% lowest sal [message #639768] |
Wed, 15 July 2015 18:28 |
|
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 #639776 is a reply to message #639768] |
Thu, 16 July 2015 01:29 |
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 #639779 is a reply to message #639778] |
Thu, 16 July 2015 01:39 |
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 #639830 is a reply to message #639773] |
Fri, 17 July 2015 06:20 |
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 #639833 is a reply to message #639830] |
Fri, 17 July 2015 06:50 |
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 |
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.
|
|
|
|
|