Danny, fueled by his profound love for Japanese cuisine, embarked on the restaurant venture in 2021. Eager to comprehend the dynamics of his budding business, he accumulated a trove of data, seeking to gain insights into the performance of his loyalty program. Additionally, he aimed to create user-friendly data tables that would provide a snapshot of the restaurant’s pulse without the need for SQL expertise.
Danny shared three essential datasets: sales, menu, and members, all residing within the dannys_diner schema.
- Sales: This dataset serves as a ledger of customer-level purchases, meticulously tracking order dates and the products that graced each order.
- Menu: The menu featuring product IDs, actual product names, and their corresponding prices.
- Members: A list of patrons who embraced Danny’s Diner’s loyalty program, each tagged with their ‘join_date.’ Notably, this dataset includes customers who are also members, categorized into Group A and Group B.
In this project, I used SQL to assist Danny in answering questions for his business. I-
- Developed a SQL query to calculate the total amount spent by each customer, providing valuable insights into customer spending behavior.
- Created a SQL query to determine the number of days each customer visited the restaurant, offering insights into customer engagement and loyalty.
- Constructed a SQL query to identify the first menu item purchased by each customer, revealing initial customer preferences and purchase patterns.
- Analyzed restaurant sales data to identify the most popular menu items and the number of times they were purchased by customers, aiding in menu optimization and business decisions.
- Successfully determined the most popular menu item for each customer by developing a SQL query that applied DENSE_RANK(), facilitating personalized menu recommendations.
- Developed SQL queries to identify menu items purchased immediately before and after customers joined the membership program, enabling tailored marketing strategies.
- Created a SQL query to calculate the total items and amount spent by members before they joined the program, providing insights into member spending and preferences.
- Applied a SQL query to calculate customer loyalty points, accounting for special promotions and 2x points on specific items.
- Developed a SQL query to calculate points earned by customers in the first week after joining the program, considering 2x points for the initial period, supporting customer engagement and reward tracking.
You can read the report here.