György Varga19,198 Points
Here is my solution for the prediction...
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 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);
Thanks for your help!
Matthew Stevenson4,873 Points
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? -- data1 = grade, student_count WITH data1 AS ( SELECT GRADE, COUNT(*) AS STUDENT_COUNT FROM STUDENTS GROUP BY GRADE ) -- data2 = grade, student_count, growth_from_previous_year , data2 AS ( SELECT a.GRADE, a.STUDENT_COUNT, (b.STUDENT_COUNT - a.STUDENT_COUNT) AS growth_from_previous_year FROM data1 AS a LEFT OUTER JOIN data1 AS b ON b.GRADE = (a.GRADE -1) ) SELECT GRADE, 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 WHERE GRADE = 6 UNION SELECT GRADE, STUDENT_COUNT, STUDENT_COUNT + data2.growth_from_previous_year -- next_year_projected_student_count where student count is already known for lower grade FROM data2 WHERE GRADE <> 6 ;