Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

György Varga
György Varga
19,198 Points
Posted by 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";