With that, we can now make the unpivoted_data CTE, which is just the min_averages CTE but unpivoted. Here’s the query and output that represents the join between the two input tables. This is our first CTE which is called data. The next two fields, Attendance Percentage and TestScoreInteger, involved using the ROUND function. However, as you can see in the desired output, these two fields are two different types of numbers. Attendance Percentage is a decimal while TestScoreInteger is an integer. When using the ROUND function, it matters what the original number format is for the affected field. To see why, take a look at the supplemental query and output below. It showcases why configuring the ROUND function in an appropriate way matters. In this blog, I’ll be working through the Preppin’ Data challenge called “Is it the teacher or the student? Part 1”. Try it out here! I’ll be using the incredible SQL to solve this challenge!

As you can see, in contrast to the aforementioned supplemental output (what would happen if we didn’t group the averages CTE by the Class field), the values shown here of 68.2, 52.4 and 59.6 for the subjects of English, Economics and Psychology respectively are correct.Moving on, let’s look at how the Student Name column was handled to be able to create two new fields that represent First Name and Surname respectively. This can be seen in the SQL query below.

In other words, it’s crucial to be careful with how thresholds are defined if we want to convey our data accurately and meaningfully. We can get totally different results if we get confused about how we use comparison operators! What the UNPIVOT function does is to transform the shape of the table. Like I mentioned earlier, the min_averages CTE has three fields and one row. The fields represent each of the subjects and the single row represents the score for each subject. However, through the UNPIVOT function, the unpivoted_data CTE now has two fields and three rows. The fields are Subject and Grade while the three rows represent each of the scores for the aforementioned subjects. In other words, we took the three fields from the min_averages CTE and transformed to be rows in the unpivoted_data CTE. Now we can build out the second CTE, averages, which calculates the average score across the English, Psychology and Economics fields and groups this result by the Class field. However, in the case of the TestScoreInteger field, we can just round the test_score field normally. Ultimately, we just have to be careful about what data changes we’re doing to our data. Are those changes actually necessary or correct? If not, then that could cause a lot of confusion for end users who are then going to use the datasets that we prepare, such as populating a C-suite facing dashboard with misleading numbers. Imagine showing a totally wrong KPI to the CEO because of a rounding error or because of some faulty aggregation logic. What a disaster!One last thing that’s worth mentioning is the need for the WHERE statement. What it’s doing is limiting the result to only show the rows where grade values (which are lowest average scores across the three subjects) are equal to the average scores for the three subjects. To illustrate what this logic is actually doing, let’s take a look at a supplemental output which is returned when we remove the WHERE statement. Take a look at Bob Jones at the last row of the image above. His attendance rate is 0.9 This matters because the logic of the High Attendance value in the Attendance Flag field is inclusive. In other words, the logic assigns High Attendance for any student whose attendance percentage is 0.9 or higher. As you can see, if we were to just ROUND the Attendance Percentage field normally, every student’s attendance percentage would be 1. If we used the wrong_ap field in the query that is responsible for the Attendance Flag field, then the logic would totally break and every student would be categorized as having High Attendance. Not only would this differ from the desired output, this would completely undermine any attempt to determine if there’s “a correlation between attendance and test scores” (as the challenge post outlines as the reason for this work.)

In this blog, I’ll be working through the Preppin’ Data challenge called Student Attendance vs Test Scores. You can find it here. This was a pleasantly straightforward challenge where, among other functions, I got to use the ROUND function. Give the challenge a try!

You may also like…

The last field that needs to be fixed is Subject. The problem with this field is that the subjects of English and Science are misspelled. This is shown with the supplemental query and output below. Finally, we can bring everything together with the code between lines 36 and 45 shown in the query below. For this challenge, we need to join two tables, Student Information and Student Results. After that, we need to average the scores in the English, Psychology and Economics fields and group them by the Class field. Then we need to find the lowest average score across the three subjects and relate those scores to their respective classes as shown in the output table below. Overall, I used four CTEs to solve this challenge. The second issue I was running into was properly setting up the UNPIVOT function as shown in the query for the unpivoted_data CTE. I had to read through the documentation of that function a few times, but I think, at least for this problem, I figured out what the syntax of the function amounts to in the output as shown above.

Moving on, the below query and output shows us the min_averages CTE, which results in only one row with the correct values that we’re looking for. As you can see above, both the SQL query and the result table have been condensed. Both this output and the desired output, which will be shown after this paragraph, have 990 records.To fix the misspelling of “Sciece” and “Engish”, I used two CASE statements and the wildcard character % which looks for every character in a string. In this case, I want to look for every string in the original Subject field that starts with “S” and “E” respectively. Once those strings have been found, I replace them with the correct spellings of “Science” and “English”. UNPIVOT(grade FOR subject IN ("lowest_avg_english_score", "lowest_avg_psychology_score", "lowest_avg_economics_score")) With all of that said, let’s start diving into how the various fields were made! Let’s begin with the most straightforward one which is Joining Date. The SQL query is pictured below.