UptoSkills Logo
Login
Order Delivery Time CalculationMedium

Order Delivery Time Calculation

MediumSQLPractice library · +20 XPdate functionssqldelivery

A food delivery company wants to analyze the efficiency of their delivery operations. They need to identify how long it takes for orders to be delivered, focusing on orders placed in a particular week.

Tables:

  • orders: order_id (integer), customer_id (integer), order_time (timestamp), delivery_time (timestamp)
  • customers: customer_id (integer), name (varchar(100))

Return the order_id and the delivery_duration in hours for all orders that were placed between Monday and Sunday of the 3rd week of 2025. The delivery_duration should be rounded to 2 decimal places. If an order has not been delivered yet, it should not be included in the result. The results should be ordered by order_id.

Handling NULLs and empty results: Orders with delivery_time as NULL are excluded. If no orders match the criteria, an empty result set should be returned. Ties in order_id are not possible as it's a primary key.

Tables

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id integer NOT NULL,
  order_time timestamp NOT NULL,
  delivery_time timestamp
);

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name varchar(100) NOT NULL
);

Example data

INSERT INTO customers (customer_id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO orders (order_id, customer_id, order_time, delivery_time) VALUES
(101, 1, '2025-01-14 10:00:00', '2025-01-14 12:30:00'),
(102, 2, '2025-01-15 14:00:00', '2025-01-15 18:00:00'),
(103, 1, '2025-01-13 09:00:00', '2025-01-13 11:00:00'),
(104, 3, '2025-01-18 20:00:00', '2025-01-19 01:00:00'),
(105, 2, '2025-01-20 08:00:00', '2025-01-20 10:00:00'), -- Order placed in the next week
(106, 1, '2025-01-10 12:00:00', '2025-01-10 15:00:00'), -- Order placed in the previous week
(107, 3, '2025-01-16 11:00:00', NULL); -- Not delivered yet

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems