Mastering Rank() in BigQuery: Restarting Counts by Season

Discover how to effectively use `Rank()` in BigQuery to create a ranked list of SKU sales per season and restart the count as the season changes.
---
This video is based on the question https://stackoverflow.com/q/65728267/ asked by the user 'CoffeeCoder' ( https://stackoverflow.com/u/4488338/ ) and on the answer https://stackoverflow.com/a/65728507/ provided by the user 'rtenha' ( https://stackoverflow.com/u/11643858/ ) 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: How to use Rank() in BigQuery and restart Rank counting by timeframe?

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 Rank() in BigQuery: Restarting Counts by Season

When working with data in BigQuery, you might come across the need to create a ranked list based on sales data segmented by different timeframes, such as seasons. This can be particularly helpful if you want to analyze sales performance per SKU over several defined periods.

In this guide, we’ll explore how to effectively use the Rank() function in BigQuery to accomplish this task, ensuring that each season's ranking starts afresh.

The Problem Statement

If you're looking to generate a report that ranks SKU sales during specific seasons and resets the rankings with each new season, you're not alone. For many analysts, translating raw data into meaningful insights frequently presents challenges, especially when dealing with time-based dimensions.

Consider the following desired output:

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

The goal is to restart the ranking when the season changes, ensuring that each SKU is evaluated afresh according to its sales.

Step-by-Step Solution

To achieve this, we will break down the task into two main components:

Data Preparation: Create a SQL query to summarize sales data into seasonal groups.

Ranking Logic: Implement the RANK() function to assign ranks based on the total sales within each season.

1. Data Preparation

In this section, we first need to organize our sales data by season and sum the total sales for each SKU in the defined timeframes. The following SQL CTE (Common Table Expression) can be utilized:

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

2. Ranking Logic

Once we have the summarized data, the next step is to apply the ranking. We will use another CTE to introduce the Rank() function. This function will allow us to assign ranks within each season based on the total sales (total_spent).

Here’s the continuation of your SQL:

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

In this query, we utilized:

RANK(): This function will provide ranks based on the sales amounts within each season, restarting the count for each season due to PARTITION BY season.

ORDER BY total_spent DESC: This ensures that the SKU with the highest sales is ranked first.

Conclusion

By implementing the combination of data preparation and ranking logic in BigQuery, you can effectively create rankings of SKU sales that reset according to seasonal changes. This allows you to maintain meaningful comparisons across different periods, providing key insights into sales performance.

Feel free to adjust the date ranges and other parameters to suit your specific dataset needs. With this approach, mastering rankings in Google BigQuery can become a simple yet powerful tool in your data analytics toolkit!