8weeksqlchallenge - Week2

Case Study #2 - Pizza Runner



Introduction

With the support of technology, Danny Uberize his pizza store to max out his revenue. Danny has provided an entity relationship diagram for the database design but need help cleaning the data and performing some basic calculations. This assistance is necessary to guide their delivery personnel more effectively and optimize the operations of Pizza Runner.


Case Study Insights


Pizza Metrics
This pizza metrics are indicator that show how users interact with main products (pizza). The metrics can give you information on the way to improve the products by time. With these question , it helps to dig deeper into the customer behavior and how the other elements as extras affecting to the final results.

Runner and Customer Experience and Pricing & Ratings
This about how effective of Pizza Runner is quantified with metrics of time, distance, rating. With these numbers , Danny can evaluate the capabilities of each runner, thereby making changes to improve delivery efficiency to bring a better experience to customers. Moreover rating systems empower customers, hold businesses accountable, drive improvement, and contribute to transparency and trust in the marketplace. When used effectively, rating systems can lead to more satisfying and mutually beneficial interactions between businesses and customers.

Ingredient Optimisation
Ingredient optimization metric is vital in the restaurant industry for cost efficiency, menu profitability, sustainability, consistency, inventory management, and customer satisfaction. It allows restaurants to manage resources effectively, adapt to market changes, and maintain a competitive edge while ensuring compliance with food safety regulations. Ultimately, it contributes to both financial success and responsible, sustainable food practices.


Or you can refer these resources for more restaurant metrics:

  • The 21 Must-Know Restaurant Metrics & How To Improve Them
  • Top 10 Customer Metrics for Restaurants Industry


    • SQL code

    The techniques that you can learn from this case study :

  • JOINS SQL : 5 Best Practices for Writing SQL JOINS
  • Temp Table : Temporary Table in SQL
  • Aggregate Functions : COUNT(), AVG() and SUM() Functions
  • Window Functions : How to use Window Functions
  • CTE : What Is a Common Table Expression (CTE) in SQL?
  • Sub query :SQL Subqueries
  • Date Function :Date and time data types and functions
  • Cross Apply, Outer Apply :What is SQL CROSS APPLY?
  • Union :Union & Union All
  • Pivot & Unpivot :SQL Server PIVOT and UNPIVOT


    • Cleaning is the vital steps before analyzing to avoid the wrong result. In this case, I do cleaning data first and insert it into a temp table.

  • Changing all the NULL and 'null' to blanks
  • Creating a clean temp table
    • Original table Image description


    Cleaned table Image description


    Click for code


    												
                                                    SELECT  order_id, 
                                                            customer_id,
                                                            pizza_id, 
                                                            CASE WHEN exclusions = '' OR exclusions like 'null' THEN NULL
                                                                ELSE exclusions END AS exclusions,
                                                            CASE WHEN extras = '' OR extras like 'null' THEN NULL
                                                                ELSE extras END AS extras, 
                                                            order_time
                                                    INTO #customer_orders -- create TEMP TABLE
                                                    FROM customer_orders;


    This is the most difficult case study in series of 8weeksqlchallenge. You have to create 5 different temp tables combining with complex skills to answer all questions. It’s quite long to generate all answers here, you can access my github link below for full code and explanation.

    My Github