I practice my SQL skills with the #8WeekSQLChallenge prepared by Danny Ma. Thank you Danny for the excellent case study. If you are also looking for materials to improve your SQL skills you can find it here and try it yourself.

Case Study #5: 🛒 Data Mart

---

Table of Contents

• Business Case
• Relationship Diagram
• Available Data
• Case Study Questions

Business Case

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.

Relationship Diagram

Available Data

Table: weekly_sales

Note: sample of data, 10 random rows

week_date	region	platform	segment	customer_type	transactions	sales
9/9/20	OCEANIA	Shopify	C3	New	610	110033.89
29/7/20	AFRICA	Retail	C1	New	110692	3053771.19
22/7/20	EUROPE	Shopify	C4	Existing	24	8101.54
13/5/20	AFRICA	Shopify	null	Guest	5287	1003301.37
24/7/19	ASIA	Retail	C1	New	127342	3151780.41
10/7/19	CANADA	Shopify	F3	New	51	8844.93
26/6/19	OCEANIA	Retail	C3	New	152921	5551385.36
29/5/19	SOUTH AMERICA	Shopify	null	New	53	10056.2
22/8/18	AFRICA	Retail	null	Existing	31721	1718863.58
25/7/18	SOUTH AMERICA	Retail	null	New	2136	81757.91

Case Study Questions

• A. Data Cleansing Steps

• B. Data Exploration

• C. Before & After Analysis

• D. Bonus Question

SQL Skills Gained

• Aggregations - functions like COUNT(), SUM(), AVG(), MIN(), MAX() to summarize data and perform calculations over grouped records;
• Joins - different types of joins (INNER JOIN, LEFT JOIN, RIGHT JON, FULL JOIN) to combine data from multiple tables;
• Window Functions (ROW_NUMBER, DENSE_RANK);
• CASE WHEN Statements;
• Data Cleaning/Preparation - SQL functions, such as TRIM(), REPLACE(), COALESCE();
• Filtering and Sorting;
• Group By & Having Clauses;
• Data Transformation - such as CAST() and CONVERT() to change data types;

---

Thank you for your attention! 🫶️

Thank you for reading. If you have any comments on my work, please let me know. My email address is [email protected].

---