Databases SQL Reporting by Example Day 2: Advanced Selecting Predicting the Future

György Varga
György Varga
19,198 Points

Chech my solution please!

Hi! Here is my solution for "Predicting the Future". Please check it!

SELECT 
(
(

SELECT MIN(CAPACITY) * (SELECT COUNT(ID) FROM PERIODS) FROM classes
INNER JOIN subjects ON subjects.id = classes.subject_id
INNER JOIN rooms ON rooms.id = classes.room_id
WHERE subjects.grade = 6

)

>

(
  SELECT (COUNT(id) + ROUND(AVG
(
  (
  SELECT COUNT(id)-(SELECT COUNT(students.id) FROM students
                           WHERE grade = (SELECT DISTINCT(max(grade)-1) FROM students)
                           GROUP BY grade) AS "Growth 1"
FROM STUDENTS
WHERE grade = (SELECT MIN(grade) FROM students GROUP BY GRADE)
GROUP BY grade

  )
  +
  (
  SELECT COUNT(id)-(SELECT COUNT(students.id) FROM students
                           WHERE grade = (SELECT  DISTINCT(max(grade)) FROM students)
                           GROUP BY grade) AS "Growth 2"
FROM STUDENTS
WHERE grade = (SELECT DISTINCT(max(grade)-1) FROM students)
GROUP BY grade
  )
)/2)) AS "Next year 6th grade prediction" FROM students WHERE grade = (SELECT  DISTINCT(min(grade)) FROM students)
)
) AS "1 = ENOUGH ROOM | 2 = NOT ENOUGH ROOM";