LeetCode Medium 1112 Coursera Interview SQL Question with Detailed Explanation

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[abhinavmishra]

Thought of using ROW_NUMBER() window function, and then ordering by grade and course_id

[saisushanthdurvasulabitsian]

thank you so much for the video, could you pls review this query?
select distinct student_id
, first_value(course_id) over (partition by student_id order by grade desc, course_id) as course_id
, first_value(grade) over (partition by student_id order by grade desc, course_id) as grade
from Enrollments
order by student_id

[expandingourselves]

select student_id , course_id, grade from
(
select student_id , course_id, grade, rank() over ( partion by student_id order by course_id asc) as rnk from
( select student_id , course_id, max(grade) as grade
from Enrollments
)X
) Y where Y.rnk = 1

[yashnikhare]

with data as (
select *, dense_rank() over(partition by student_id order by grade desc) as rnk from Enrollments
)

select student_id, course_id from data where rnk=1 order by student_id

[shashikantshevale]

Thank you so much for this video.
We can also do without window function.

;WITH CTE
AS
(SELECT Student_Id,
Course_Id,
MAX(Grade) MX_Grade
FROM Enrollments
GROUP BY Student_Id,
Course_Id
)

SELECT Ct.Student_Id,
Ct.Course_Id,
En.Grade
FROM CTE Ct
INNER JOIN Enrollments En
ON Ct.Student_Id = En.Student_Id
AND Ct.Course_Id = En.Course_Id
AND Ct.MX_Grade = En.Grade