8weeksqlchallenge - Week 5

Case Study #5 - Data Mart



Introduction

Denny is operating an online supermarket with sustainable products, especially fresh produce. From June 2020, He had started using sustainable packing methods and he need use analyze data to quantify of this strategy to the supermarket's sale performance

Case Study Insights

When a venture starts a new business strategy, they need some specific metrics to review how success of strategy is ,and keep deploying strategy to better version.

For 3 specific of business metric questions that Denny is heading to :

  • What was the quantifiable impact of the changes introduced in June 2020?
  • Which platform, region, segment and customer types were the most impacted by this change?
  • What can we do about future introduction of similar sustainability updates to the business to minimize impact on sales?

    • At the end of project, Evaluation is one of the key metrics to measure how success of project . The other elements of success project can can be referred here :
  • How To Measure the Success of a Project (With Tips)
  • 7 powerful ways to measure project success

    • SQL code

    The techniques that you can learn from this case study :

  • JOINS SQL : 5 Best Practices for Writing SQL JOINS
  • Aggregate Functions : COUNT(), AVG() and SUM() Functions
  • Window Functions : How to use Window Functions
  • CTE : What Is a Common Table Expression (CTE) in SQL?
  • Date Functions : Date and time data types and functions
  • Union & Union All Function : SQL UNION Operator


    • Let’s get through some questions of this study case to review of technique.

    Part 2.Data Exploration
    2. What range of week numbers are missing from the dataset?

  • Because of SQL Server does not support to generate a Series values in one column, so we need to apply Union in CTE to create a table with one column from 1 to 52.
  • After we need only to use LEFT JOIN to find missing weeks

    • Click for code

    												
                                                    WITH numbers_week AS (
                                                        SELECT 52 as week_number_year
                                                        UNION all
                                                        SELECT week_number_year - 1
                                                        FROM numbers_week
                                                        WHERE week_number_year > 1
                                                       ),
                                                  
                                                  WEEK_data as (
                                                      SELECT DISTINCT week_number
                                                      FROM clean_weekly_sales)
                                                  
                                                  SELECT COUNT(*) AS missing_weeks
                                                  FROM numbers_week w1 
                                                  LEFT JOIN WEEK_data w2 ON w1.week_number_year = w2.week_number
                                                  WHERE week_number is NULL;

    Part 3.Before & After Analysis

    1.What is the total sales for the 4 weeks before and after 2020-06-15? What is the growth or reduction rate in actual values and percentage of sales?

    Before we start, we find out the week_number of 2020-06-15 so that we can use it for filtering.

    Click for code

    												
                                                    SELECT  DISTINCT week_number
                                                    FROM clean_weekly_sales
                                                    WHERE week_date = '2020-06-15';

    The result shows 2020-06-15 is 25th week. So we need to calculate sales of 21-24, 25-28

    Click for code

    												
                                                    WITH CTE AS (
                                                        SELECT 
                                                                SUM (CASE WHEN week_number BETWEEN 21 AND 24 THEN sales END) AS before_change,
                                                                SUM (CASE WHEN week_number BETWEEN 25 AND 28 THEN sales END) AS after_change
                                                        FROM clean_weekly_sales
                                                        WHERE calendar_year = '2020' 
                                                                AND week_number BETWEEN 21 AND 28
                                                                )
                                                
                                                SELECT *, 
                                                        (after_change - before_change ) AS sale_change,
                                                        100 * (cast(after_change as float) - before_change) / before_change as rate_change
                                                FROM CTE ;
    Result Image description

    Since the new sustainable packaging came into effect, the sales has dropped by $26,884,188 at a negative 1.146%. A new packaging isn't the best idea - as customers may not recognize your product's new packaging on the shelves!

    We have total 13 questions in 4 Parts. You can access my github link for full code and explanation

    My Github