All right. Let's see the last one that we have. We want to find the total number of products in our category. So, for this one we'll do it very quickly. We have the Revenue Transactions. We know there's a field called product category, I don't like to memorize things. So, I just use Control F in the browser. I'm going to bring this selects. I'm going to have the product name and a product category from our particular table. I'm going to group by, because again this template's transactional, it's just going to return everything. So, if I just executed this, I'm going to get all 56,000 rows. So, I want to group things together. So, I'm going to group all, I'm going to do just instead of copying and pasting the names, I can group by the index for the columns, execute that and you can see this is the product lists for the product names and the product categories. So, you want a product category first, we can also do that. As you can see, it's a pretty intensive operation. If you're doing extremely large group by, it's having to look up every single product in every single product category. If you have something that has a ton of different products, getting all of those different products for every single 56,000 user, and bringing them all together could be an expensive operation. We'll cover that in one of the performance section on avoiding doing large group bys, because of the way the massive parallel processing is handled, and ways that you can actually filter and do aggregations. So, take a look here, we've got 989 products in our category list, and we've got all those different categories that are listed there. I'm going to add a couple of different filters where the products name is not null, meaning that it is populated. Right now I don't want to list the product categories. I just want account of all the different products as number of products, and I want to order by. Since we can use the alias outside of the where clause, the category that has the most number of products first. This will give us the total 989 products. If you exclude the null that's 988. If I just execute this, we have aggregated on the first column now. Now, we can no longer have that in the group by clause, and since I don't want to just grouped by two. I'm going to add a little bit more verbosity in the group by statement and boom, executing this will give us the total count of products. The category that corresponds to them. Filtering out to make sure that the product name actually exists, and order by the category that has the most number of products associated with it, starting from highest to lowest. So, let's see what that returns. Here we have it. They are over 15,000 products. That might not necessarily mean that those are individual products, because what this actually represents is the number of products that were ordered from that particular category because this isn't distinct. So, say we can actually throw in- this is insightful though, because 15,000 means that's how many products were ordered from there. Because, let's see the- if you summed up this column here, you will get that 56,000 number which is the total number of products ordered. So, let's make this a little bit more precise. We're going to say, we want the top five product categories in the actual number of products that were ordered. All right and here we go. So, the number of products, you have 387 products that don't have a particular category that set for it. So, that's insightful. You have 204 that are belong to apparel. You have 165 that belong to this. This could be something that you could want to investigate on your own. It looks like it is just a placeholder value. So, maybe that's also null, or it's set dynamically on the front end. You have 60 products associated with office, 30 associated with electronics. Again, I just limited to five, you can limit to your heart's content there. But it gives you a lot of insights that you have a lot of products that actually aren't set to a particular category. You have 165 that are set to this dynamic value it looks like. Then you have the highest of what you're actually selling, or available to sell in apparel. So, the question that I'll leave here with is more of a thought-provoking one. Does this actually represent all of the products that are in our catalog for sale? The answer, it depends on what you're actually querying for inside of your dataset. In this particular case we're querying revenue transactions. So, the real insight that you can bring here is because you're only querying things after the fact, mean that transaction is already bought. So, somebody's already bought a shirt, somebody's already bought office supplies. That's how we know it exists, but I'll leave you with a question. What about products that haven't been in somebody's card, or haven't been in somebody's checkout. Would those still appear on this revenue transaction dataset? The answer of course, is no. So, if you have a full list of our product catalog and you reconcile that against or join it against what's actually been bought, then you can get at things like, what product has been listed but no one's ever checked it out? Or what's a product is brand new and folks had been checking out a lot? We'll cover that type of deeper insight when we joined together the product catalog, the official product catalog, and the transactions data that we have here. That'll be in course two, when we're ingesting new data inside of BigQuery. But hopefully this was a good exercise. You saw a lot of common errors that you will likely experience inside of your career working with big query. We look forward to seeing you in the next couple of lectures. We're going to explore a few more concepts.
