IKRUX SQL Interview Question - SQL JOINS, GROUP BY, STRING_AGG() Function

One of the SQL questions recently asked in IKRUX Solutions interview.
Given us Emp and Dept table, we need to find the Department wise highest salary emp name.
In order to solve this questions, we used SQL JOINS, GROUP BY and STRING_AGG() Function.
Let us create Emp table and insert records
create table emp(empid int, empname varchar(50), salary int, deptid int)
insert into emp values (1,'Nikitha',45000,206),(2,'Ashish',42000,207),(3,'David',40000,206),(4,'Ram',50000,207),(5,'John',35000,208),(6,'Mark',50000,207),(7,'Aravind',39000,208)
Let us create Dept table and insert records
create table dept (deptid int, deptname varchar(50))
insert into dept values (206,'HR'),(207,'IT'),(208,'Finance')
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@chinmay.dunakhe]

How to get that last row with , in mysql

[@CloudChallengers]

@chinmay.dunakhe, you can try GROUP_CONCAT(empname)

[@chinmay.dunakhe]

@@CloudChallengers got it thanks done

[@ishanshubham8355]

with cte as (
select empname,deptname,rank() over(partition by deptname order by salary desc) as rnk
from emp1 inner join dept
using(deptid))
select deptname,group_concat(empname) as name
from cte
where rnk=1
group by deptname

[@pallavimohapatra7241]

In Postgresql :-
select deptname, STRING_AGG (empname, ', ')
from (select *, dense_rank() over(partition by deptid order by salary desc) as col_num
from (select e.*, d.deptname from emp e join dept d on e.deptid = d.deptid order by empid)x)y
where col_num = 1
group by deptname

[@user-gq6cg3ls7f]

with cte as(
select *, max(salary) over (partition by deptid order by deptid) max_sal from emp
),
cte2 as(
select * from dept)
select deptname, STRING_AGG(e.empname,',') empname from cte c inner join
emp e on c.max_sal = e.salary and c.empid = e.empid
inner join cte2 on c.deptid = cte2.deptid
group by deptname

[@Savenature635]

with cte as (select empname,deptname,dense_rank() over(partition by deptname order by salary desc) as rn
from emp e
join dept d
on e.deptid=d.deptid)
select deptname,group_concat(empname) as empname from cte
where rn=1
group by 1;

[@user-gq6cg3ls7f]

with cte as(
select * from(
select e.empname, d.deptname, dense_rank() over (partition by e.deptid order by salary desc) RN from emp e inner join dept d on e.deptid=d.deptid
) x where x.RN = 1
)
select STRING_AGG(empname,','), deptname from cte group by deptname

[@devarajululanka6427]

Here is my answer :
with cte as (
select e.empname,e.salary,d.deptname from emp e
inner join dept1 d
on
e.deptid = d.deptid ),
rnk_salary as ( select empname,deptname,
rank() over (partition by deptname order by salary desc) as rn
from cte )
select deptname,STRING_AGG(empname, ', ') as empname from
rnk_salary
where rn = 1
group by deptname

[@jagannathsahu1087]

It's a great learning from all of your videos. Thanks

[@CloudChallengers]

@jagannathsahu1087, Glad to hear that!

[@KESHANNAKUMMARI-cj2sf]

SELECT DEPTNM,GROUP_CONCAT(EMPNAME) FROM(SELECT D.DEPTNM,E.EMPNAME,DENSE_RANK()OVER(PARTITION BY E.DEPTID ORDER BY E.SALARY DESC)AS DRNK FROM EMP E
LEFT JOIN DEPT D ON E.DEPTID=D.DEPTID)A WHERE A.DRNK=1 GROUP BY DEPTNM ORDER BY DEPTNM;

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@vijay.s-ll1yq]

select deptname,STRING_AGG(empname,',') emp_name from
(
select
e.*,
max(e.salary) over (partition by d.deptid order by e.salary desc ) fdf,d.deptname
from
emp e
inner join
dept d
on e.deptid =d.deptid)ef
where salary =fdf
group by deptname
with cte as
(select
e.*,lag(salary,1,salary+1) over (partition by e.deptid order by e.salary) as lag_data,d.deptname
from
emp e
inner join
dept d
on e.deptid =d.deptid),
cte3 as
(select *,case when salary >=lag_data then 1 else 0 end as final from cte
)
select deptname,STRING_AGG(empname,',') from cte3 where final 0
group by deptname

[@CloudChallengers]

Thanks for sharing different approaches Vijay. Keep sharing different approach for upcoming videos as well.

[@sudhindrab1606]

with cte as(
select *,
max(salary)over(partition by deptid ) as max_sal
from emp),
cte2 as(
select empname,b.deptname from cte a
join dept b on
a.deptid=b.deptid
where salary = max_sal)
select STRING_AGG(empname,',') as empname,deptname from cte2
group by deptname;

[@CloudChallengers]

Thanks for posting different approach Sudhindra. Keep posting different approaches for upcoming videos as well.

[@venkatesh.kedarisetti]

in oracle instead of string_agg(empname, ',') which function will use?

[@CloudChallengers]

@venkatesh.kedarisetti, We can use LISTAGG() function. Syntax will be different, try exploring the syntax for LISTAGG() function in Oracle.

[@iamram436]

with cte as (select d1.deptname, e1.empname,e1.salary,
max(e1.salary) over(partition by d1.deptname order by e1.salary desc) as maxsalary
from emp_cd e1
inner join dept_cd d1
on e1.deptid=d1.deptid)
select deptname, string_agg(empname,',') as empname from cte where salary=maxsalary group by deptname

[@CloudChallengers]

Thanks for posting different approach Ram.

[@vasanthkumar-zw3xf]

select deptname,string_agg (empname,',') empname
from emp1 join dept1
on emp1.deptid=dept1.deptid
where salary in(select max(salary) from emp1 group by deptid)
group by dept1.deptname