google-bigquerygoogle-cloudsql

Woodcreek College Exam Results

Ben Gorman

Ben Gorman

Life's a garden. Dig it.

Challenge

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 :slight_frown:.

Let's get stuck in!

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

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

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

Solution

This content is gated

Subscribe to one of the products below to gain access

Footnotes

  1. Dataset Source: http://roycekimmons.com/ ā†©