In this video, we will learn about how we can get.
Top subcategories per category.
So here you can see we have product categories and then we have Pader subcategories and here the third
column will contain the total number of products but subcategory and we need to know the top two subcategories
per category.
In the above picture, you can see we have one category.
Bikes and bikes contain three subcategories.
And these are the different number of products that are available in this sub category.
And we need to display top two subcategory for each category for this product bikes, which would display
like road bikes.
This is the top most because this category contains the highest amount of products.
And then the second will be mountain bikes.
And in the same way and the components and all the other categories that are available in the dataset,
in the below image, you can see the required output.
So we need to display the categories.
And then for each category, we need to display top two subcategories for like these are the top tossup
category for the component clothing, accessories.
So for all the available categories, we need to display top two subcategories.
And this should be based on the total number of products available for that subcategory.
Let's go to the power created for this example.
We need to categories of category and productivity.
Let's combine these three tables with each other so they click on the Mouskouri Much Korea's new product
category.
And then we need product subcategories.
This is the primary key and this one is the foreign key.
And we need Energiewende, OK?
And here we can rename
categories subcategories, here you can see one table that contains the information, multiple subcategories.
So we can remove the unnecessary columns.
And I'll try to add product.
Now we need to join with the productivity again, much curious new sorry for the formatting, we need
subcategory keys for this.
We have to take a broader subcategory key and in this part of subcategory name.
Here you can write subcategories.
And not too much as new.
Products and we need a subcategory.
Part of secretary energy.
OK.
And this will be the final day, both our top.
Subcategory.
This is an amateur table, so now we have product category and we don't need this column and we have
the sub category and this is the table that contains the product information.
And from the product table, we are interested only in the product.
So we need to add one column here, add column.
The project project underscore key.
And here we have the right column, epigastric column, so it needs the table, the table is product.
And then we are interested in the product key.
Yes, it.
OK, so here you can see we are getting the product column and now we need to count the number of products
for each subcategory so we can do it with the same step and go to the Lescott, the.
Setting and here we have to read.
One more function, Rockhound Tabletop Rockhound.
That's it.
OK, so here we got the number of products for every subcategory I command and basically a 30 year old
basically at 65 caps, we had three so we can delete this column.
OK, so now we have.
Category subcategory and.
And now we need to be rapping on English category.
So I need to click goodbye and here we need in this category name.
And we need all the rules against each category only to click OK, and here you can see buycks companies,
clothing and accessories and this column count.
This column contains a subcategory and product key.
And if you click the component, so here you can see we are getting.
Let me zoom it.
So here you can see we are getting 14, three one, then 40.
So how we can get the top products first we need to sort this table and once it will be sorted and then
we can get the top two or three or any number that you want.
So now we need to sort this table.
Every table we need to sort.
And so how we can do that, we need to add one more column.
Ed column and custom column.
And here we have to add.
The table, this is the name of the column and we need here sort of out sort.
And the name of the table is second.
So this is the name of the table which column we need to sort this out, so it will be, I think, Questrom.
That's it, look, OK.
It's a broader underscore key.
Product from this clunky.
So here you can see if I click here.
It is order in ascending order, right?
Everything is in ascending and we need to do this order by descending, so.
Let's go to the city and hear, if I try to add the third element is that it is not possible because
the table doesn't only need two parameters the first time, which is the table and the other parameter
is the criteria.
So we have to use here as a list.
And the first one is the product key and the second one will meet the criteria for order function.
Auchterarder sending this one.
And close the curly bracket.
So know, we need to click, OK?
So here you can see Citified 38, 22, and then the four components, it is 70 for the 18 and so on.
So now we are able to sort everything out and not the next function is we need to get the top two records
so we can do that.
We need to add one more column, custom column, and it is first table talk first, and this is the
function and it takes the first parameter is a table.
This is the product table.
And then we need that, how many euros do we need?
So this is the first.
Turow's, was it?
OK.
And here you can see.
For the components we are getting, 65, 38 and 43, for the bikes, we are getting 65, 38 for the
components, we are getting 70, 40.
And if you look at the product level, so he gives me 70, 40, and in the clothing we have 16 and nine.
And if I click here, the new column is 16 and nine.
So Knottier Wideman's has almost fulfilled.
So what we need now, we need to remove this column, the product label.
And we need to expand and in this column, we need subcategory and the product key.
This is subcategory, and it is you can rename it like a product called so far the we are getting on
our bikes, mountain bikes and the total number of products are certified.
And 38 and the same component are our friends, mountain friends.
So this is the way we can get top products by sell our products by account.
We can do anything what we need.