SQL CTE to Rank Sales by Products using Fabric Data Warehouse

In this Microsoft Fabric Data Warehouse video

I'm going to show you how to auto common table expression query popularly known as City Equity

I'm going to use the rank window function with the over clause to rank sales amount by products

So let's get started. We're going to use the table called KQL1 in my Data Warehouse of Fabric

Of course, this is simple select star which select or return all the columns in the KQL1 table

Of course, you can see we have the date, the year, product, payment type

unit, price, and the sales amount column. Go ahead and auto this query

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

So I'm going to use the width and then I can call the name of the city as sales by products

You can actually use anything like that is totally fine. I'm going to use the Rs and then open and close the brackets

So inside open and close bracket, I'm going to expand. So I can go ahead and select my column

So I'm going to use this select and I want to select the product column

So tabs, so I'm going to call it product and then go to the comma

and then I'm going to jump to the next line to make the code readable and easy to understand

Now, I'm going to use the sum aggregate function and I want to aggregate the sales

I want columns, I want to call it sales amount, and then I'm going to double-click on that

Then I'm going to close the bracket and alias this as total sales

So I'm going to call it total sales and then put in a comma

So I'm going to click enter and use the run window function

So run and open and close the bracket. I'm going to use the overclose and open and close bracket and we'll order

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

I'm going to sort from largest to smallest. So that's why I'm using the descending order

Then I can go on and close the bracket and let's call this or alias as rank of sales

So rank of sales and then I can specify the name of the table

which is from the KQL1 table. I'm going to click enter and then I'm going to use the group by

because I have to group by the products column. So group by and then I'm going to click enter and just tab

and I'm going to group by the product. So that's all. I can go ahead and just delete

delete so I can get the code or the closing brackets up there

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

So I can just select star from the city, we define which is the sales by products

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

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

I'm going to move this up a little bit. Amazing. So you can see the microwave products is the best selling product

Of course, it is run as number one, followed by speaker and so on and so forth

Now, this total sales can be difficult to read. So what I'm going to do is to use the format function so I

can format in the proper currency symbol. So let's move down and come to this part of the code

I'm going to use the format and then open the bracket. I'm going to come to the other side, put in comma

and then I want to use currency. So inside the single quote C, close the single quote and of course

if I want to format in the GB pound inside single quote

I'm going to specify EN and then GB and close the single quote

close the format and I can go ahead and run the query again

select and run and let's see the results. Amazing. So we can see the total sales generated for

microwave product is 15,569,404 and that is run as the fourth. And of course, the speaker is 15,342,858

and of course that is run as the second. And of course you can see the laptop is the least run

with total sales of 8,923,427. And this is basically how we can go ahead

and use the rank window function to rank our data. I trust you enjoyed this video

If you do, like, share with your friends and comment. Thank you and bye for now. Cheers