Max CGPA by Year and Gender | Dataford Interview Questions

Write an SQL query to find the maximum CGPA per year, broken down by gender.

Use the student and academic record data. Treat records with a missing CGPA as unavailable.

Output

One row per academic year and gender with a non-null CGPA
Columns: year, gender, and max_cgpa
Sort by year ascending, then gender ascending, with missing gender values last

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's full name
gender	VARCHAR(30)	Student's gender classification

academic_records

Column	Type	Description
record_idPK	INT	Unique academic record identifier
student_id	INT	Student associated with the academic record
academic_year	INT	Academic year represented as a four-digit year
cgpa	NUMERIC(3,2)	Cumulative grade point average

Your question is Max CGPA by Year and Gender. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.