Скачать или смотреть Mastering SQL: Rank Items with Combined Rankings

Learn how to calculate a master ranking in SQL by summing multiple ranking values without running into errors!
---
This video is based on the question https://stackoverflow.com/q/72888468/ asked by the user 'JessicaB12' ( https://stackoverflow.com/u/19496494/ ) and on the answer https://stackoverflow.com/a/72888604/ provided by the user 'Thorsten Kettner' ( https://stackoverflow.com/u/2270762/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL sum of multiple rank() statements

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering SQL: Rank Items with Combined Rankings

When working with SQL, creating master rankings based on multiple criteria can be a challenging task. If you find yourself needing to sum rankings of various metrics like traffic, conversion, and discounts, you might encounter some common issues. In this guide, we’ll explore a practical example of how to achieve this while avoiding typical SQL errors. Specifically, we'll tackle a scenario where the desired output is a final ranking of items based on their combined ranks.

The Problem

Imagine you have a dataset of items, and for each item, you want to create a comprehensive ranking based on three distinct metrics:

Traffic

Conversion Rate

Discount Rate

The goal is to sum the ranks of these three metrics for each item category, such as shirts and pants. However, when attempting to further rank the summed total, many users run into an SQL compilation error, particularly stating that window functions (like RANK()) cannot be nested.

Here’s a simplified version of the SQL query that resulted in an error for our scenario:

[[See Video to Reveal this Text or Code Snippet]]

The query works in getting the individual ranks but shows totals like 4, 5, 6, etc., instead of the desired ranks of 1, 2, 3.

The Solution

To avoid nesting window functions, a common approach is to utilize subqueries. This allows you to perform the calculations in stages and reference the computed ranks in an outer query. Here’s how you can achieve that:

Step 1: Set Up Your Temporary Table

First, ensure that you have your temporary table set up correctly, which includes your items and their respective metrics:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Calculate Individual Ranks Using a Subquery

Next, you can create a subquery that ranks each item based on the three metrics:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Summarize and Rank the Total

Finally, wrap the previous query in a main query to compute the total rank based on the sum of the individual ranks:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By organizing your SQL query in this way, you can effectively avoid nesting issues, accurately calculate combined rankings, and produce a final ranking that reflects the desired order based on multiple criteria.

This approach ensures that you gain a clear overview of rankings within your dataset while adhering to SQL constraints effectively. So, whether you’re dealing with products, services, or any other ranked items, leveraging subqueries is a powerful and efficient method.

By implementing these steps, you should see ranks for items like shirts and pants starting from 1 and going in the expected order.

With this newfound understanding, you can confidently apply the same principles to other scenarios where multiple rankings need to be summed and organized effectively.