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 Students by Grade

György Varga
György Varga
19,198 Points
Posted by 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,873 Points
Teacher Russell
Teacher Russell
16,873 Points

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

1 Answer

Matthew Stevenson
Matthew Stevenson
4,877 Points
Matthew Stevenson
Matthew Stevenson
4,877 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
;
Gia Hoang Nguyen
Gia Hoang Nguyen
4,137 Points

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