Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using RANK()

Explore how to apply the RANK() window function in SQL to rank product categories based on sales data. Understand the use of joins, grouping, and ordering to compute rankings and learn alternative ranking methods like DENSE_RANK() and ROW_NUMBER(). This lesson helps you master ranking functions critical for intermediate SQL interviews.

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 ...