Abido PureStar Herbal skin heal Ex. 2021 Original

Product Information

You may have noticed the Month_Part field. As the name suggests, I’ll be using it to create a date field. Let’s compare this output with the desired output which comes from the challenge post and is pictured below. 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. In case the text in the above image is too small, they show an average score of 73.8, 62.3 and 55.5 for the subjects of English, Psychology and Economics. These values are not the ones we’re looking for. Instead, we are looking for the lowest average score. So, we need the Class field so that the result for the averages CTE is six rows instead of one (this will also be relevant later on).

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 ‘ 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 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!)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.

With that, we can now make the unpivoted_data CTE, which is just the min_averages CTE but unpivoted. 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!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”. 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”. 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 for the Joining Date field, this can be constructed by concatenating the following elements Joining Day (where zero-padding is applied where appropriate, i.e. 01 or 09), Month Part and 2023.

This blog will consist of screenshots of my queries, outputs and descriptions of what the queries are doing. 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. Here’s the SQL query that handles that. This CTE is aliased as data and has been referred to as the “unioned output” throughout this article. 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.) 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. 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.

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. 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. This is important because if the logic was strictly just looking for attendance percentages that were just higher than 0.9, that would mean Bob Jones would be assigned a Medium Attendance, which would differ from the desired output. In contrast, having an attendance rate of 0.7 would not lead to a student being categorized as having Low Attendance but instead Medium Attendance (look at row 11 for Kate Liu). This is because the logic for the Low Attendance is non-inclusive. That is, only those attendance percentages that are below 0.7 are categorized as Low Attendance.

As you can see in the output above, the Joining Date field is probably the easiest field to make because it doesn’t require transforming the unioned output. This is because none of the necessary elements for the field is contained with the Demographic- Value field pair. 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. 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.