[Master Ticket] Incorrect Query Generation

[Gautam-Rajeev]

For the question : "get me the schools with the highest average marks in maths from each district", the resultant SQL query does not create the required logic.

Current answer :

 SELECT s.name as school_name, s.city as district, AVG(m.marks_obtained) as avg_math_marks 
FROM school s 
JOIN class c ON s.id = c.school_id 
JOIN student st ON c.id = st.class_id 
JOIN mark m ON st.id = m.student_id 
JOIN subject sub ON m.subject_id = sub.id 
WHERE sub.name = "Maths" 
GROUP BY s.city 
HAVING AVG(m.marks_obtained) = (SELECT MAX(avg_math_marks) 
 FROM (SELECT s.city as district, AVG(m.marks_obtained) as avg_math_marks 
 FROM school s 
 JOIN class c ON s.id = c.school_id 
 JOIN student st ON c.id = st.class_id 
 JOIN mark m ON st.id = m.student_id 
 JOIN subject sub ON m.subject_id = sub.id 
 WHERE sub.name = "Maths" 
 GROUP BY s.city) as temp_table);

This holds true for the questions that require to rank within partitions/groups

[TSinghh]

Need to add s.name in line number 8 using GROUP BY clause

SELECT s.name AS school_name, s.city AS district, AVG(m.marks_obtained) AS avg_math_marks
FROM school s
JOIN class c ON s.id = c.school_id
JOIN student st ON c.id = st.class_id
JOIN mark m ON st.id = m.student_id
JOIN subject sub ON m.subject_id = sub.id
WHERE sub.name = "Maths"
GROUP BY s.city, s.name
HAVING AVG(m.marks_obtained) = (
 SELECT MAX(avg_math_marks)
 FROM (
 SELECT s.city AS district, AVG(m.marks_obtained) AS avg_math_marks
 FROM school s
 JOIN class c ON s.id = c.school_id
 JOIN student st ON c.id = st.class_id
 JOIN mark m ON st.id = m.student_id
 JOIN subject sub ON m.subject_id = sub.id
 WHERE sub.name = "Maths"
 GROUP BY s.city
 ) AS temp_table
);