sql case study data with danny

8 Week SQL Challenge

Start your SQL learning journey today!

• Case Study #1 - Danny's Diner

Danny Ma · May 1, 2021

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 with you 3 key datasets for this case study:

You can inspect the entity relationship diagram and example data below.

Entity Relationship Diagram

Example datasets.

All datasets exist within the dannys_diner database schema - be sure to include this reference within your SQL scripts as you start exploring the data and answering the case study questions.

Table 1: sales

The sales table captures all customer_id level purchases with an corresponding order_date and product_id information for when and what menu items were ordered.

Table 2: menu

The menu table maps the product_id to the actual product_name and price of each menu item.

Table 3: members

The final members table captures the join_date when a customer_id joined the beta version of the Danny’s Diner loyalty program.

Interactive SQL Session

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 have access to a dedicated SQL script in the 8 Week SQL Challenge section of the course which they can use to generate relevant temporary tables like we’ve done throughout the entire course!

Case Study Questions

Each of the following case study questions can be answered using a single SQL statement:

• What is the total amount each customer spent at the restaurant?
• How many days has each customer visited the restaurant?
• What was the first item from the menu purchased by each customer?
• What is the most purchased item on the menu and how many times was it purchased by all customers?
• Which item was the most popular for each customer?
• Which item was purchased first by the customer after they became a member?
• Which item was purchased just before the customer became a member?
• What is the total items and amount spent for each member before they became a member?
• If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?
• In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi - how many points do customer A and B have at the end of January?

Bonus Questions

Join all the things.

The following questions are related creating basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL.

Recreate the following table output using the available data:

Rank All The Things

Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.

It’s highly recommended to save all of your code in a separate IDE or text editor as you are trying to solve the problems in the provided SQL Fiddle instance above!

If you’d like to use this case study for one of your portfolio projects or in a personal blog post - please remember to link back to this URL and also don’t forget to share some LinkedIn updates using the #8WeekSQLChallenge hashtag and remember to tag me!

Ready for the next 8 Week SQL challenge case study? Click on the banner below to get started with case study #2!

I really hope you enjoyed this fun little case study - it definitely was fun for me to create!

Official Solutions

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 protected] from your educational email or include your enrolment details or student identification for a speedy response!

The following topics relevant to the Danny’s Diner case study are covered lots of depth in the Serious SQL course:

• Common Table Expressions
• Group By Aggregates
• Window Functions for ranking
• Table Joins

Don’t forget to review the comprehensive list of SQL resources I’ve put together for the 8 Week SQL Challenge on the Resources page!

Community Solutions

This section will be updated in the future with any community member solutions with a link to their respective GitHub repos!

Final Thoughts

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 [email protected] 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:

• 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 #7 - Balanced Tree Clothing Co.
• Case Study #8 - Fresh Segments

Share: Twitter , Facebook

DEV Community

Posted on Oct 11, 2022

8 Week SQL Challenge: Case Study #1 Danny’s Diner

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
• 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.

The data set contains the following 3 tables which you may refer to the relationship diagram below to understand the connection

Table Relationship

Case Study Questions

1.What is the total amount each customer spent at the restaurant? 2.How many days has each customer visited the restaurant? 3.What was the first item from the menu purchased by each customer? 4.What is the most purchased item on the menu and how many times was it purchased by all customers? 5.Which item was the most popular for each customer? 6.Which item was purchased first by the customer after they became a member? 7.Which item was purchased just before the customer became a member? 8.What is the total items and amount spent for each member before they became a member? 9.If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have? 10In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customer A and B have at the end of January?

I’m using Microsoft SQL Server and these are the functions used.

• Aggregate functions — SUM, MIN, MAX
• Numerical functions — TOP
• Joins — Inner join, left join
• Temp tables (CTE)
• Windows function
• What is the total amount each customer spent at the restaurant? We use the SUM and GROUP BY functions to find out total spent for each customer and JOIN function because customer_id is from sales table and price is from menu table.

• Customer A spent $76.
• Customer B spent $74.
• Customer C spent $36.
• How many days has each customer visited the restaurant?

Use DISTINCT and wrap with COUNT function to find out number of days customer visited the restaurant.

If we do not use DISTINCT for order_date, the number of days may be repeated. For example, if customer A visited the restaurant twice on ‘2021–01–07’, then number of days may have counted as 2 instead of 1 day.

• Customer A visited 4 times.
• Customer B visited 6 times.
• Customer C visited 2 times.
• What was the first item from the menu purchased by each customer?

First, we have to create a CTE using WITH function. In the summary CTE, we use DENSE_RANK and OVER(PARTITION BY ORDER BY) to create a new column rank based on order_date.

I chose to use DENSE_RANK instead of ROW_NUMBER or RANK as the order_date is not time stamped hence, we do not know which item is ordered first if 2 or more items are ordered on the same day.

Subsequently, we GROUP BY the columns to show rank = 1 only.

• Customer A’s first order are curry and sushi.
• Customer B’s first order is curry.
• Customer C’s first order is ramen.
• What is the most purchased item on the menu and how many times was it purchased by all customers?

• Most purchased item on the menu is ramen. Yummy!
• Which item was the most popular for each customer?

Again, we create a CTE to rank the number of orders for each product by DESC order for each customer.

Then, we generate results where rank of product = 1 only as the most popular product for individual customer.

• Customer A and C’s favourite item is ramen.
• Customer B enjoys all items in the menu. He/she is a true foodie.
• Which item was purchased first by the customer after they became a member?

Yeap, you can guess it! We’re creating another CTE.

In this CTE, we filter order_date to be on or after their join_date and then rank the product_id by the order_date.

Next, we filter the table by rank = 1 to show first item purchased by customer.

After Customer A became a member, his/her first order is curry, whereas it’s sushi for Customer B.

• Which item was purchased just before the customer became a member?

Basically this is a reversed of Question #6. Create a CTE in order

Create new column rank by partitioning customer_id by DESC order_date to find out the order_date just before the customer became member

Filter order_date before join_date.

Then, pull table to show the last item ordered by customer before becoming member.

• Customer A’s order before he/she became member is sushi and curry and Customer B’s order is sushi. That must have been a real good sushi!
• What is the total items and amount spent for each member before they became a member?

First, filter order_date before their join_date. Then, COUNT unique product_id and SUM the prices total spent before becoming member.

Answer: Before becoming members,

• Customer A spent $ 25 on 2 items.
• Customer B spent $40 on 2 items.
• If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?

Let’s breakdown the question.

• Each $1 spent = 10 points.

But, sushi (product_id 1) gets 2x points, meaning each $1 spent = 20 points So, we use CASE WHEN to create conditional statements

If product_id = 1, then every $1 price multiply by 20 points

All other product_id that is not 1, multiply $1 by 10 points

So, you can see the table below with new column, points.

Using the table above, we SUM the price, match it to the product_id and SUM the total_points.

• Total points for Customer A, B and C are 860, 940 and 360.
• In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customer A and B have at the end of January?

Again, we breakdown the question.

• Find out customer’s validity date (which is 6 days after join_date and inclusive of join_date) and last day of Jan 2021 (‘2021–01–21’).

Then, use CASE WHEN to allocate points by dates and product_name.

Our assumptions are

• Day -X to Day 1 (customer becomes member (join_date), each $1 spent is 10 points and for sushi, each $1 spent is 20 points.
• Day 1 (join_date) to Day 7 (valid_date), each $1 spent for all items is 20 points.

Day 8 to last day of Jan 2021 (last_date), each $1 spent is 10 points and sushi is 2x points. Answer:

Customer A has 1,370points.

Customer B has 820 points.

Bonus Questions

Join All The Things Recreate the table with: customer_id, order_date, product_name, price, member (Y/N)

Rank All The Things

Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.

From the analysis, we discover a few interesting insights that would be certainly useful for Danny.

• Customer B is the most frequent visitor with 6 visits in Jan 2021.
• Danny’s Diner’s most popular item is ramen, followed by curry and sushi.
• Customer A and C loves ramen whereas Customer B seems to enjoy sushi, curry and ramen equally. Who knows, I might be Customer B!
• Customer A is the 1st member of Danny’s Diner and his first order is curry. Gotta fulfill his curry cravings!
• The last item ordered by Customers A and B before they became members are sushi and curry. Does it mean both of these items are the deciding factor? It must be really delicious for them to sign up as members!
• Before they became members, both Customers A and B spent $25 and $40.
• Throughout Jan 2021, their points for Customer A: 860, Customer B: 940 and Customer C: 360.
• Assuming that members can earn 2x a week from the day they became a member with bonus 2x points for sushi, Customer A has 660 points and Customer B has 340 by the end of Jan 2021.

Thank you Danny Ma for the excellent case study! You can find it here and try it yourself.

Top comments (0)

Templates let you quickly answer FAQs or store snippets for re-use.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink .

Hide child comments as well

For further actions, you may consider blocking this person and/or reporting abuse

ReductStore CLI Client now in Rust

Alexey Timin - Apr 9

ESG: Transforming Business Practices for Environmental and Social Impact

Ashok Admin - Apr 9

🕵️‍♂️ The Art of Self-Learning: How to Teach Yourself Any Programming Concept 🤓

Lucas Lima do Nascimento - Apr 16

Employee Management System using Python.

Ebikara Dandeson Spiff - Apr 21

We're a place where coders share, stay up-to-date and grow their careers.

Data With Danny

Virtual data internship.

Learn Data Analytics, Data Science & Machine Learning under guided mentorship and deliver challenging projects in a unique data apprenticeship.

Does this sound familiar to you?

Common challenges faced by aspiring data professionals

I am super motivated to learn but I feel overwhelmed by all the different online courses, tutorials and resources to choose from

I don't know how to build a portfolio that can showcase my technical skills and impress future employers and recruiters

I have little to no access to professional mentors in my current company or network and struggle to make the right connections

I need more realistic hands on experience to improve my skills in data analytics, data visualization and machine learning

I want to know which languages and technology I should focus on so I don't waste time learning the wrong thing

I need guidance on my journey to become an outstanding data professional who can make a huge impact in this world

About Me: Danny Ma

What makes me the right person to help you?

Strong Technology Focus

Learn the tools and technology that will help you make the most impact

Learn & Retain Skills Effectively

Project Based Data Apprenticeship

• SQL Fundamentals
• Advanced Data Analysis
• Data Problem Solving
• Data Visualization
• Data Storytelling
• Presentation Skills
• Experimentation Design
• Statistical Learning
• Time Series Forecasting
• Web Scraping
• Data Structures
• NLP & Text Mining
• Machine Learning Pipelines
• Deep Learning
• Probabilistic ML
• Version Control
• Docker Containers
• Cloud Computing

Serious SQL

A comprehensive SQL learning experience

Serious SQL Student Reviews

Marianne Kohsiek

“I love the way how Danny explains SQL. He explains well and makes it all very relatable. Highly recommended!”

“This is by far the best SQL course out there to my knowledge. Danny did an excellent job with the content as well as the use cases. ”

Akshaya Parthasarathy

“Danny's course has really exceeded all of my expectations surrounding SQL. The various case studies have really pushed me to understand powerful and challenging concepts which take data analysis to a whole new level. Although certain topics may se...”

“Danny's course has really exceeded all of my expectations surrounding SQL. The various case studies have really pushed me to understand powerful and challenging concepts which take data analysis to a whole new level. Although certain topics may seem to go over the head, Danny as well as his team have been extremely welcoming and approachable for all kinds of doubts (no doubt is too silly!). Highly recommended for anyone looking to transition into the "data movement" ”

Butch Davis

“I consider myself to be an immediate to advanced user of SQL that yearns to learn more about the language in a practical manner. This course has been exactly that, a chance to use real world, applicable techniques in SQL to real world data sets in...”

“I consider myself to be an immediate to advanced user of SQL that yearns to learn more about the language in a practical manner. This course has been exactly that, a chance to use real world, applicable techniques in SQL to real world data sets in ways I never have before. This course will send your career into the next stratosphere! ”

Michael Taverner

“There are tonnes of SQL courses and most have great elements to them. Serious SQL is in a class of it's own - it's focused where it needs to be, uses broad strokes instead of belabouring a point for the sake of it, comes with a nice set of data th...”

“There are tonnes of SQL courses and most have great elements to them. Serious SQL is in a class of it's own - it's focused where it needs to be, uses broad strokes instead of belabouring a point for the sake of it, comes with a nice set of data that suits the content. I've 'learned' a lot of this syntax and these concepts before, but I swear I've learned more (in a way I feel is going to stick) in every hour of this course than I did in a few 8 - 15 hour long courses from other providers, simply by virtue of how the course has been thoughtfully constructed.”

Mahendra Kumar

“I have done many online courses but I never felt connection I have with this course. I was exposed to SQL in my high school where many commands were pushed into my mind without their practical use . Day by day my analytical skills are increasing ...”

“I have done many online courses but I never felt connection I have with this course. I was exposed to SQL in my high school where many commands were pushed into my mind without their practical use . Day by day my analytical skills are increasing which I can feel. I am now confident I will emerge as a great analyst by end of this course. Thanks Danny Ma for creating this absolute master piece. ”

William Tindan

“Danny has a unique way of explaining complex concepts. He responds to question promptly and has great delight in helping his mentees.”

Shantanu Prakash

“This course will make you think as an analyst. Here the commands have been explained basis business use cases which helps in better understanding.”

Felix Nicholas

“The course is structured well, covered all important points. It really helped me reviewing SQL! 10.5/10!! (The 0.5 extra point is from the use of memes which occasionally made me blow more air through my nose xD)”

“The name Serious SQL is aptly named. It starts off with setting up Docker, instead of a view-in-page approach with directly SQL select commands. I can immediately see this will be a serious course, with much to learn (and I have been using basic S...”

“The name Serious SQL is aptly named. It starts off with setting up Docker, instead of a view-in-page approach with directly SQL select commands. I can immediately see this will be a serious course, with much to learn (and I have been using basic SQL in my job for 2 years now!) Loving it so far, great stuff Danny!”

Laura Asara

“Very well structured and a descriptive step-by-step guide to install the needed tools!”

Eric Bremiller

“Granted, I am only a few days in and have only watched the first live stream recording, but I am very excited about the rest of this course. I was pleasantly surprised with the DWD experience thus far. From the community on discord and the fun yet...”

“Granted, I am only a few days in and have only watched the first live stream recording, but I am very excited about the rest of this course. I was pleasantly surprised with the DWD experience thus far. From the community on discord and the fun yet professional vibe that carries through all aspects of the platform, to the air of expertise that Danny speaks with, and perhaps more importantly that he speaks with without trying to boast or sound like an "expert". He just speaks from experience and its immediately clear. I am looking forward to working through all the case studies and tutorials and benefiting from any guidance Danny offers through the Discord spaces. (Also looking forward to the python course down the road!) **update. Just finished the first debugging case study and I still think this is an excellent course. I really like the written tutorials, but I also like the live videos that compliment the tutorials. I do the tutorials first. The live videos are nice to reinforce what I have been practicing but also to get commentary about things that are not in the tutorials, about the course or about data in general, which I find very valuable.”

Recommended By Experts

“Danny has a generational ability to explain complex concepts in simple terms. I firmly believe he can make machine learning understandable to anyone. During my interview with him, I was amazed at his dedication to personal growth and the the focus on the success of his students. I could listen to him speak about data for hours!”

“When facing an uncharted landscape, it's best to hire a guide. When facing a rapidly changing and advancing landscape you need a guide. Data science is exactly such a landscape, and Danny is a great guide who clearly deeply cares for members of the data community. So you might be asking - why do I need to pay Danny when there are so many free learning materials? You can only get so far with educational materials - what you need is a good mentor. Danny interacts with his students to help them grow fast and efficiently. For a low charge you gain that extra data science growth coaching from a highly seasoned practitioner who truly cares!”

“Danny has been a great source of inspiration for me. When Danny speaks on podcasts or gives talks, I always listen to him. And every time he says something, I have to pull out my notebook and take notes. This information is simply too valuable, I cannot just rely on my brain to retain it. Luckily, Danny is now putting all his knowledge into a series of courses, so everyone can benefit from his experience in an easy-to-access way. Don't miss that chance!”

Mentee Testimonials

“Danny is an excellent mentor who goes above and beyond in every thing he does. No matter how busy he is, he’s always on top of answering all my questions 1-1 and gives ton of importance to ensure I can always ask him for help. Danny also was super supportive and provided ample guidance to help me navigate my job search during the pandemic. I continue to get inspired and learn something new everyday from him. Danny is extremely kind, compassionate with a truck load of knowledge - the definition of a true unicorn!”

“I cannot recommend Danny enough. He is the best mentor I have come across. He is so supportive and pushes you to do well. He gives his all to ensure that you get the correct advice and amazing resources to learn from on your data journey. I think he is on a mission to help people and encourage them to make progress. It is inspiring to see his passion towards data and the way he helps people. We need more people like Danny in the data community.”

“Danny has put hundreds of hours of thought into his courses, and it shows. I feel grateful to be working with someone who has thoroughly considered every aspect of what it takes to break into data analytics and data science -- including the importance of soft skills and creating a portfolio. I can confidently say that he leaves no stone unturned when it comes to helping his mentees develop and demonstrate their ability in the most effective way possible, and can't speak highly enough of his goal to make courses accessible for all levels. Despite starting my study at a beginner level, I feel confident that I can learn and retain new skills, apply them in real-life situations, and further develop the knowledge I have gained through Danny's mentorship.”

No! I will show you how simple it is to get started on your data journey with zero background knowledge and will get you up and running, answering business questions and solving problems with data right from the very beginning!

No! But you should have a machine with minimum 4GB of RAM on Windows 10 Home or MacOS 10.13 or newer (High Sierra, Catalina or Mojave) Linux support is also provided for Ubuntu Focal 20.04, Bionic 18.04 or Xenial 16.04

Honestly this will vary from person to person based on existing levels of experience and the amount of time you can commit. The best estimates at this stage would be roughly 4-12 weeks per course. Each course will cover the fundamentals required to learn from specific skills and tools from scratch and will quickly progress you through to the implementation of fast-paced case studies and additional bonus content.

Please send an email to [email protected] with any additional questions!

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

Case study solutions for #8WeekSQLChallenge at https://8weeksqlchallenge.com

joyceannie/SQL-Data-with-Danny-Case-Studies

Folders and files, repository files navigation, 🍔 8 week sql challenge.

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.

View the case study here and my solution here .

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