Skip to content

Abysmal Grades


You work at Goober Elementary ๐Ÿ‘ฉโ€๐ŸŽ“, where students recently took their initial exams. The grades are in, and you want to analyze them. In their current form they look like this

grades.csv

|student_id |gender | math_score| reading_score| writing_score|
|:----------|:------|----------:|-------------:|-------------:|
|AyL1u      |male   |      92.12|         68.87|        100.00|
|Tp312      |female |      71.40|         44.97|         76.11|
|rq5zh      |female |      78.38|         47.51|         82.11|
...
|JWRWU      |male   |      71.26|         84.21|         57.17|
|3Vku1      |male   |      77.79|         54.90|        100.00|
|5en5d      |male   |      82.53|         65.67|         59.59|

You decided to categorize scores into the following tiers

  • stellar: score >= 90
  • passing: score in the range [60, 90)
  • failing: score in the range [10, 60)
  • abysmal: score < 10. Just for kicks. No one would score this low.....

Pop the grades.csv file into a BigQuery table named grades. Then measure the number of students who fall into each (gender, tier) pair, for each subject, producing an output like this.

|gender |tier    | math| reading| writing|
|:------|:-------|----:|-------:|-------:|
|female |stellar |   12|      11|      10|
|female |passing |   24|      15|      25|
|female |failing |    7|      17|       8|
|male   |stellar |    7|      17|      11|
|male   |passing |   39|      28|      30|
|male   |failing |   11|      12|      15|
|male   |abysmal |    0|       0|       1|

Make sure the output is ordered by (gender, tier), where the tiers are in order from best to worst.


See the solution