Danny's Diner

Business Goal: Examine and analyze the data provided by Danny and find out about the customer visiting patterns, how much money they’ve spent and also which menu items are their favorite to help the restaurant stay afloat. I created tables in SQL with the help of data provided by Danny and then answered the following 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?

10.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?

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.

Customer A is the 1st member of Danny’s Diner and his first order is curry.

The last item ordered by Customers A and B before they became members are sushi and curry.

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.

SQL concepts used: CTEs, Aggregations, Table Joins, Windows Function for ranking