UNPIVOT(grade FOR subject IN ("lowest_avg_english_score", "lowest_avg_psychology_score", "lowest_avg_economics_score")) 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.

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. 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. This blog will consist of screenshots of my queries, outputs and descriptions of what the queries are doing. The reason why this challenge was difficult for me was because, at one point, I tried nesting two aggregate functions. It looked something like this MIN(AVG(english)) AS “min_avg_english_score” — but this is completely wrong and would return an error. 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.)

You may also like…

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! 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! 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. With that, we can now make the unpivoted_data CTE, which is just the min_averages CTE but unpivoted.

In our case, since we’re working with Snowflake, our tables aren’t expressed in the form of Excel sheets. No problem! To consolidate these tables, we will need to use the UNION function (and a lot of them!) Here’s the query and output that represents the join between the two input tables. This is our first CTE which is called data.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. 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. 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. 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.

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!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. Subject” is the name of the field that contains aliased values for each of the scores. So, while the actual value of MIN(“avg_english_score”) is 68.2, its aliased value is “lowest_average_english score”. Finally, we can bring everything together with the code between lines 36 and 45 shown in the query below. 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!

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.

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. 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. The data of the Original_Joining Date represents the first clause of the REGEX_REPLACE function. In other words, that field is the original concatenation that needs to be modified further. This is what I was referring to earlier about how the Joining Day field has to be zero-padded. The second clause, which is the RegEx string pattern of ‘