(A) Here, I have analyzed the data and provided a solution using MySQL queries based on the case studies organized by Steel Data and Matthew Steel.
Scenario:
Steve runs a top-end car showroom but his data analyst has just quit and left him without his crucial insights. Can you analyse the following data to provide him with all the answers he requires?
Scenario:
The top eSports competitors from across the globe have gathered to battle it out Can you analyse the following data to find out all about the tournament?
Scenario:
You are a Customer Insights Analyst for 'The General Store' Can you analyse the following tables to find out crucial information about your customers to provide to your marketing team?
Scenario:
You are a Finance Analyst working for 'The Big Bank' You have been tasked with finding out about your customers and their banking behaviour. Examine the accounts they hold and the type of transactions they make to develop greater insight into your customers.
Scenario:
You are a Pricing Analyst working for a pub chain called 'Pubs "R" Us' You have been tasked with analysing the drinks prices and sales to gain a greater insight into how the pubs in your chain are performing.
Scenario:
You are a Marketing Analyst The 'Sustainable Clothing Co.' have been running several marketing campaigns and have asked you to provide your insight into whether they have been successful or not. Analyse the following data and answer the questions to form your answer.
(B) Here, I have analyzed the data and provided a solution using MySQL queries based on the case studies organized by Danny Ma.
Introduction:
Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen.
Danny’s Diner is in need of your assistance to help the restaurant stay afloat - the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.
Problem Statement:
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. Having this deeper connection with his customers will help him deliver a better and more personalised experience for his loyal customers.
He plans on using these insights to help him decide whether he should expand the existing customer loyalty program - additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.
Danny has provided you with a sample of his overall customer data due to privacy issues - but he hopes that these examples are enough for you to write fully functioning SQL queries to help him answer his questions!
Danny has shared 3 key datasets for this case study:
sales, menu, members.
Introduction:
Did you know that over 115 million kilograms of pizza is consumed daily worldwide??? (Well according to Wikipedia anyway…)
Danny was scrolling through his Instagram feed when something really caught his eye - “80s Retro Styling and Pizza Is The Future!”
Danny was sold on the idea, but he knew that pizza alone was not going to help him get seed funding to expand his new Pizza Empire - so he had one more genius idea to combine with it - he was going to Uberize it - and 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.
Problem Statement:
Because Danny had a few years of experience as a data scientist - he was very aware that data collection was going to be critical for his business’ growth.
He has prepared for us an entity relationship diagram of his database design but requires further assistance to clean his data and apply some basic calculations so he can better direct his runners and optimise Pizza Runner’s operations.
This case study has LOTS of questions - they are broken up by area of focus including:
- Pizza Metrics
- Runner and Customer Experience
- Ingredient Optimisation
- Pricing and Ratings
- Bonus DML Challenges (DML = Data Manipulation Language)
Introduction:
Subscription based businesses are super popular and Danny realised that there was a large gap in the market - he wanted to create a new streaming service that only had food related content - something like Netflix but with only cooking shows!
Danny finds a few smart friends to launch his new startup Foodie-Fi in 2020 and started selling monthly and annual subscriptions, giving their customers unlimited on-demand access to exclusive food videos from around the world!
Danny created Foodie-Fi with a data driven mindset and wanted to ensure all future investment decisions and new features were decided using data. This case study focuses on using subscription style digital data to answer important business questions.
Problem Statement:
Problem Statement: In a rapidly growing market of subscription-based services, Foodie-Fi, a niche streaming platform offering exclusive food-related content, aims to leverage data to guide its business decisions. The challenge lies in analyzing and utilizing subscription-style digital data to address key questions such as customer retention, subscription growth, content preferences, and the impact of pricing models on revenue. By adopting a data-driven approach, Foodie-Fi seeks to optimize its offerings, improve customer experience, and sustain competitive advantage in the streaming industry.
This case study has LOTS of questions - they are broken up by area of focus including:
- Customer Journey
- Data Analysis Questions
- Challenge Payment Question
- Outside The Box Questions
Introduction:
There is a new innovation in the financial industry called Neo-Banks: new aged digital only banks without physical branches.
Danny thought that there should be some sort of intersection between these new age banks, cryptocurrency and the data world…so he decides to launch a new initiative - Data Bank!
Data Bank 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!
Problem Statement:
The rise of Neo-Banks, which are entirely digital with no physical branches, presents an exciting intersection between finance, cryptocurrency, and data management. Data Bank, a new initiative, functions not only as a digital bank but also as the world’s most secure distributed data storage platform. In this unique model, customer cloud data storage is linked to the funds in their accounts.
Objective: The management team at Data Bank aims to expand their customer base while efficiently tracking and forecasting their customers' future data storage needs. They require a robust system to calculate key metrics, predict growth, and analyze their data effectively to drive business strategy and improve operational planning.
Your task is to assist the Data Bank team by designing and implementing SQL queries that will provide insights into customer behavior, storage usage trends, and growth forecasting, helping the company optimize resources and plan for future developments.
This case study has LOTS of questions - they are broken up by area of focus including:
- Customer Nodes Exploration
- Customer Transactions
- Data Allocation Challenge
- Extra Challenge
Introduction:
In today’s market, sustainability has become a driving factor for consumers and businesses alike. Companies are increasingly adopting eco-friendly practices to meet consumer expectations and regulatory requirements. For Data Mart, an online supermarket specializing in fresh produce, a large-scale shift was introduced in June 2020, adopting sustainable packaging across the supply chain. Danny, Data Mart's founder, seeks to understand how this operational transformation has impacted sales performance across various business areas. By examining the effects of sustainable packaging on platform-specific, regional, and customer-segment sales, Danny aims to make informed decisions on future sustainability initiatives that align with customer preferences while minimizing any potential negative impact on sales.
Problem Statement:
The project aims to quantify the impact of Data Mart’s transition to sustainable packaging on its overall sales and specific business segments. This analysis will address three core business questions:
- Quantifiable Impact: What measurable effect has the shift to sustainable packaging had on Data Mart's sales performance since June 2020?
- Affected Segments: Which sales channels (platforms), regions, customer types, and product segments have been most impacted by this change?
- Strategic Recommendations: How can Data Mart effectively introduce similar sustainability updates in the future to continue promoting eco-friendly practices while minimizing any potential disruptions to sales?
This analysis will provide actionable insights to guide Data Mart’s future sustainability strategies, ensuring alignment with customer expectations and maintaining positive sales momentum.
This case study has LOTS of questions - they are broken up by area of focus including:
- weekly_sales
Introduction:
Clique Bait is revolutionizing the seafood industry by blending data-driven insights with the traditional online seafood retail model. Founded by Danny, a former digital data analytics professional, Clique Bait aspires to deliver a seamless and efficient online seafood shopping experience. The company prides itself on using data to inform decisions, optimize operations, and deliver value to customers.
As a data-driven organization, Clique Bait tracks customer behavior through its e-commerce platform. Analyzing these patterns allows the company to understand the performance of its sales funnel and improve user experiences. By identifying where customers drop off during their journey, Clique Bait aims to fine-tune its processes, increase conversions, and reinforce its position as a leading player in the seafood e-commerce industry.
Problem Statement:
Despite its innovative approach, Clique Bait is facing challenges in retaining potential customers throughout its online sales funnel. The exact stages where customer fallout occurs and the reasons behind these drop-offs are unclear.
To address this, the project seeks to:
- Analyze Funnel Fallout Rates: Examine the dataset to identify stages in the customer journey (e.g., browsing, cart addition, checkout) with the highest dropout rates.
- Provide Insights: Interpret the data to uncover potential reasons for these fallouts, such as usability issues, pricing concerns, or lack of engagement.
- Offer Solutions: Propose creative and actionable strategies to reduce dropout rates, enhance user experience, and ultimately improve conversion rates for Clique Bait.
This project aligns with Danny’s vision of leveraging data analytics to optimize customer experiences and strengthen Clique Bait’s impact in the online seafood market.
This case study has LOTS of questions - they are broken up by area of focus including:
- Users
- Events
- Event Identifier
- Campaign Identifier
- Page Hierarchy
Introduction:
Balanced Tree Clothing Co. specializes in delivering a curated range of clothing and lifestyle products for the modern adventurer. Founded by Danny, an experienced retail leader, the company focuses on blending data-driven decisions with fashion trends to better serve its customers.
As a forward-thinking organization, Balanced Tree collects detailed sales data across its product range. By analyzing this data, the company aims to strengthen merchandising decisions, optimize revenue, and enhance reporting for business stakeholders.
Problem Statement:
Despite its focus on quality and data, Balanced Tree is seeking help to better utilize its sales data for reporting and business improvement. Danny’s team needs to answer critical questions about product sales, customer purchasing behavior, and discount impacts to support better merchandising.
This project focuses on:
- Analyzing Sales Performance: Track revenue, sales by category/segment, and identify high-performing products.
- Understanding Discounts: Measure the effect of discounts on sales and revenue.
- Financial Reporting: Summarize key findings in a report for leadership and merchandising teams.
- Bonus Challenge: Rebuild product profiles using hierarchy and pricing data to enrich analysis.
This case study is designed to turn raw data into practical business insights that help Balanced Tree stay ahead in the competitive clothing market.
Case Study Areas:
- Product Sales
- Product Hierarchy
- Pricing Impact
- Member vs Non-Member Analysis
(C) Here, I have analyzed the data and provided a solution using MySQL queries based on the case studies organized by Data In Motion.
Introduction:
In this case study, we are analyzing the sales data of a retail company. The database consists of four primary tables: customers, products, orders, and order_items. These tables store detailed information about customers, the products they purchase, their orders, and the specific items within those orders. Our goal is to extract meaningful insights and answer specific business questions using SQL queries. The data spans across multiple orders made by customers over time, involving various products with different prices.
Problem Statement:
Within this corporate framework, we encounter several key challenges.
- Identify the product with the highest price.
- Determine the customer who has made the most orders.
- Calculate the total revenue per product.
- Find the day with the highest revenue.
- Identify the first order for each customer.
- Identify the top 3 customers who have ordered the most distinct products.
- Find the least purchased product in terms of quantity.
- Calculate the median order total.
- Classify each order based on its total value.
- Identify customers who have ordered the product with the highest price.
By addressing these key business questions, the retail company can gain valuable insights into product performance, customer behavior, and sales trends, enabling more informed strategic decisions and improved business outcomes.
Introduction:
In a dynamic corporate environment, managing departments, employees, and projects efficiently is essential for organizational success. The provided SQL schema represents a typical company structure with departments managed by dedicated individuals, employees engaged in various roles, and projects driving departmental objectives. Leveraging this data, we aim to address pertinent challenges to enhance operational effectiveness and employee management.
Problem Statement:
Within this corporate framework, we encounter several key challenges.
- Firstly, identifying the longest ongoing project in each department is crucial for resource allocation and project prioritization.
- Secondly, delineating employees who are not managers facilitates targeted management strategies and career progression plans.
- Thirdly, pinpointing employees hired after project commencement within their department assists in assessing recruitment efficiency and project alignment.
- Additionally, ranking employees based on hire dates enables fair performance evaluations and career trajectory planning.
- Lastly, computing the duration between consecutive employee hires within departments sheds light on workforce dynamics and succession planning opportunities.
Through comprehensive analysis and strategic insights derived from SQL queries, we aim to address these challenges effectively, fostering organizational excellence and employee satisfaction.