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 Results¹..

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

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