SQL for Data Analysis in Google BigQuery: 13. Advanced Sales Analysis

Advanced Sales Analysis

Advanced SQL Tasks

Task #1: Year-Over-Year Sales Growth per Category

In this analytical task, we delve into understanding the sales dynamics across different product categories over time. The focus is on quantifying the year-over-year sales growth for each category, a crucial metric for evaluating business performance.

Objective: Calculate the percentage growth in sales year-over-year for each category

Expected Result: A clear representation of the year-over-year percentage growth in sales for each product category across different years.

Data Source: dataacademykz.superstore.sales_analytics
-- Creating a CTE to calculate yearly sales for each category

WITH YearlySales AS (
  SELECT
    EXTRACT(YEAR FROM Order_Date) AS Year, -- Extracting year from order dat
    Category, -- Category of the product
    SUM(Sales) AS TotalSales -- Summing up sales
  FROM
    dataacademykz.superstore.sales_analytics -- Source table
  GROUP BY
    Year, Category -- Grouping by year and category
)

SELECT
  Year, -- Year of the sale
  Category, -- Category of the product
  TotalSales, -- Total sales for the year and category
  ROUND((TotalSales - LAG(TotalSales, 1) OVER (PARTITION BY Category ORDER BY Year)) / LAG(TotalSales, 1) OVER (PARTITION BY Category ORDER BY Year) * 100, 1) AS YoYGrowth, -- Calculating year-over-year growt
FROM
  YearlySales -- Referencing the CTE
ORDER BY
  Category, Year -- Ordering by category and year
Explanation:

Think of this query as a way to track how much a store’s different product categories are growing or shrinking each year.

First, we set up a scoreboard for each year, showing total sales for each product category.

Then, we compare this year’s score to last year’s for each category. It’s like asking, “Did we sell more gadgets this year than last year? By how much?”

We calculate the percentage increase or decrease in sales for each category, comparing year to year.

It's like a growth chart for a business, showing which parts are booming and which might need a boost.

Technical Breakdown:

Let's simplify and look at this query step by step. First, we make a temporary table called YearlySales. This table helps us figure out two things for each category: what year the order was made and how much total sales were made in that year.

Then, we use this temporary table to find out how sales have grown from one year to the next for each category. We do this using something called a window function. This function is super important because it's what tells us how much the sales have increased or decreased each year for every category.

First Step (CTE - YearlySales):

The YearlySales CTE extracts the year from the order date, categorizes the data by product category, and calculates total sales for each category in each year.

SUM(Sales) AS TotalSales computes the total sales for each combination of year and category.

Window Function:

The LAG function, a type of window function, is used to retrieve the total sales of the previous year for each category (LAG(TotalSales, 1) OVER (PARTITION BY Category ORDER BY Year)).

Sales Growth Calculation:

The query calculates the year-over-year growth as a percentage by subtracting the previous year’s sales from the current year’s sales, dividing by the previous year’s sales, and multiplying by 100.

Final Query:

The main query selects the year, category, total sales from the CTE YearlySales, and calculates the year-over-year growth percentage for each category.

Result:

The output provides a year-over-year sales growth analysis for each category, ordered by category and year, offering insights into sales trends and growth patterns.

Additional Details:

LAG(TotalSales, 1) OVER (PARTITION BY Category ORDER BY Year): The LAG function is used to get the total sales of the previous year (LAG(TotalSales, 1)) for each category. It's a window function that accesses data from a previous row without needing a self-join.

(TotalSales - LAG(TotalSales, 1)) / LAG(TotalSales, 1) * 100: This calculates the percentage growth from the previous year. It subtracts last year's sales from this year's sales, divides by last year's sales, and multiplies by 100 to get a percentage.

Task #2: Category-Wise Top Performing Products in Each Region

In this task, the focus is on uncovering regional sales trends by identifying the top-performing products within each category across different regions. This kind of analysis is essential for regional market insights, helping to tailor strategies to local consumer preferences and maximize sales opportunities.

Objective: Identify the top 3 performing products in terms of sales within each category for every region.

Expected Result: A list of the top 3 products, based on sales, within each category for every region.

Data Source: dataacademykz.superstore.sales_analytics
-- Selecting top 3 selling products in each region and category

SELECT
  Region, -- Region of the sale
  Category, -- Category of the product
  Product_Name, -- Name of the product
  Sales, -- Sales amount
  RANK() OVER (PARTITION BY Region, Category ORDER BY Sales DESC) AS SalesRank, -- Ranking sales within each region and category
FROM
  dataacademykz.superstore.sales_analytics -- Source table
QUALIFY
  SalesRank <= 3 -- Limiting results to top 3 ranks
Explanation:

Imagine you’re a detective looking for the stars of the sales show in each region and category. This query is like your magnifying glass, zooming in on each region and category to pick out the top three sales champions.

We look at every region and category combo, like “Electronics in the West” or “Furniture in the South.”

For each combo, we see which products sold the most and crown the top three as our sales winners.

It’s a way of spotlighting the best performers in every corner of the sales map.

Technical Breakdown:

This SQL query is crafted to identify the top three products in terms of sales within each category for every region. Here's the technical breakdown:

Objective: The goal is to rank products based on their sales performance within each category and region, and then identify the top three performers.

Use of RANK() Window Function:

The RANK() OVER (PARTITION BY Region, Category ORDER BY Sales DESC) AS SalesRank is a key component.

It ranks products based on their sales within each combination of region and category.

The PARTITION BY clause ensures that the ranking is reset for each combination of region and category.

Filtering with QUALIFY:

The QUALIFY SalesRank <= 3 clause filters the results to include only the top three ranked products in each region-category combination.

Selection and Ordering:

The query selects region, category, product name, sales, and the calculated sales rank.

The ORDER BY clause within the window function orders products by sales in descending order for ranking.

Result:

The output presents the top three products for each category within each region, based on their sales performance.

Additional Details:

Our window function in this query is RANK() OVER (PARTITION BY Region, Category ORDER BY Sales DESC). Let’s break it down:

RANK(): This function assigns a rank to each row within a partition of the result set. The rank is based on the specified ordering, with equal values receiving the same rank but causing gaps in the subsequent ranks.

OVER (PARTITION BY Region, Category ORDER BY Sales DESC): This defines the window over which the RANK() function operates.

PARTITION BY Region, Category: This partitions (or groups) the data by Region and Category. So, the ranking will restart for each unique combination of Region and Category.

ORDER BY Sales DESC: This orders the data in descending order of Sales within each partition. The highest sales get the rank of 1, the next highest sales get the rank of 2, and so on.

What is QUALIFY Clause?

QUALIFY SalesRank <= 3: The QUALIFY clause is used in some SQL dialects (like Google BigQuery) and functions similarly to the WHERE clause but is specifically for window functions. It filters the results based on the window function's output. In this case, it filters to keep only those rows where the SalesRank is 3 or less.

This means that for each combination of Region and Category, only the top 3 products (those with the highest sales) will be included in the final result set.

Task #3: Moving Average and Sales Trend Analysis

In this intriguing task, we transform into sales detectives, aiming to uncover extraordinary sales activities for products. We're specifically interested in spotting days when sales figures notably diverged from the usual pattern. This can reveal valuable insights into consumer behavior, promotional effectiveness, or unusual market conditions.

Objective: Compare each product's daily sales to its 7-day moving average and identify days where sales significantly deviated from the average.

Expected Result: A detailed view of each product's daily sales compared to its 7-day moving average, highlighting days with significant deviations.

Data Source: dataacademykz.superstore.sales_analytics
SELECT
  Order_Date,
  Product_Name,
  Sales,
  ROUND(AVG(Sales) OVER (PARTITION BY Product_Name ORDER BY Order_Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS MovingAvgSales,
  CASE
    WHEN Sales > AVG(Sales) OVER (PARTITION BY Product_Name ORDER BY Order_Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) * 1.5 THEN 'Significantly Higher'
    WHEN Sales < AVG(Sales) OVER (PARTITION BY Product_Name ORDER BY Order_Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) * 0.5 THEN 'Significantly Lower'
  ELSE
    'Normal'
  END
    AS SalesTrend
FROM
  dataacademykz.superstore.sales_analytics
ORDER BY
  Product_Name,
  Order_Date
To simplify this query, let’s break it into two parts using a temporary table.
-- CTE for calculating moving average sales for each product

WITH
  MovingAvgSales AS (
  SELECT
    Order_Date, -- Date of the order
    Product_Name, -- Name of the product
    Sales, -- Sales amount
    ROUND(AVG(Sales) OVER (PARTITION BY Product_Name ORDER BY Order_Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS MovingAvg -- Calculating moving average of sale
  FROM
    dataacademykz.superstore.sales_analytics -- Source table
  )

SELECT
  Order_Date, -- Date of the order
  Product_Name, -- Name of the product
  Sales, -- Sales amount
  MovingAvg, -- Moving average of sales
  CASE
    WHEN Sales > MovingAvg * 1.5 THEN 'Significantly Higher' -- Classifying sales as significantly highe
    WHEN Sales < MovingAvg * 0.5 THEN 'Significantly Lower' -- Classifying sales as significantly lowe
  ELSE 'Normal' -- Classifying sales as normal
  END
    AS SalesTrend -- Trend based on comparison with moving averag
FROM
  MovingAvgSales -- Referencing the CTE
ORDER BY
  Product_Name, 
  Order_Date -- Ordering by product name and order date
Explanation:

Imagine you’re a detective with a magnifying glass, looking closely at how products are selling day by day. This query is like a special tool that lets you compare each day’s sales to the average sales over the past week.

We calculate an average ‘sales line’ for the last 7 days for each product.

Then, we see if any day’s sales jump way above or fall way below this line.

It’s like watching for waves in the ocean of sales – some days might have big waves (high sales), and others might have low tides (low sales).

Technical Breakdown:

This SQL query is designed to compare each product's daily sales with its 7-day moving average to identify significant deviations. Here's the technical breakdown:

First Step (CTE - MovingAvgSales):

The MovingAvgSales CTE calculates the moving average for each product.

The AVG(Sales) OVER (PARTITION BY Product_Name ORDER BY Order_Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) computes the average sales over the current and previous six days for each product.

Deviation Analysis:

The main query selects the order date, product name, actual sales, and the calculated moving average.

A CASE statement classifies each day's sales as 'Significantly Higher', 'Significantly Lower', or 'Normal' compared to the moving average. This is determined by whether the sales are 1.5 times greater or 0.5 times less than the moving average.

Final Query:

The query orders the results by product name and order date.

Result:

The output provides a detailed view of each product's daily sales compared to its 7-day moving average, highlighting days with significant sales trends.

Additional Details:

Let's dive deep into our MovingAvgSales CTE, our first step. Here, we calculate the moving average using this function AVG(Sales) OVER (PARTITION BY Product_Name ORDER BYOrder_Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW):

AVG(Sales) OVER (...): This is a window function that calculates the average sales.

PARTITION BY 'Product Name': The window function partitions the data by Product Name, meaning the average is calculated separately for each product.

ORDER BY 'Order Date': Within each partition, the data is ordered by Order Date. This ordering is essential for calculating a moving (or rolling) average.

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: This part defines the window frame for the average calculation. It includes the current row and the six preceding rows in the window. In other words, for each row in the dataset, the function calculates the average of sales for that day and the previous six days.

By breaking the query into two parts using a CTE, it becomes more readable and easier to manage, especially for complex data analysis tasks. The CTE provides a clear separation between the calculation of the moving average and its subsequent use in sales trend analysis.