Write SQL for top student per department

Q: Write SQL for top student per department

This is a Data Manipulation (SQL/Python) interview question from Point72 for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Schema: Departments(dept_id PK, dept_name) Students(student_id PK, student_name, dept_id FK nullable, gpa numeric(3,2) nullable, enrolled_at date)

Sample data: Departments +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | CS | | 2 | Math | | 3 | Physics | | 4 | History | +---------+-----------+

Students +------------+--------------+---------+------+------------+ | student_id | student_name | dept_id | gpa | enrolled_at| +------------+--------------+---------+------+------------+ | 101 | Alice | 1 | 3.90 | 2022-09-01 | | 102 | Bob | 1 | 3.90 | 2023-01-15 | | 103 | Cara | 2 | 3.85 | 2021-09-01 | | 104 | Dan | 2 | NULL | 2024-02-01 | | 105 | Eve | NULL | 3.70 | 2023-09-01 | | 106 | Frank | 3 | 3.90 | 2020-09-01 | +------------+--------------+---------+------+------------+

Write a single SQL query to list, for every department (including those with no students), the department name and the top student by GPA; break GPA ties by earliest enrolled_at, then by smallest student_id. If a department has no students with non-NULL GPA, return one row with student fields NULL. Output columns: dept_id, dept_name, student_id, student_name, gpa, enrolled_at. Show the expected result for the sample data.

Write SQL for top student per department

Comments (0)