Samagra-Development / Text2SQL

20 stars 11 forks source link

[Master Ticket] Incorrect Query Generation #27

Open Gautam-Rajeev opened 1 year ago

Gautam-Rajeev commented 1 year ago

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 commented 1 year ago

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
);