This video shows how to author Common Table Expressions in Fabric Data Warehouse to Rank Sales by Products.
Show More Show Less View Video Transcript
0:00
In this Microsoft Fabric Data Warehouse video
0:05
I'm going to show you how to auto common table expression query popularly known as City Equity
0:12
I'm going to use the rank window function with the over clause to rank sales amount by products
0:18
So let's get started. We're going to use the table called KQL1 in my Data Warehouse of Fabric
0:28
Of course, this is simple select star which select or return all the columns in the KQL1 table
0:35
Of course, you can see we have the date, the year, product, payment type
0:39
unit, price, and the sales amount column. Go ahead and auto this query
0:44
The first thing I'm going to do is to use the width keyword, which define the city and then we're going to provide a name for the city
0:51
So I'm going to use the width and then I can call the name of the city as sales by products
0:57
You can actually use anything like that is totally fine. I'm going to use the Rs and then open and close the brackets
1:03
So inside open and close bracket, I'm going to expand. So I can go ahead and select my column
1:10
So I'm going to use this select and I want to select the product column
1:14
So tabs, so I'm going to call it product and then go to the comma
1:18
and then I'm going to jump to the next line to make the code readable and easy to understand
1:23
Now, I'm going to use the sum aggregate function and I want to aggregate the sales
1:27
I want columns, I want to call it sales amount, and then I'm going to double-click on that
1:32
Then I'm going to close the bracket and alias this as total sales
1:36
So I'm going to call it total sales and then put in a comma
1:41
So I'm going to click enter and use the run window function
1:45
So run and open and close the bracket. I'm going to use the overclose and open and close bracket and we'll order
1:53
by the sum of the sales amount columns. I'm just going to copy and contribute to paste and then I'm going to use the descending
2:02
I'm going to sort from largest to smallest. So that's why I'm using the descending order
2:07
Then I can go on and close the bracket and let's call this or alias as rank of sales
2:15
So rank of sales and then I can specify the name of the table
2:21
which is from the KQL1 table. I'm going to click enter and then I'm going to use the group by
2:28
because I have to group by the products column. So group by and then I'm going to click enter and just tab
2:37
and I'm going to group by the product. So that's all. I can go ahead and just delete
2:42
delete so I can get the code or the closing brackets up there
2:46
Then I'm going to go ahead and call the city. So let me just expand or move this down a little bit so they can see what is going on
2:54
So I can just select star from the city, we define which is the sales by products
3:02
sales by products and that's all I need to do. Put in the same column to tabulate this statement and I can go ahead and select
3:09
the query and you can see it is nicely formatted. I can go ahead and click on run to run the query and there we go
3:18
I'm going to move this up a little bit. Amazing. So you can see the microwave products is the best selling product
3:26
Of course, it is run as number one, followed by speaker and so on and so forth
3:30
Now, this total sales can be difficult to read. So what I'm going to do is to use the format function so I
3:36
can format in the proper currency symbol. So let's move down and come to this part of the code
3:43
I'm going to use the format and then open the bracket. I'm going to come to the other side, put in comma
3:47
and then I want to use currency. So inside the single quote C, close the single quote and of course
3:53
if I want to format in the GB pound inside single quote
3:57
I'm going to specify EN and then GB and close the single quote
4:01
close the format and I can go ahead and run the query again
4:05
select and run and let's see the results. Amazing. So we can see the total sales generated for
4:14
microwave product is 15,569,404 and that is run as the fourth. And of course, the speaker is 15,342,858
4:29
and of course that is run as the second. And of course you can see the laptop is the least run
4:34
with total sales of 8,923,427. And this is basically how we can go ahead
4:42
and use the rank window function to rank our data. I trust you enjoyed this video
4:49
If you do, like, share with your friends and comment. Thank you and bye for now. Cheers
#Business & Productivity Software
#Training & Certification


