WIPRO SQL Interview Question - FIRST_VALUE( ) Function
Vložit
- čas přidán 7. 09. 2024
- One of the SQL questions recently asked in WIPRO interview.
We need to Find department wise minimum salary empname and maximum salary empname .
To solve this, we will write a query by using FIRST_VALUE() windows function for first approach and Rownumber() function for second approach.
Create table syntax
CREATE TABLE emps_tbl (emp_name VARCHAR(50), dept_id INT, salary INT);
INSERT INTO emps_tbl VALUES ('Siva', 1, 30000), ('Ravi', 2, 40000), ('Prasad', 1, 50000), ('Sai', 2, 20000), ('Anna', 2, 10000);
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
info@cloudchallengers.com
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers
with t as
(
select dept_id, first_value(emp_name)over(partition by dept_id order by salary) as min_sal_empname,
last_value(emp_name)over(partition by dept_id order by salary rows between unbounded preceding and unbounded following) as max_sal_empname
from emps_wipro
)
select distinct dept_id, min_sal_empname,max_sal_empname from t;
with cte as (
select *, first_value(salary) over(partition by dept_id order by salary) min , first_value(salary) over(partition by dept_id order by salary desc) max from emps)
select dept_id,max(case when salary = min then emp_name end) as min_salary_emp_name, max(case when salary = max then emp_name end)
as max_salary_emp_name from cte group by 1;
with cte as
(
select *,DENSE_RANK()over(partition by dept_id order by salary desc) as maxsal,
DENSE_RANK()over(partition by dept_id order by salary ) as minsal
from emps_min_max
) select c.dept_id,c1.emp_name as min_sal_emp ,c.emp_name as max_sal_emp
from (select * from cte where maxsal = 1) c
join (select * from cte where minsal = 1) c1
on c.dept_id = c1.dept_id
@Hope-xb5jv, Thanks for sharing different approach.
SQL code for this question:
select distinct dept_id,first_value(emp_name) over (partition by dept_id order by salary) min_salary,
first_value(emp_name) over (partition by dept_id order by salary desc) max_salary
from emps_tbl
with cte as (
select *,first_value(salary) over(partition by dept_id order by salary rows between unbounded preceding and unbounded following) as min_salary,
last_value(salary) over(partition by dept_id order by salary rows between unbounded preceding and unbounded following) as max_salary
from emps_tbl)
select c.dept_id,
max(case when c.salary = min_salary then c.emp_name end) as Min_Salary_EmpName,
max(case when c.salary = max_salary then c.emp_name end) as Max_Salary_EmpName
from cte c
group by c.dept_id;
-----1 approach using first_value analytical function
select distinct dept_id , first_value(emp_name) over(partition by dept_id order by salary asc) min_sal_emp
first_value(emp_name) over(partition by dept_id order by salary desc) max_sal_sal from emps_tbl
group by dept_id,min_Sal_emp,max_sal_emp;
---2 Approach using row_number analytical function
with cte as
(select e.* , row_number() over(partition by dept_id order by salary asc ) rn_min_sal ,
row_number () over(partition by dept_id order by salary desc) rn_max_sal from emps_tbl e)
select dept_id , max(case when rn_min_sal =1 then emp_name end) as min_sal_emp,
max(case when rn_max_sal =1 then emp_name end ) as max_sal_emp from cte group by dept_id;
Select distinct(Dept_id),first_value(Emp_Name) over (Partition by dept_id order by salary desc) as Max_sal_Emp,
first_value(Emp_Name) over (Partition by dept_id order by Salary asc) as Min_Sal_Emp from emps_TBL ;
We can use group by clause in first question. this is very easy and simple to understand
@kingvirat9912, l would be appreciated if you can share your query here.
@@CloudChallengers SELECT
dept_id,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM
salary_table
GROUP BY
department_id;
Nice explanation bro 👍 👌 👏
@sravankumar1767, Thanks for the encouragement.
my solution please let me know this is fine or not
with cte as(
select *, dense_rank() over(partition by dept_id order by salary) min ,dense_rank() over(partition by dept_id order by salary desc) max from emps)
select dept_id,max(case when min = 1 then emp_name end) as min_salary,
max(case when max=1 then emp_name end) as max_salary from cte group by 1;
WITH CTE AS(SELECT *,
row_NUMBER()OVER(partition by dept_id ORDER BY SALARY) AS RW
FROM SALARY),V1 AS(
SELECT *,
CASE WHEN RW = min(RW) OVER(partition by dept_id) THEN 1
WHEN RW = max(RW) OVER(partition by dept_id) THEN 0 END AS QUALIFY
FROM CTE)
SELECT dept_id,
MIN(CASE WHEN QUALIFY = 1 THEN emp_name END) AS MIN_SAL_EMP,
MIN(CASE WHEN QUALIFY = 0 THEN emp_name END) AS MAX_SAL_EMP
FROM V1
group by 1;
Good video❤
@shahzan525, Thanks for the feedback
please bring some medium level sql questions which require brain storming
Is this a easy one ??
@@MubarakAli-qs9qq I think you starting your sql journey.
@@chandanpatra1053 yes its been a week , u ??
Not able to use first_value function in mysql
@thrupthilc3060, first_value function should work in mysql. Check your query once
0:24 may u share the error i mail?it should work in mysql
Bro.. I am using oracle database... Is there any change in oracle ?
@akash.i7391, It should work in oracle as well.
@@CloudChallengers but in Oracle first values function includes some statement like unbounded preceding something) ..if i was not using this key word ,it through error..I am using Oracle 11 g version
with cte as(
select dept_id,MIN(salary)minsal,MAX(salary)maxsal from emps_tbl
group by dept_id),
cte2 as(
select cte.dept_id,cte.minsal,cte.maxsal,e1.emp_name from cte
join emps_tbl as e1
on cte.dept_id = e1.dept_id and cte.minsal = e1.salary)
select cte2.dept_id,cte2.emp_name as min_sal_empname,e3.emp_name as max_sal_empname from cte2
join emps_tbl as e3
on cte2.maxsal = e3.salary and cte2.dept_id = e3.dept_id
with cte as
(
select
*,lag(emp_name) over(partition by a.dept_id order by salary desc) nxt
from himanshudb.emps_tbl a
join
(select dept_id as dpt_id,min(salary) min_sal,max(salary) max_sal from himanshudb.emps_tbl group by dept_id) b
on
a.dept_id = b.dpt_id and a.salary = b.min_sal or a.salary = b.max_sal
)
select
dept_id,emp_name as min_sal_emp_name, coalesce(nxt,0) as max_sal_emp_name
from
cte
where
nxt != '0';
with cte as(
select dept_id,MAX(salary) as max1 FROM emps_tbl GROUP BY dept_id
),cte1 as(
select cte.dept_id,emp_name as max_man FROM cte JOIN emps_tbl ON cte.dept_id=emps_tbl.dept_id and cte.
max1=emps_tbl.salary
),
cte3 as(
select dept_id,min(salary) as min1 FROM emps_tbl GROUP BY dept_id
),cte4 as(
select cte3.dept_id,emp_name as min_man FROM cte3 JOIN emps_tbl ON cte3.dept_id=emps_tbl.dept_id and cte3.
min1=emps_tbl.salary
)
select cte1.dept_id,min_man,max_man FROM cte1 JOIN cte4 ON cte1.dept_id=cte4.dept_id;