8 Week SQL Challenge - Case Study 5 (Before & After Analysis)

The focus for this article: 3. Before & After Analysis is to now calculate the total sales and calculate the percentage change in sales amount.

This technique is usually used when we inspect an important event and want to inspect the impact before and after a certain point in time.

Taking the week_date value of 2020-06-15 as the baseline week where the Data Mart sustainable packaging changes came into effect.

We would include all week_date values for 2020-06-15 as the start of the period after the change and the previous week_date values would be before

Using this analysis approach - answer the following questions:

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?

WITH four_week_b4_after AS (
    SELECT 
        week_date,
        WEEK(STR_TO_DATE(week_date, '%d/%m/%y')) AS week_number,
        sales,
        CASE 
            WHEN STR_TO_DATE(week_date, '%d/%m/%y') >= '2020-06-15' THEN 'After Change'
            ELSE 'Before Change'
        END AS period
    FROM weekly_sales
    WHERE STR_TO_DATE(week_date, '%d/%m/%y') BETWEEN DATE_SUB('2020-06-15', INTERVAL 4 WEEK) AND DATE_ADD('2020-06-15', INTERVAL 3 WEEK)
)
	
SELECT
	period,
    SUM(sales) AS total_sales,
    SUM(sales) - LAG(SUM(sales)) OVER (ORDER BY period DESC) AS sales_differences,
    ROUND(
		100 * 
        (SUM(sales) - LAG(SUM(sales)) OVER (ORDER BY period DESC)) / LAG(SUM(sales)) OVER (ORDER BY period DESC)
    ,2) as percent_change
FROM four_week_b4_after
GROUP BY period
ORDER BY period DESC;

1. What about the entire 12 weeks before and after?

WITH twelve_week_before_after AS (
    SELECT 
        week_date,
        WEEK(STR_TO_DATE(week_date, '%d/%m/%y')) AS week_number,
        sales,
        CASE 
            WHEN STR_TO_DATE(week_date, '%d/%m/%y') >= '2020-06-15' THEN 'After Change'
            ELSE 'Before Change'
        END AS period
    FROM weekly_sales
    WHERE STR_TO_DATE(week_date, '%d/%m/%y') BETWEEN DATE_SUB('2020-06-15', INTERVAL 12 WEEK) AND DATE_ADD('2020-06-15', INTERVAL 11 WEEK)
)

SELECT
	period,
    SUM(sales) AS total_sales,
    ROUND(
		100 * (SUM(sales) - LAG(SUM(sales)) OVER (ORDER BY period DESC)) / LAG(SUM(sales)) OVER (ORDER BY period DESC),
    2) AS percent_change
FROM twelve_week_before_after
GROUP BY period
ORDER BY period DESC;

1. How do the sale metrics for these 2 periods before and after compare with the previous years in 2018 and 2019?

WITH four_week_b4_after_per_year AS (
	SELECT
		YEAR(STR_TO_DATE(week_date, '%d/%m/%y')) AS calendar_year,
        sales,
        CASE
			WHEN WEEK(STR_TO_DATE(week_date, '%d/%m/%y')) >= WEEK('2020-06-15') THEN 'After Change' ELSE 'Below Change'
        END AS period
	FROM weekly_sales
    WHERE WEEK(STR_TO_DATE(week_date, '%d/%m/%y')) BETWEEN WEEK(DATE_SUB('2020-06-15', INTERVAL 4 WEEK)) AND WEEK(DATE_ADD('2020-06-15', INTERVAL 3 WEEK))
)

SELECT
	calendar_year,
    period,
    SUM(sales) AS total_sales,
    SUM(sales) - LAG(SUM(sales)) OVER (PARTITION BY calendar_year ORDER BY period DESC) AS sales_difference,
    ROUND(
		100 * 
			(SUM(sales) - LAG(SUM(sales)) OVER (PARTITION BY calendar_year ORDER BY period DESC)) 
			/ LAG(SUM(sales)) OVER (PARTITION BY calendar_year ORDER BY period DESC)
    , 2) AS percent_change
FROM four_week_b4_after_per_year
GROUP BY calendar_year, period
ORDER BY calendar_year, period DESC;