case study #1 danny's diner answers

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

Introducing Catepedia: A Nuxt 3 Cat Breed Search App

Rajesh Royal - Apr 8

Form and Function: How I Lost My Submit Button & Got It Back

Nick Taylor - Apr 2

CI/CD Pipeline for React Native

Riddhesh - Mar 15

Maximizing Value: A Guide to Cost Optimization in Microsoft Azure

Yasir Rehman - Mar 27

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

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 Danny's Diner #8weeksqlchallenge

kelechiu/Dannys_Diner_SQL

Folders and files, repository files navigation, case study #1 - danny's diner, 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:

Case Study Questions

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

• Moscow Tourism
• Moscow Holiday Rentals
• Moscow Photos
• All Moscow Hotels
• Moscow Hotel Deals
• Moscow Motels
• Moscow Campsites
• Moscow Hostels
• Business Hotels Moscow
• Spa Hotels in Moscow
• Family Hotels Moscow
• Luxury Hotels in Moscow
• Romantic Hotels in Moscow
• Moscow Green Hotels
• Moscow Ski-In / Ski-Out Hotels
• Moscow Resorts
• 5-stars Hotels in Moscow
• 4-stars Hotels in Moscow
• 3-stars Hotels in Moscow
• Novotel Hotels in Moscow
• Crowne Plaza Hotels in Moscow
• Rotana Hotels in Moscow
• Accor Hotels in Moscow
• InterContinental (IHG) Hotels in Moscow
• Radisson Hotels in Moscow
• Hilton Hotels in Moscow
• Holiday Inns in Moscow
• ibis Hotels in Moscow
• Radisson Blu Hotels in Moscow
• Hampton by Hilton Hotels in Moscow
• AZIMUT Hotels in Moscow
• Moscow Hotels with a Pool
• Pet Friendly Hotels in Moscow
• Moscow Hotels with Parking
• 3rd Transport Ring (TTK) Hotels
• District Central (TsAO) Hotels
• Garden Ring Hotels
• Tverskoy Hotels
• Boulevard Ring Hotels
• Zamoskvorechye Hotels
• Meshchanskiy Hotels
• Arbat Hotels
• Presnensky Hotels
• District Eastern (VAO) Hotels
• Boutique Hotels in Moscow
• Downtown Moscow Hotels
• Hotels with Smoking Rooms in Moscow
• Cool & Unique Hotels Moscow
• Modern Hotels in Moscow
• Affordable Hotels in Moscow
• Moscow Hotels with Gym
• Moscow Hotels with Tennis Courts
• Moscow Shopping Hotels
• Moscow Hotels with Fireplaces
• Things to Do
• Restaurants
• Holiday Rentals
• Travel Stories
• Add a Place
• Travel Forum
• Travellers' Choice
• Help Centre

Does the ticket machines accept cards or just...

• Europe    
• Russia    
• Central Russia    
• Moscow    
• Things to do in Moscow    
• Moscow Metro

Moscow Metro Questions & Answers

Does the ticket machines accept cards or just money?

Yes they accept credit cards.

Yes same here. Just got a card with 20 rides. At the counter as the machine was not working for me.

Small bills are better.But it was bit complicated for me and I used to buy tickets over the counter.

Browse nearby

Ask a question.

Get quick answers from Moscow Metro staff and past visitors.

Simulation of the sulfide phase formation in a KhN60VT alloy

• Simulation of Metallurgical and Thermal Processes
• Published: 23 September 2017
• Volume 2017 , pages 447–453, ( 2017 )

Cite this article

• I. V. Kabanov 1 ,
• E. V. Butskii 1 ,
• K. V. Grigorovich 2 &
• A. M. Arsenkin 2  

33 Accesses

Explore all metrics

The conditions of the existence of sulfide phases in Fe–Ni–S alloys and four-component Fe–50 wt % Ni–0.001 wt % S– R ( R is an alloying or impurity element from the TCFE7 database) systems are studied using the Thermo-Calc software package and the TCFE7 database. The modification of nickel superalloys by calcium or magnesium is shown to increase their ductility due to partial desulfurization, the suppression of the formation of harmful sulfide phases, and the uniform formation of strong sulfides in the entire temperature range of metal solidification. The manufacturability of superalloys can decrease at a too high calcium or magnesium content because of the formation of intermetallics with a low melting temperature along grain boundaries.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Price includes VAT (Russian Federation)

Instant access to the full article PDF.

Rent this article via DeepDyve

Institutional subscriptions

Similar content being viewed by others

Modeling Thermophysical Characteristics of Nickel-Based Superalloys

A. A. Glotka, S. V. Haiduk & V. Yu. Ol’shanetskii

Phase field modeling of solidification in multi-component alloys with a case study on the Inconel 718 alloy

Michael Fleck, Frank Querfurth & Uwe Glatzel

Phase-Field Simulation of Microstructural Evolution in Nickel-Based Superalloys During Creep and in Low Carbon Steels During Martensite Transformation

H.-P. Chen, R. K. Kalia, E. Kaxiras, G. Lu, A. Nakano, N. Kenichi, A.C.T. van Duin, P. Vashishta, and Z. Yuan, Physical Review Letters, No. 104, 155502 (2010).

Article   Google Scholar  

F. Kristofory, M. Mohila, D. Mikulas, and J. Vitec, Acta Metallurgica Slovacia 10 (3), 236–241 (2004).

Google Scholar  

J. Morscheiser, L. Thönnessen, and B. Friedrich, “Sulphur Control in Nickel-Based Superalloy Production,” in Proceedings of Conference on EMC (2011), pp. 1–15.

Software Package. Thermodynamic Calculations of Phase Diagrams for Multicomponent Systems . http://www.thermocalc.com.

Download references

Author information

Authors and affiliations.

OAO Metallurgical Plant Elektrostal’, Elektrostal’, Moscow oblast, Russia

I. V. Kabanov & E. V. Butskii

Baikov Institute of Metallurgy and Materials Science, Russian Academy of Sciences, Moscow, Russia

K. V. Grigorovich & A. M. Arsenkin

You can also search for this author in PubMed   Google Scholar

Corresponding author

Correspondence to E. V. Butskii .

Additional information

On the Centenary of Plant “Elektrostal”

Original Russian Text © I.V. Kabanov, E.V. Butskii, K.V. Grigorovich, A.M. Arsenkin, 2017, published in Elektrometallurgiya, 2017, No. 3, pp. 13–21.

Rights and permissions

Reprints and permissions

About this article

Kabanov, I.V., Butskii, E.V., Grigorovich, K.V. et al. Simulation of the sulfide phase formation in a KhN60VT alloy. Russ. Metall. 2017 , 447–453 (2017). https://doi.org/10.1134/S0036029517060106

Download citation

Received : 21 November 2016

Published : 23 September 2017

Issue Date : June 2017

DOI : https://doi.org/10.1134/S0036029517060106

Share this article

Anyone you share the following link with will be able to read this content:

Sorry, a shareable link is not currently available for this article.

Provided by the Springer Nature SharedIt content-sharing initiative

• addition of calcium and magnesium
• strong sulfides
• grain boundaries
• intermetallics
• melting temperature
• Find a journal
• Publish with us
• Track your research