Data Mart is Danny’s latest venture and after running international operations for his online supermarket that specialises in fresh produce - Danny is asking for your support to analyse his sales performance.
In June 2020 - large scale supply changes were made at Data Mart. All Data Mart products now use sustainable packaging methods in every single step from the farm all the way to the customer.
Danny needs your help to quantify the impact of this change on the sales performance for Data Mart and it’s separate business areas.
The key business question he wants you to help him answer are the following:
- 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 minimise impact on sales?
For this case study there is only a single table:
Entity Relationship Diagram is shown below with the data types made clear, please note that there is only this one table - hence why it looks a little bit lonely!
The columns are pretty self-explanatory based on the column names but here are some further details about the dataset:
- Data Mart has international operations using a multi-
- Data Mart has both, a retail and online
platformin the form of a Shopify store front to serve their customers
customer_typedata relates to personal age and demographics information that is shared with Data Mart
transactionsis the count of unique purchases made through Data Mart and
salesis the actual dollar amount of purchases
Each record in the dataset is related to a specific aggregated slice of the underlying sales data rolled up into a
week_date value which represents the start of the sales week.
10 random rows are shown in the table output below from
Interactive SQL Instance
You can use the embedded DB Fiddle below to easily access these example datasets - this interactive session has everything you need to start solving these questions using SQL.
You can click on the
Edit on DB Fiddle link on the top right hand corner of the embedded session below and it will take you to a fully functional SQL editor where you can write your own queries to analyse the data.
You can feel free to choose any SQL dialect you’d like to use, the existing Fiddle is using PostgreSQL 13 as default.
Serious SQL students will have access to the same relevant schema SQL and example solutions which they can use with their Docker setup from within the course player!
Case Study Questions
The following case study questions require some data cleaning steps before we start to unpack Danny’s key business questions in more depth.
1. Data Cleansing Steps
In a single query, perform the following operations and generate a new table in the
data_mart schema named
week_numberas the second column for each
week_datevalue, for example any value from the 1st of January to 7th of January will be 1, 8th to 14th will be 2 etc
month_numberwith the calendar month for each
week_datevalue as the 3rd column
calendar_yearcolumn as the 4th column containing either 2018, 2019 or 2020 values
Add a new column called
age_bandafter the original
segmentcolumn using the following mapping on the number inside the
|3 or 4||Retirees|
- Add a new
demographiccolumn using the following mapping for the first letter in the
nullstring values with an
"unknown"string value in the original
segmentcolumn as well as the new
Generate a new
avg_transactioncolumn as the
salesvalue divided by
transactionsrounded to 2 decimal places for each record
2. Data Exploration
- What day of the week is used for each
- What range of week numbers are missing from the dataset?
- How many total transactions were there for each year in the dataset?
- What is the total sales for each region for each month?
- What is the total count of transactions for each platform
- What is the percentage of sales for Retail vs Shopify for each month?
- What is the percentage of sales by demographic for each year in the dataset?
demographicvalues contribute the most to Retail sales?
- Can we use the
avg_transactioncolumn to find the average transaction size for each year for Retail vs Shopify? If not - how would you calculate it instead?
3. Before & After Analysis
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.
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:
- 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?
- What about the entire 12 weeks before and after?
- How do the sale metrics for these 2 periods before and after compare with the previous years in 2018 and 2019?
4. Bonus Question
Which areas of the business have the highest negative impact in sales metrics performance in 2020 for the 12 week before and after period?
Do you have any further recommendations for Danny’s team at Data Mart or any interesting insights based off this analysis?
This case study actually is based off a real life change in Australia retailers where plastic bags were no longer provided for free - as you can expect, some customers would have changed their shopping behaviour because of this change!
Analysis which is related to certain key events which can have a significant impact on sales or engagement metrics is always a part of the data analytics menu. Learning how to approach these types of problems is a super valuable lesson and hopefully these ideas can help you next time you’re faced with a tough problem like this in the workplace!
Ready for the next 8 Week SQL challenge case study? Click on the banner below to get started with case study #6!
If you’d like to see the official code solutions and explanations for this case study and a whole lot more, please consider joining me for the Serious SQL course - you’ll get access to all course materials and I’m on hand to answer all of your additional SQL questions directly!
Serious SQL is priced at $49USD and $29 for students and includes access to all written course content, community events as well as live and recorded SQL training videos!
Please send an email to email@example.com from your educational email or include your enrolment details or student identification for a speedy response!
This section will be updated in the future with any community member solutions with a link to their respective GitHub repos!
The 8 Week SQL Challenge is proudly brought to you by me - Danny Ma and the Data With Danny virtual data apprenticeship program.
Students or anyone undertaking further studies are eligible for a $20USD student discount off the price of Serious SQL please send an email to firstname.lastname@example.org from your education email or include information about your enrolment for a fast response!
We have a large student community active on the official DWD Discord server with regular live events, trainings and workshops available to all Data With Danny students, plus early discounted access to all future paid courses.
There are also opportunities for 1:1 mentoring, resume reviews, interview training and more from myself or others in the DWD Mentor Team.
From your friendly data mentor, Danny :)
All 8 Week SQL Challenge Case Studies
All of the 8 Week SQL Challenge case studies can be found below: