Mphasis SQL Interview question - UPDATE scores column values with department wise maximum score

[@nagabhushannaga1801]

Good explanation and good to know possible ways to write logic

[@CloudChallengers]

@nagabhushannaga1801, Thanks for the encouragement

[@snehsparsh7954]

-- Approach 1: Using a subquery in the FROM clause
UPDATE
practiceDB1.emp_dept_tbl2 AS target
SET
scores = sub.max_score
FROM
(
SELECT
dept_name,
MAX(scores) AS max_score
FROM
practiceDB1.emp_dept_tbl2
GROUP BY
dept_name
) AS sub
WHERE target.dept_name = sub.dept_name;
-- Approach 2: Using a MERGE statement
MERGE practiceDB1.emp_dept_tbl2 AS target
USING (
SELECT
dept_name,
MAX(scores) AS max_score
FROM
practiceDB1.emp_dept_tbl2
GROUP BY
dept_name
) AS source
ON target.dept_name = source.dept_name
WHEN MATCHED THEN
UPDATE SET scores = source.max_score;

[@masterhipster8358]

with cte as (
select
*
, max(scores) over(partition by dept) as max_score
from empdept_tbl
order by eid
)
update empdept_tbl
set scores = cte.max_score
from cte
where empdept_tbl.eid = cte.eid;
select * from empdept_tbl

[@landchennai8549]

; with cte
as
(
select *
, FIRST_VALUE(scores) over (partition by dept order by scores desc) as NewScore
from #empdept_tbl
)
update cte set scores = NewScore

[@mahenderchilagani5916]

select eid, dept, max(scores)over(partition by dept) as scores from empdept_tbl

[@riteshhraj8033]

your query is correct but its not updating teh original table :).the thing is if yu do again select * from tablename ,you will see old table again :).

[@rudrakshisrivastava3417]

with cte as(SELECT *,
MAX(Scores) over(partition by dept) as max_dept_score FROM employee_scores
order by eid)
UPDATE employee_scores set scores=(select max_dept_score from cte where cte.eid=employee_scores.eid)
select * from employee_scores

[@chandanpatra1053]

I have solved using rows between preceding & following concept
select eid,dept ,
max(scores) over(partition by dept order by scores rows between unbounded preceding and
unbounded following) as 'scores'
from empdept_tbl

[@CloudChallengers]

Thanks for sharing different approach Chandan

[@pravinshinde1891]

select eid,dept, max(scores)over(partition by dept order by scores desc) as scores from empdept_tbl
order by eid

[@vijay.s-ll1yq]

Nice Logic

[@CloudChallengers]

Thanks for the feedback Vijay

[@hairavyadav6579]

my sql approach
update empdept_tbl join (select *,max(scores) over(partition by dept) max_score from empdept_tbl) as
new_emp on empdept_tbl.dept =new_emp.dept set empdept_tbl.scores= new_emp.max_score;

[@saravanansevanan]

--Solution_1
select *, max(scores) over (partition by dept order by eid
rows between unbounded preceding and unbounded following) as max_score from empdept_tbl
--Soution_2
select eid, dept,
case when dept = 'd1' then (select max(scores) from empdept_tbl_cc where dept = 'd1')
when dept = 'd2' then (select max(scores) from empdept_tbl_cc where dept = 'd2')
when dept = 'd3' then (select max(scores) from empdept_tbl_cc where dept = 'd3')
when dept = 'd4' then (select max(scores) from empdept_tbl_cc where dept = 'd4')
end as score
from empdept_tbl

[@dennurajan7897]

Can we use a case function here🤔

[@CloudChallengers]

@dennurajan78971, yes you can use. But you need to put efforts in writing logic. Instead we can go with other simple logic.

[@divyaborse5866]

with cte as (select dept,max(scores) as max_score from empdept_tbl group by dept )
select a.eid,a.dept,b.max_score as scores from empdept_tbl a left join cte b on a.dept = b.dept;

[@divyaborse5866]

sry I have problem statement wrong update part is missing
updated query
with cte as (select dept,max(scores) as max_score from empdept_tbl group by dept ),
cte2 as (select a.eid,a.dept,b.max_score as scores from empdept_tbl a left join cte b on a.dept = b.dept)
update empdept_tbl set scores = b.scores from empdept_tbl a inner join cte2 as b on a.dept = b.dept;

[@vijay.s-ll1yq]

update a set scores = b.cnt from
empdept_tbl a inner join
(select max(scores) cnt,dept from empdept_tbl
group by dept) b
on a.dept =b.dept

[@chinmay.dunakhe]

My Solution: with cte as(
select *, row_number() over(partition by dept order by scores desc) rn
from empdept_tbl)
select eid, dept, scores, case when rn = 1 then scores else (select max(scores) from empdept_tbl t2 where cte.dept = t2.dept) end as new
from cte
order by eid;

[@CloudChallengers]

Thanks for sharing different approach Chinmay.

[@ishanshubham8355]

update empdept_tbl e
inner join (
select eid,max(scores) over(partition by dept) as scores1 from empdept_tbl) m
using(eid)
set scores = scores1;

[@CEMANIVANNAN]

with t1 as(select *,dense_rank() over(partition by dept order by scores desc) dn from empdept_tbl qualify dn=1)
,t2 as(select eid,dept from empdept_tbl)
select t2.eid,t2.dept,t1.scores from t1 right join t2 on t1.dept=t2.dept order by 1;

[@Nikhil-xw2wc]

WITH DeptMaxScores AS (
SELECT
dept,
MAX(scores) AS max_score
FROM
empdept_tbl
GROUP BY
dept
)
UPDATE empdept_tbl
SET scores = d.max_score
FROM empdept_tbl e
JOIN DeptMaxScores d
ON e.dept = d.dept;

[@HARSHRAJ-gp6ve]

with cte as(
select dept,MAX(scores) as x1 FROM empdept_tbl GROUP BY dept
)
select eid,cte.dept,x1 FROM cte JOIN empdept_tbl ON cte.dept=empdept_tbl.dept;

[@SaifKhan-sd2gd]

with cte as(
select *, row_number() over(partition by dept order by scores desc) as rnk
from empdept_tbl
)
select eid, dept, FIRST_VALUE(scores) over(partition by dept ORDER by rnk) as new_scores from cte
order by eid

[@CloudChallengers]

@SaifKhan-sd2gd, can u try having update in your query?

[@kushmanthreddy4762]

select eid,dept,first_value(scores) over(partition by dept order by dept,scores desc range between unbounded preceding and unbounded following) as fi
from empdept_tbl order by eid ;

[@monasanthosh9208]

First Comment 😀
MYSQL Solution
Select eid,Dept,first_value(Scores) Over (Partition by Dept Order by Scores desc) as Scores from Empdept_Tbl
order by eid;
Updation not working in mysql

[@CloudChallengers]

@monasanthosh9208, Thanks for quick turn around 🙂

[@UnrealAdi]

Are these SQL questions for freshers or 3+ years of experience candidate?

[@CloudChallengers]

@UnrealAdi, This question is asked for experienced candidate with 3+ years of experience in Data Analytics.

[@sportsblast8215]

update empdept_tbl
set scores=5.28
where dept='d1';

[@CloudChallengers]

@sportsblast8215, this query will update values for dept d1 only. We have to update others department values as well.