You are given two tables: Restaurants and Orders. After receiving an order,
each restaurant has 15 minutes to dispatch it. Dispatch times are categorized as follows:
on_time_dispatch: Dispatched within 15 minutes of order received.
late_dispatch: Dispatched between 15 and 20 minutes after order received.
super_late_dispatch: Dispatched after 20 minutes.
Task: Write an SQL query to count the number of dispatched orders in each category for each restaurant.
DROP TABLE IF EXISTS restaurants;
DROP TABLE IF EXISTS orders;
CREATE TABLE Restaurants (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE Orders (
id INT IDENTITY(1,1) PRIMARY KEY,
restaurant_id INT REFERENCES Restaurants(id),
order_time Datetime,
dispatch_time Datetime
);
INSERT INTO Restaurants (name) VALUES
('KFC'),
('McDonald'),
('Starbucks'),
('Haldiram'),
('Dominoz');
INSERT INTO Orders (restaurant_id, order_time, dispatch_time) VALUES
(1, '2024-07-23 12:00:00', '2024-07-23 12:14:00'),
(1, '2024-07-23 12:30:00', '2024-07-23 12:48:00'),
(1, '2024-07-23 13:00:00', '2024-07-23 13:16:00'),
(2, '2024-07-23 13:30:00', '2024-07-23 13:50:00'),
(2, '2024-07-23 14:00:00', '2024-07-23 14:14:00'),
(3, '2024-07-23 14:30:00', '2024-07-23 14:49:00'),
(3, '2024-07-23 15:00:00', '2024-07-23 15:16:00'),
(3, '2024-07-23 15:30:00', '2024-07-23 15:40:00'),
(4, '2024-07-23 16:00:00', '2024-07-23 16:10:00'),
(4, '2024-07-23 16:30:00', '2024-07-23 16:50:00'),
(5, '2024-07-23 17:00:00', '2024-07-23 17:25:00'),
(5, '2024-07-23 17:30:00', '2024-07-23 17:55:00'),
(5, '2024-07-23 18:00:00', '2024-07-23 18:19:00'),
(1, '2024-07-23 18:30:00', '2024-07-23 18:44:00'),
(2, '2024-07-23 19:00:00', '2024-07-23 19:13:00'); Solving Approach:
1.Get the dispatch_category of Orders by using Datediff function to get the minute difference between two timestamp per restaurant.
2. Now we want the three different column for three Dispatch category so we will use the sum and case function to get the count of each category .
---Solution
with cte
as
(
SELECT
id,
Restaurant_id,
DATEDIFF(MINUTE,order_time, dispatch_time) as Timing,
CASE WHEN DATEDIFF(MINUTE,order_time, dispatch_time) > 15 and DATEDIFF(MINUTE,order_time, dispatch_time) <= 20 THEN 'late_dispatch'
WHEN DATEDIFF(MINUTE,order_time, dispatch_time) > 20 THEN 'super_late_dispatch'
ELSE 'on_time_dispatch'
END as dispatch_category
from orders
)
select Restaurant_id,Name,
SUM(CASE WHEN dispatch_category = 'on_time_dispatch' THEN 1 ELSE 0 END) AS on_time_dispatch,
SUM(CASE WHEN dispatch_category = 'late_dispatch' THEN 1 ELSE 0 END) AS late_dispatch,
SUM(CASE WHEN dispatch_category = 'super_late_dispatch' THEN 1 ELSE 0 END) AS super_late_dispatch
from cte a
inner join restaurants b on a.restaurant_id = b.id
group by name,Restaurant_id
ORDER BY Restaurant_id
Thanks for visisting
Leave a Reply