SQL Interview Preparation—Intermediate Level/

...

/

Answer: Using RANK()

Answer: Using RANK()

Find a detailed explanation of using RANK() in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to rank the records using RANK()
SELECT   PC.CategoryName, 
         SUM(S.SalesAmount) AS TotalSales,
         RANK() OVER (ORDER BY SUM(S.SalesAmount) DESC) AS 'Rank'
FROM     ProductCategories AS PC
JOIN     Products AS P ON PC.CategoryID = P.CategoryID
JOIN     Sales AS S ON P.CategoryID = S.CategoryID
GROUP BY PC.CategoryName
ORDER BY TotalSales DESC;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns CategoryName and TotalSales. The RANK() function assigns a rank to each category based on TotalSales in descending order. We use AS to set an alias for the calculated column.

  • Line 5: The FROM clause specifies the table name as ProductCategories.

  • Line 6: The JOIN is applied to the Products table on the CategoryID columns in the ProductCategories and Products tables.

  • Line 7: The JOIN is applied with Sales on CategoryID columns in the Products and Sales tables.

  • Line 8: The GROUP BY clause groups the results by CategoryName.

  • Line 9: The ORDER BY clause sorts the results by the TotalSales column in descending order.

Recalling relevant concepts

We have covered the following concepts in this question:

  • Selective columns ...