Skip to content

Woodcreek College Exam Results

Pleased with your progress so far, your supervisor at Woodcreek College shared your work with colleagues, which went down really well 😃! Perhaps too well, as your supervisor is back with a new set of requests from the team 🙁.

Let's get stuck in!

Skipped Part 1?

We recommend doing the problem Beginner > Woodcreek College Exam Results before trying this one, but if you're keen to press on anyway then you must download our student_results_combined.csv file and upload it as a table named Student_Results_Combined in BigQuery.

Recall the Student_Results_Combined table we created in the precursor problem Beginner > Woodcreek College Exam Results1..

Student_Results_Combined
|Student_ID   |Gender |Year_Group |Parental_Level_of_Education |Lunch_Plan   |Test_Preparation_Course | math_score| reading_score| writing_score|
|:------------|:------|:----------|:---------------------------|:------------|:-----------------------|----------:|-------------:|-------------:|
|9508878DBECD |Male   |Group A    |High School                 |Standard     |None                    |         32|            34|            28|
|2F0C6D6AA452 |Male   |Group A    |Associate's Degree          |Free/Reduced |None                    |         49|            39|            30|
|26663929B9D5 |Male   |Group A    |High School                 |Free/Reduced |None                    |         41|            40|            32|
...
|78B70E4F2601 |Female |Group E    |Associate's Degree          |Free/Reduced |Completed               |         90|            99|           100|
|8B22437CACFD |Female |Group E    |Bachelor's Degree           |Standard     |Completed               |         94|            99|           100|
|0DB4F0C08BD8 |Female |Group E    |Bachelor's Degree           |Standard     |Completed               |        100|           100|           100|

This time, your supervisor wants you to report pass rate - the percentage of students who passed their exam. A student passed their exam if they scored at least 60.

Your supervisor requests to see pass rates by

  • (Gender, Year_group) pairs and
  • (Gender) and
  • All students combined

... in the same report 😵‍💫.

The pass rate must also be formatted to one decimal place and should include the % sign in the results for readability.

Expected Output

Hint
  • You'll need to use an uncommon addition to the GROUP BY clause for this called ROLLUP. This is key for producing the rolled up pass rates. You'll need to use a sub-query to re-format the null values generated by ROLLUP

Previous Next Solution

  1. Dataset Source: http://roycekimmons.com/ ↩