All right, last piece. Let's practice the actual SQL piece with one or two more examples and we'll launch you into your next lab. Okay, so here is our example, and I'm actually going to hop into the BigQuery web UI as soon as I explain what's going on. We have a news story publication, called Hacker News, it's a public BigQuery data source. And we will want to pull the top two Hacker News articles by day. because there's an upvote mechanic, and users can like these things. And I'm actually going to walk you through this inside of one of our saved queries. So Query History, let's go into the Saved Queries, let's find our Hacker News example and edit it. Okay, let's walk through what's going on here. So first and foremost, this is the solution for the query, so let's break it apart and see what the fundamental pieces are. And you can see they make best practice of having the common table expression do a little bit of the work first. And then there's the actual iteration of the limit and the ordering of the top two articles a little bit later. But let's break this apart by seeing what this first piece does. So first, let's look at the underlying data set itself. So I want to pull up the Hacker News stories, again, by hitting Cmd or Ctrl and selecting that. And I want to preview the results. So I have a bunch of articles, it looks like the unique identifier here is an article title. And the article has a score associated with that, which is how popular it is, and the articles are published at a certain timestamp. We're going to have to do some date parsing on this so we can get down to individual days, right? So we don't have to worry about the timestamp. So we've got a ton of individual article titles, nothing else looks interesting here, great, so now what are we looking to do? The first thing that we need to do is we need to get just the elements that we care about. So we're going to set up an array of articles titles and in the up vote score that is associated with them into an array. And again, setting up an array is using that array aggregation function that you've seen before. And then very normal SQL function after that is just extracting the date piece of this timestamp. So we can later on do a group by, and then get that daily value. And just some simple data quality clean up where the up voting score is not null. So it has to have a score, and the article title is not null, so that's just for data quality. And then we will actually want to just group these by the day. Now, what does that mean? This is where it actually gets pretty powerful. So if you have multiple articles that are published on the same day, so that day, itself, like December 20th, 2013, that's going to be the parent record in this kind of parent, child nested relationship. So you could have the day itself that has multiple, multiple records that are associated with that. So I don't want to just select the whole world here. So let's just select, say, ten records, and execute that. So what I imagine you might see is days and then some empty space. And here you go, that's exactly what you see. So you see these are all of the articles that are published on December 31st, 2013. And you see, again, the mind blowing part is this is all one row, and that's because this is just an array. And inside the array, it's actually a struct, and then the struct itself says, hey, store this string value of title. And as another item in that struct, store this score value of integer. So now it's pretty simple after this. Now, once you set up your array, you can see what we're going to do here. We're going to take the top value of the score, the top two values. You're going to probably see an ordered by to take the top two values, much like you saw in our partitioning examples a little bit earlier. And of course, the title that corresponds with it. So now that we have this data prepared, let's look at the next part of the query, pretty cool? Okay, so nothing too crazy here. So again, in order to operate over these arrays, you're going to see the unnest here. So we need to unnest this titles array, and again, this is titles.title, a little bit confusing that it's titles and then the column is actually title. A little bit confusing there, but we need to unnest this so it's not just in one row, so everything is broken out into individual rows. So we do that doing the unnest, and we need to make sure that we're ordering it by the highest score first. And that's descending, it's highest to lowest, and we only want to take the top two. So let's see what we're actually doing here. So we're unpacking the array, and then we're packing it back up by calling it an array again. And let's see, so we're saying, give me the title, and give me the score. From unpacking this array and order it by the score, highest to lowest, limited to the top two. Now the crazy part is we're doing that all in this field here, repacking it into the array. But if you notice, we're doing it by this other field that's right here, which is the day. So we're doing this operation on our arrays for every single date that's present. So let's execute the whole query and let's see what we get. Perfect, so we just condensed the massive amount of results for each different day into the top two results. So you have for each of the different days, we have the top two articles that are present there. And the score of those particular two articles ordered here as well. All right, that is your struct and unnesting example. Feel free to come back to this and practice a lot more of these examples. It'll get more familiar and familiar as time goes by, but you see all of the pieces kind of came together with our array aggregations using something like a column/table expression to set that up. And then unpacking the array, performing some operations on it, and then repackaging it back up so we can do something fun with having two articles associated with that particular date, as you see there. Fantastic, and again, you see here that this is one, two, three rows, not six rows, perfect. All right, let's go into a little bit of a recap, and then practice this in our next lab. And there was a lot to cover in this module, and even you SQL gurus out there may have seen a lot of new architecture concepts. Let's summarize the key points. Recall that BigQuery, and even Google Cloud Storage relies in breaking apart data sets into smaller chunks, called shards, which are then stored. Working with data in smaller shards allows BigQuery to massively parallel-process your query by using many workers or slots at the same time. Another key difference for BigQuery is that your actual table is not stored as a table at all. Instead, each individual column is broken off, compressed, and then stored individually. And this is why, when you limit your select queries to just the columns you need. BigQuery does not need to fetch the entire record from disc, which saves you on the amount of bytes that are processed. Lastly, BigQuery natively supports structs and arrays as part of repeated fields. Structs, again, are your flexible data container and an array of multiple structs is the basis for our repeated fields of setup. And again, repeated fields offer that performance benefit of having a pre-joined table, where parent and child records are nested with each other. While avoiding the downfall of increased scanning time of large detail record tables,when you don't need that level of detail. You access these nested child records by using the SQL unnest syntax in conjunction with that cross-join that you just learned about. Now, let's practice these concepts in our next lab.
