8-weeks-challenge-with-SQL
This repository contains the solution for the 8 case studies in #8WeekSQLChallenge!
Thanks @DataWithDanny for the excellent SQL case studies! 👋🏻
đź“š Table of Contents
- Case Study #1: Danny's Diner
- Case Study #2: Pizza Runner
- Case Study #3: Foodie-Fi
- Case Study #4: Data Bank
- Case Study #5: Data Mart
- Case Study #6: Clique Bait
- Case Study #8: Fresh Segments
Case Study #1: Danny's Diner
View the case study here and my solution here and on Medium.
Business Task
Danny wants to use the data to answer a few simple questions about his customers, especially about their visiting patterns, how much money they’ve spent and also which menu items are their favourite.
Entity Relationship Diagram
Insights and Findings
Case Study #2: Pizza Runner
View the case study here and my solution here and on Medium.
Business Task
Danny is expanding his new Pizza Empire and at the same time, he wants to Uberize it, so Pizza Runner was launched!
Danny started by recruiting “runners” to deliver fresh pizza from Pizza Runner Headquarters (otherwise known as Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers.
Entity Relationship Diagram
Insights and Findings
Case Study #3: Foodie-Fi
View the case study here and my solution here and on Medium.
Business Task
Danny and his friends launched a new startup Foodie-Fi and started selling monthly and annual subscriptions, giving their customers unlimited on-demand access to exclusive food videos from around the world.
This case study focuses on using subscription style digital data to answer important business questions on customer journey, payments, and business performances.
Entity Relationship Diagram
Insights and Findings
Case Study #4: Data Bank
View the case study here and my solution here and on [Medium].
Business Task
Danny launched a new initiative, Data Bank which runs just like any other digital bank - but it isn’t only for banking activities, they also have the world’s most secure distributed data storage platform!
Customers are allocated cloud data storage limits which are directly linked to how much money they have in their accounts. There are a few interesting caveats that go with this business model, and this is where the Data Bank team need your help!
The management team at Data Bank want to increase their total customer base - but also need some help tracking just how much data storage their customers will need.
This case study is all about calculating metrics, growth and helping the business analyse their data in a smart way to better forecast and plan for their future developments!
Entity Relationship Diagram
Insights and Findings
Case Study #5: Data Mart
View the case study here and my solution here and on [Medium].
Business Task
Data Mart is an online supermarket that specialises in fresh produce.
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 analyse and quantify the impact of this change on the sales performance for Data Mart and it’s separate business areas.
The key business question to 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?
Entity Relationship Diagram
Here are some further details about the dataset:
- Data Mart has international operations using a multi-
regionstrategy. - Data Mart has both, a retail and online
platformin the form of a Shopify store front to serve their customers. - Customer
segmentandcustomer_typedata relates to personal age and demographics information that is shared with Data Mart. transactionsis the count of unique purchases made through Data Mart andsalesis 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.
Insights and Findings
Case Study #6: Clique Bait
View the case study here and my solution here and on [Medium].
Business Task
Clique Bait is an online seafood store. In this case study - you are required to support the founder and CEO Danny’s vision and analyse his dataset and come up with creative solutions to calculate funnel fallout rates for the Clique Bait online store.
Entity Relationship Diagram
Insights and Findings
Case Study #8: Fresh Segments
View the case study here and my solution [here] and on [Medium].
Business Task
Fresh Segments is a digital marketing agency that helps other businesses analyse trends in online ad click behaviour for their unique customer base.
Clients share their customer lists with the Fresh Segments team who then aggregate interest metrics and generate a single dataset worth of metrics for further analysis.
In particular - the composition and rankings for different interests are provided for each client showing the proportion of their customer list who interacted with online assets related to each interest for each month.
Danny has asked for your assistance to analyse aggregated metrics for an example client and provide some high level insights about the customer list and their interests.