RANK() window function to calculate the rank of each student within their department based on their marks in descending order.COUNT() window function to count the total number of students in each department.(rank - 1) * 100 / (total_students - 1).ROUND() function.COALESCE() to handle cases where the denominator in the percentage calculation is zero (which happens when there is only one student in the department).student_id, department_id, and the calculated percentage for the final output.
erDiagram
Students {
int student_id
int department_id
int mark
}