## 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!

### 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.  # Here is my solution for the prediction...

Hi!

Can you please check my solution for predicting how many students will be in the 6th grade next year? It works well I think.

```  SELECT (COUNT(id) + ROUND(AVG
(
(
SELECT COUNT(id)-(SELECT COUNT(students.id) FROM students
GROUP BY grade) AS "Growth 1"
FROM STUDENTS

)
+
(
SELECT COUNT(id)-(SELECT COUNT(students.id) FROM students
GROUP BY grade) AS "Growth 2"
FROM STUDENTS
)
``` Very interesting! You inspired me to try this but with a different approach. It took some googling...

First, I created a CTE (data1) to get the student count by grade data set to work with.

Next I made another CTE (data2) which joins that first CTE table to itself, but the join criteria is that each grade gets matched to the previous grade.

In the final table, I calculate the next year projected student count in the same way György has (student count + average growth of grade 6 over previous years). I create and union another table to calculate the next year projected student count for the other grades in a different way. With these we already know how many students are coming up from the grade below.

Final output is one table showing each grade, the current student count and what we expect the student count to be next year.

```-- How many students are in each grade? And how many 6th graders do you think they'll have next year?

WITH data1 AS (
COUNT(*) AS STUDENT_COUNT
FROM STUDENTS
)

-- data2 = grade, student_count, growth_from_previous_year
, data2 AS (
a.STUDENT_COUNT,
(b.STUDENT_COUNT - a.STUDENT_COUNT) AS growth_from_previous_year
FROM data1 AS a
LEFT OUTER JOIN data1 AS b
)

STUDENT_COUNT,
STUDENT_COUNT + (SELECT ROUND(AVG(growth_from_previous_year)) FROM data2) AS next_year_projected_student_count -- projected grade 6 growth next year = current year 6 student count + avg growth over previous years
FROM data2