Databases SQL Reporting by Example Day 2: Advanced Selecting Students by Grade

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

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

Teacher Russell
Teacher Russell
16,869 Points

I can spend the next few days studying this. Thanks:)

1 Answer

Matthew Stevenson
Matthew Stevenson
4,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
;

I am interested in Mathew's approach but still cant understand the concept behind the b.GRADE = (a.GRADE -1).

Can someone help me please?

Thanks