SQL Challenge #1 – Danny’s Diner

Welcome to my SQL-powered analysis of Danny’s Diner! In this case study, I tackle the first challenge from the 8 Week SQL Challenge, focusing on customer preferences, ordering habits, and patterns that drive the business’s success. By diving deep into Danny’s data, I uncover insights that reveal what makes his diner a favorite spot and how data-driven strategies can optimize his operations.

Overview of the Analysis

Danny’s Diner serves a diverse range of customers, each with unique tastes and dining frequencies. The goal is to understand their behavior—how often they visit, what menu items they prefer, and which customers are the diner’s most valuable. Using SQL, I analyze various aspects of the customer journey to provide data-backed recommendations that could enhance both customer satisfaction and business profitability.

Let’s Dive In!

We are provided with a relational database where multiple tables offer various columns and thus data. We are requested to understand and analyze the table below clearly so that we can smoothly work with it later.

Our business managers come up with several questions for us to solve and analyse by means of SQL. We’ll progress question by question with their related answer of SQL queries.

  1. What is the total amount each customer spent at the restaurant?
  1. What is the total amount each customer spent at the restaurant?
  1. What was the first item from the menu purchased by each customer?
  1. What is the most purchased item on the menu and how many times was it purchased by all customers?
  1. Which item was the most popular for each customer?
  1. Which item was purchased first by the customer after they became a member?
  1. Which item was purchased just before the customer became a member?
  1. What is the total items and amount spent for each member before they became a member?
  1. If each $1 spent equates to 10 points and sushi has a 2x points multiplier – how many points would each customer have?
SQL Skills Applied

In this project, I applied various SQL functions and techniques to extract meaningful insights from Danny’s diner data, including:

  • INNER JOIN: Used to connect multiple tables and gather comprehensive customer transaction details by linking customer, menu, and order data.
  • Common Table Expressions (CTEs): Simplified complex queries by breaking down each step in the analysis. CTEs helped create logical segments for tasks like identifying frequent customers, calculating item popularity, and analyzing order frequency.
  • SUM and COUNT: Calculated total spending per customer, the frequency of orders, and the popularity of each menu item. These aggregate functions allowed for efficient analysis of customer contributions and menu demand.
  • CASE, WHEN, THEN, ELSE: Used to categorize data based on conditions, such as differentiating between loyal and occasional customers or highlighting peak hours.
  • WHERE: Filtered the data to focus on specific conditions, such as selecting orders from frequent customers or excluding null values, making the analysis more precise and relevant.
  • ROW_NUMBER and RANK: Applied these functions to rank customers by spending and order frequency, providing a clear picture of the diner’s most valuable customers and top-ordered items.
Insights & Findings

My analysis provides actionable insights that Danny can leverage to grow his business. By understanding his most loyal customers, popular dishes, and peak hours, Danny can:

  • Customer Loyalty and Behavior: SQL queries reveal that a few customers contribute a large portion of repeat visits. By categorizing them based on visit frequency, I provide insights into creating targeted loyalty programs.
  • Popular Menu Items: Through analysis, I identify top menu items, helping Danny optimize his offerings. Understanding which dishes are customer favorites can assist in tailoring the menu to customer preferences, potentially boosting satisfaction and sales.
  • Timing and Frequency of Visits: By examining timestamps, I can outline the busiest hours and days. This allows for recommendations on optimal staffing schedules, stock replenishment, and even targeted promotions during off-peak hours.
Conclusion

This case study demonstrates the power of SQL in transforming data into insights that drive business strategy. Danny’s Diner is a great example of how understanding customer behavior can lead to smarter, data-informed decisions. I invite you to dive deeper into my analysis to see the full potential of SQL and how it supports success in the dynamic world of the restaurant industry.