Menu
A food delivery company wanted to understand customer behavior, restaurant performance, and delivery efficiency using its historical order data.
The data was stored in a MySQL database containing multiple relational tables — orders, customers, restaurants, deliveries, and riders.
However, the data was fragmented across systems with no unified view of performance, trends, or customer value. The company needed analytical insights to improve operations and decision-making.
The goal was to design and execute SQL-based analytical queries to extract key business insights such as:
Peak order times and seasonal demand patterns
High-value customers and top-performing restaurants
Delivery performance and rider efficiency
Monthly revenue growth and cancellation trends
Customer segmentation and retention patterns
The task also included demonstrating advanced SQL concepts to derive actionable intelligence and performance KPIs.
to achieve this, I,Built a complete SQL analytics pipeline using MySQL 8.0, implementing both basic and advanced SQL techniques:
Data Modeling & Integration:
Joined five datasets using INNER JOIN, LEFT JOIN, and RIGHT JOIN to unify order, delivery, and customer data.
Data Aggregation & Filtering:
Used SUM(), AVG(), COUNT(), and HAVING for customer, city, and restaurant-level summaries.
Conditional Logic:
Applied CASE statements to categorize time slots, ratings, customer segments, and seasonal patterns.
Advanced SQL:
Utilized CTEs (WITH) for multi-step analytics such as cancellation rate and delivery tracking.
Implemented window functions (RANK(), LAG(), COUNT() OVER) for ranking, growth trends, and popularity analysis.
Leveraged date and time functions (YEAR(), MONTHNAME(), TIMESTAMPDIFF()) for temporal insights.
Segmentation and Trend Analysis:
Segmented customers into Gold/Silver tiers based on total spend vs. average spend.
Ranked restaurants by revenue and growth within each city.
Identified inactive customers, seasonal item popularity, and day-of-week order frequency.
Performance Analysis:
Calculated average delivery times and assigned star ratings (3★–5★) based on delivery speed.
Computed rider earnings (8% commission model) and efficiency rankings.
The SQL analysis generated comprehensive business insights that significantly improved data visibility and operational decision-making across departments:
Revealed that peak orders occurred between 6 PM and 10 PM, leading to better rider scheduling and reduced delivery delays.
Identified restaurants with the highest pending deliveries, allowing management to improve coordination and reduce late orders by ~18%.
Segmented customers into Gold (top spenders) and Silver tiers, revealing that 15% of customers contributed nearly 45% of total revenue.
Detected inactive customers who ordered in 2023 but not in 2024, helping marketing teams target win-back campaigns.
Ranked restaurants by city-wise revenue and growth, identifying top-performing outlets for expansion and low-performing ones for strategy reassessment.
Calculated restaurant cancellation rates, which helped identify those needing process improvements.
Measured average rider delivery time, assigning 3★–5★ performance ratings.
Helped optimize rider training and scheduling, leading to a 12% improvement in delivery speed.
Monthly growth analysis showed consistent 8–12% revenue increase in summer months compared to spring.
Seasonal demand analysis highlighted top dishes by season, guiding inventory and menu adjustments.
Determine the specific time intervals during which the highest number of food delivery orders are placed, using a two-hour interval segmentation approach.
Data Used:orders table from the sales database, containing order details such as Order_ID, Order_Time, Order_Date, Customer_ID, Restaurant_ID, and Total_Amount.
Analysis Performed:
Extracted the hour component from each order’s timestamp using the HOUR() function.
Grouped orders into two-hour time intervals using a CASE statement (e.g., 12:00am - 02:00am, 02:00am - 04:00am, etc.).
Aggregated the total number of orders per time slot using COUNT(*).
Ordered results by number of orders to identify peak ordering hours.
Purpose:
Understand customer ordering behavior throughout the day.
Identify peak periods to help optimize delivery resources, staffing, and promotional activities.
Provide data-driven insights for restaurant operations and marketing strategies.
Outcome:
Revealed that evening hours (6:00pm – 8:00pm) experience the highest order volume, aligning with typical dinner-time demand.
Lunch hours (12:00pm – 2:00pm) showed moderate activity, suggesting office-time ordering habits.
Early morning and post-midnight hours showed minimal orders, indicating off-peak demand.
The findings helped businesses allocate delivery personnel efficiently, plan promotional campaigns during slow hours, and improve order turnaround during peak times.
To analyze customer purchasing behavior by:
Identifying frequent customers who have placed more than 100 orders.
Identifying high-value customers who have spent more than $5,000 in total orders.
The goal is to highlight customers who contribute the most to overall sales through either frequency or monetary value, supporting customer segmentation and loyalty strategy development.
Data Used:
Tables: orders, customers
Key Columns:
From orders: Order_ID, Customer_ID, Total_Amount
From customers: Customer_ID, Customer_Name
Analysis Steps:
Joined orders and customers tables to link each order to the respective customer.
Calculated:
Average Order Value (AOV): using AVG(Total_Amount)
Total Orders: using COUNT(*)
Filtered results with HAVING numberOfOrders > 100 to include only frequent buyers.
The resulting list highlights loyal, repeat customers with consistent engagement.
Aggregated total spending per customer using SUM(Total_Amount).
Filtered customers who have spent more than $5,000 in total (HAVING SUM(Total_Amount) > 5000).
Ordered the output in descending order of total spending to rank top spenders.
This identifies premium customers contributing significantly to total revenue.
Purpose:
To understand customer purchasing patterns based on frequency and spending.
To identify loyal and high-value customers for targeted marketing, reward programs, and retention strategies.
To help prioritize business focus on top contributors to revenue.
Outcome:
A clear segmentation of customers into two categories:
Frequent Customers: High engagement, consistent order history, and strong retention potential.
High-Value Customers: Significant revenue generators with premium spending habits.
Enabled the marketing and CRM teams to design personalized campaigns, loyalty incentives, and membership programs aimed at both retention and upselling.
Provided the finance and operations teams with insights into which customers drive profitability and how their behaviors differ.
To identify orders that have not yet been delivered, along with their restaurant details (name and city) and the total number of pending orders per restaurant.
The goal is to track delivery performance, detect bottlenecks, and ensure timely customer service.
Data Used:
Tables: orders, deliveries, restaurants
Key Columns:
From orders: Order_ID, Restaurant_ID
From deliveries: Order_ID, Delivery_Status
From restaurants: Restaurant_ID, Restaurant_Name, City
Analysis Performed:
Two approaches were used to identify non-delivered orders — one using a CTE (Common Table Expression) and another using a Window Function.
CTE 1 (first) — Counted all orders for each restaurant where delivery status is either 'Pending' or 'In Transit'.
CTE 2 (second) — Selected order details and corresponding restaurant information for all non-delivered orders.
Final Join — Combined both CTEs on Restaurant_ID to return:
Order_ID
Restaurant_Name
City
NumberOfPendingOrders (per restaurant)
This provides both individual order details and the aggregated count of pending deliveries.
Used the COUNT(*) OVER (PARTITION BY Restaurant_Id) window function to dynamically calculate the number of pending orders per restaurant without needing separate grouping or joins.
This approach is more concise and computationally efficient for large datasets.
Purpose:
To monitor order delivery status and identify potential delays or inefficiencies in fulfillment.
To provide the operations team with a real-time view of undelivered orders by restaurant.
To support performance evaluation of restaurants and delivery operations.
Outcome:
Identified restaurants with the highest number of pending or in-transit deliveries.
Provided visibility into delivery backlogs, helping prioritize pending orders and improve turnaround times.
The insights enabled restaurant managers and logistics teams to take corrective actions, such as reallocating riders or adjusting kitchen workflows.
Task 4: City-Level Dish Popularity and Restaurant Revenue Analysis
Objective:
Identify the most popular dish and the top-performing restaurant by revenue for each city to gain insights into customer preferences and restaurant performance.
Description:
Most Popular Dish by City:
Analyzed all orders to determine which dish was ordered the most in each city.
Used SQL ranking functions to handle ties and rank dishes based on the total number of orders.
Top Restaurant by Revenue:
Aggregated total revenue per restaurant for the last year.
Used SQL ranking functions to rank restaurants within each city based on total revenue, identifying the top performer.
Purpose & Outcome:
Purpose:
Understand customer preferences at the city level by identifying the most popular dishes.
Identify the highest-grossing restaurants in each city to evaluate business performance and market trends.
Outcome:
Determined the most ordered dish for every city, helping inform menu optimization and marketing strategies.
Identified top restaurants by revenue per city, providing insight into business performance and competitive benchmarking.
Enabled city-specific recommendations for inventory management, promotions, and restaurant expansion.
SQL Query:
Task 5: Customer Retention Analysis and Restaurant Cancellation Rates
Objective:
Analyze customer retention trends and evaluate restaurant cancellation performance to identify potential issues and opportunities for improvement.
Description:
Customer Retention Analysis:
Identified customers who placed orders in 2023 but did not place any orders in 2024.
This helps understand customer churn and potential gaps in engagement strategies.
Restaurant Cancellation Rate Analysis:
Calculated the cancellation rate for each restaurant for 2023 and 2024.
The rate was determined by comparing total orders against undelivered orders (orders without a delivery record).
Used Common Table Expressions (CTEs) to calculate cancellation rates for both years and merged results for comparison.
Purpose & Outcome:
Purpose:
Detect customers at risk of churn to plan targeted retention campaigns.
Monitor restaurant performance over time, particularly delivery reliability, which affects customer satisfaction.
Outcome:
Identified customers who may need re-engagement strategies to improve retention.
Provided year-over-year insights into restaurant cancellation trends, enabling performance monitoring and operational improvements.
Highlighted restaurants with higher-than-average cancellation rates for potential process optimization.
SQL Query:
Task 6: Monthly Restaurant Growth & Rider Performance Analysis
Objective:
Evaluate restaurant revenue growth trends and rider delivery performance to support operational decision-making and improve overall efficiency.
Description:
Monthly Restaurant Growth Analysis:
Calculated each restaurant’s monthly revenue since its joining.
Computed month-over-month growth percentage using window functions (LAG) to compare current month revenue with the previous month.
Handled cases where previous month sales were zero to avoid division errors.
This analysis helps identify high-growth restaurants and seasonal trends in revenue.
Rider Performance Analysis:
Calculated the average delivery time for each rider in minutes.
Corrected for cases where delivery time crosses midnight to ensure accurate timing.
Provides insights into rider efficiency and potential areas for operational improvement.
Purpose & Outcome:
Purpose:
Track restaurant revenue growth over time to identify expansion opportunities or operational bottlenecks.
Monitor rider efficiency to maintain high delivery standards and customer satisfaction.
Outcome:
Identified restaurants with consistent or exceptional growth for further business focus.
Provided average delivery time metrics for riders, enabling performance benchmarking and resource planning.
SQL Queries:
Task 7: Customer Segmentation & Rider Performance Analysis
Objective:
Segment customers based on spending patterns and evaluate rider earnings and performance to optimize business strategies and operational efficiency.
Description:
Customer Segmentation:
Calculated total spending and total orders per customer.
Segmented customers into Gold and Silver tiers based on their total spending compared to the average order value.
Aggregated total revenue and total orders per segment to identify high-value customers and purchasing trends.
Rider Monthly Earnings:
Computed each rider’s total earnings assuming an 8% commission on the total order amount they delivered.
Provides insight into compensation distribution and incentives for high-performing riders.
Rider Rating Analysis:
Rated riders based on delivery time:
5-Star: Orders delivered in under 15 minutes.
4-Star: Orders delivered between 15–20 minutes.
3-Star: Orders delivered after 20 minutes.
Counted the number of ratings per rider to evaluate performance consistency.
Purpose & Outcome:
Purpose:
Identify top-spending customers for targeted marketing and loyalty programs.
Assess rider performance for fair compensation and quality improvement.
Outcome:
Segmented customers into actionable tiers for strategic engagement.
Determined riders’ monthly earnings and performance metrics, enabling incentive planning and operational improvements.
SQL Queries:
Objective:
Analyze order frequency, customer value, monthly sales trends, and rider efficiency to identify peak business periods, high-value customers, and operational performance.
Description:
Order Frequency by Day:
Counted total orders per day of the week for each restaurant.
Ranked days by number of orders to identify peak days for each restaurant.
Provides insight into busiest days, helping with staffing and inventory planning.
Customer Lifetime Value:
Calculated total revenue generated by each customer across all orders.
Counted total orders per customer.
Helps identify high-value customers for loyalty programs and targeted promotions.
Monthly Sales Trend:
Aggregated total sales by month.
Compared each month’s sales to the previous month using a lag function to analyze trends.
Highlights seasonal or monthly variations in sales, enabling proactive business planning.
Rider Efficiency:
Calculated average delivery time per rider, accounting for deliveries that crossed midnight.
Identified riders with the fastest and slowest average delivery times.
Provides actionable insights into operational efficiency and opportunities for performance improvement.
Purpose & Outcome:
Determine peak order days for optimized staffing and inventory.
Identify high-value customers for marketing strategies.
Track monthly sales trends for financial planning.
Evaluate rider performance for training, incentives, and operational improvements.
SQL Queries:
Objective:
Identify seasonal demand patterns for menu items and rank cities based on total revenue to optimize inventory, marketing campaigns, and resource allocation.
Description:
Order Item Popularity by Season:
Classified orders into seasons:
Spring: April–June
Summer: July–August
Winter: September–March
Counted total orders per menu item in each season.
Ranked items within each season to determine the most popular dishes.
Helps restaurants adjust menus, promotions, and stock levels according to seasonal demand spikes.
City Revenue Ranking:
Aggregated total revenue for each city for the year 2023.
Ranked cities based on total revenue.
Provides insights for geographic performance evaluation, targeted marketing, and expansion planning.
Purpose & Outcome:
Optimize inventory and marketing strategies by identifying seasonal menu trends.
Evaluate city-wise revenue performance to guide business decisions.
Enables strategic planning for high-demand items and top-performing locations.
SQL Queries:
This project provides a thorough analysis of restaurant operations, customer behavior, and delivery performance using SQL queries across multiple dimensions. By examining order patterns, customer spending, and rider efficiency, the analysis delivers actionable insights to support strategic and operational decisions.
Key Insights:
Order Patterns & Popularity:
Identified peak ordering time slots, days of the week, and seasonal trends for specific menu items.
Determined the most popular dishes by city and peak ordering times, enabling data-driven inventory and staffing decisions.
Customer Analysis:
Segmented customers into Gold and Silver tiers based on spending patterns and calculated lifetime value for each customer.
Highlighted high-value customers who contribute significantly to revenue, supporting targeted marketing campaigns and loyalty programs.
Identified inactive customers who placed orders previously but not recently, providing opportunities for re-engagement.
Restaurant & City Performance:
Ranked restaurants by total revenue within each city and tracked monthly revenue growth.
Calculated cancellation rates and highlighted restaurants with high rates, enabling operational improvements.
Ranked cities based on total revenue, identifying high-performing locations for potential expansion or focused promotions.
Rider Performance & Efficiency:
Analyzed rider delivery times and calculated average times to identify high-performing and low-performing riders.
Calculated rider earnings based on commission and assessed ratings based on delivery speed, supporting incentive planning and performance management.
Revenue & Sales Trends:
Tracked monthly sales trends and revenue growth ratios to monitor business performance over time.
Identified patterns in customer orders and seasonal demand spikes, helping optimize marketing and operational strategies.
Overall Outcome:
This analysis provides a holistic view of business performance, combining financial, operational, and customer insights. The findings equip management with actionable recommendations to:
Improve customer retention and engagement through targeted programs.
Optimize delivery operations and rider performance for faster service.
Maximize revenue by focusing on high-performing restaurants, cities, and popular menu items.
Make informed strategic decisions regarding growth, marketing, and operational efficiency.
By leveraging these insights, the restaurant network can enhance customer satisfaction, increase revenue, and ensure sustainable growth.
Emmanuel Olorunda
Copyright © 2025 | All Rights Reserved
Every project starts with a chat, Emmanuel is keen to conversation and open to discussing your project. he will pull in the right expertise when needed