KPMG SQL Interview Question - Windows function ROWNUMBER( )

[@Aditya61515]

today's kpmg first round simillar type of question they asked we have One table,columns are year, sallary and name WAQ to show me Only max sallary tabel like
ram 2022 100
sham 2023 200
ram 2020 500
sham 2020 1000
ram 2018 150
sham 2019100
O/P
Ram 500
sham 1000

[@CloudChallengers]

@Aditya61515, That Great. Thanks for posting here.

[@Aditya61515]

Thank's to you Sir ♥️

[@professor_DJ_010]

the output can be solved without even using window function na -
SELECT
name,
MAX(salary) AS max_salary
FROM
EmployeeSalaries
GROUP BY name;

[@khusboo4907]

select sname, sum(marks) as totalmarks from stud group by sname..... let me know if this is correct

[@akash.i7391]

​@@khusboo4907sorry bro.. It's wrong ❌
That following query returns the total of 3 values in the table.. But we want only 2 maximum value..

[@sekharskht]

I really appreciate your efforts in creating all these SQL Interview questions. Very helpful

[@CloudChallengers]

Thank you for the encouragement.

[@vijaygupta7059]

same approach but using CTE
with cte as(
Select *,ROW_NUMBER()over(partition by sname order by marks desc) as rn from students
)
Select sname, sum(marks) as totalmarks from cte
where rn

[@CloudChallengers]

Yes, that works. Thanks for posting the comment.

[@MusicalShorts-hn1px]

Appreciate your hard work...Liked the way you clarify why we're using Rownumber not Rank function...plz keep posting more SQL and Python interview with same approack

[@CloudChallengers]

@MusicalShorts-hn1px, Thanks for the encouragement. Please check similar SQL interview question in our playlist. th-cam.com/play/PLxHEfsUVhEwMqw-nDG2zd3mpXpvY1v9xX.html&si=gH7tFCwALzV1JtgL

[@prajju8114]

with cte_student
as
(
select sname,sid,marks,row_number() over(partition by sname order by marks desc) as rn from students
),
cte_2 as
(
select sname,sum(marks) as totalmarks from cte_student where rn

[@HarshSoni-x3j]

with cte as (select *,row_number() over(partition by sname order by marks desc) as c1 from stumarks order by sname,marks desc)
select sname,sum(marks) as totalmarks from cte where c1 in (1,2) group by sname;

[@sohinibanerjee9617]

;with ctex as
(SELECT *, ROW_NUMBER() over (partition by sname order by Marks desc) as Row_id from Marks)
select sname, sum(Marks) from ctex
where Row_id3
group by Sname
or
;with ctex as
(SELECT *, ROW_NUMBER() over (partition by sname order by Marks desc) as Row_id from Marks)
select sname, sum(Marks) from ctex
where Row_id

[@gouthamstar6558]

with cte as
(
select *, ROW_NUMBER() over(partition by sname order by marks desc) as part_marks from students
)
select sname, sum(marks) from cte
where part_marks

[@venkateshdhanasekaran5398]

with cte as (select *, row_number() over(partition by sname order by marks desc) as rnk from students_kpmg)
select c.sname,sum(c.marks) from cte c where rnk in (1,2)
group by c.sname;

[@sudhindrab1606]

select sname, sum(marks1) from (
select *,lead(marks)over(partition by sname order by marks ) as marks1 from input)a
where marks1 is not null
group by sname

[@mahenderchilagani5916]

with cte as(
select *, row_number() over(partition by sname order by marks desc) as rn
from students)
select sname, sum(marks) as Total_marks from cte where rn in (1,2) group by sname

[@RamanKumar-yb1dx]

select sname, sum(marks) as total_marks from (
select *,
row_number() over(partition by sname order by marks desc) as rn
from students
) as sub where rn

[@maheshnagisetty4485]

select sname,sum(marks) totalmarks from
(select sname,marks,row_number() over (partition by sname order by marks) as rn from students) as a
where rn>=2
group by sname

[@CloudChallengers]

@maheshnagisetty4485, if we have 4 or more records per student with different marks, this query may not give expected output.

[@anvithganapathy1421]

Cant we use dense_rank instead of row number?

[@CloudChallengers]

@anvithganapathy1421, I think we can't use dense_rank(). It may pick 75 two times. It would be much appreciated if you have query to get the expected output using dense_rank()

[@HimanshuBhattt]

Select sname, sum(marks) as total_marks
from
(
Select *
,row_number() over( partition by sname order by marks asc) as rn
from students
) a
where rn >=2
group by sname

[@CloudChallengers]

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

[@hrishi2839]

select sname,sum(marks) from students
where sid != 'X' and sname = 'A'
group by sname
UNION
select sname,sum(marks) from students
where sid!= 'Z' and sname = 'B'
group by sname

[@HARSHRAJ-gp6ve]

with cte as(
SELECT
students.*,
rank() OVER (PARTITION BY sname ORDER BY marks DESC) AS X1
FROM
students
),cte1 as(
select sname,marks FROM(select cte.*,ROW_NUMBER()OVER(PARTITION BY sname ORDER BY marks DESC) as X2 from cte)
as es where es.X2

[@Harish0402]

select sname , sum(marks)total_marks from
(select s.* , row_number() over(partition by sname order by marks desc) rn from students s)
where rn

[@theinsightminer08]

SELECT
sname,
SUM(marks) AS Totalmarks
FROM
(SELECT
*,
DENSE_RANK() OVER(PARTITION BY sname ORDER BY marks DESC,sid) as rnk
FROM students)
WHERE rnk

[@viru19ful]

WITH top_2_scores AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY sname ORDER BY marks DESC) AS rn
FROM students)
SELECT DISTINCT(sname), SUM(marks) OVER(PARTITION BY sname) AS top_2
FROM top_2_scores
WHERE rn < 3

[@trb_rangababu5040]

here is the answer. please correct me.
select sname,sum(distinct marks) from students
group by sname

[@CloudChallengers]

@trb_rangababu5040, Your query is not giving expected output

[@saiteja-gb8ho]

Here is my solution in PostGres:
WITH CTE AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY SNAME ORDER BY MARKS DESC ) as RN
FROM STUDENTS)
SELECT CTE.SNAME,SUM(CTE.MARKS) FROM CTE
WHERE CTE.RN IN (1,2)
GROUP BY CTE.SNAME

[@CloudChallengers]

Thanks for posting different approach Saiteja.

[@saiteja-gb8ho]

@@CloudChallengers Thank you bro for posting useful content on sql and explaining in a simple way Keep up your good work and please post more of such questions

[@tanmay_609]

with cte1 as(
select *,
ROW_NUMBER() over(partition by sname order by marks desc) as rnum
from students)
select cte1.sname,sum(marks) as total_marks from cte1
where cte1.rnum