🚀 Real SQL Interview Question Reported in a Swiggy Business Analyst Interview

Question:

Given an orders table with the following columns:

driver_id
order_time
delivered_time

Write an SQL query to calculate the average waiting/delivery time (in minutes) for each delivery partner.

✅ SQL Solution (MySQL)

SELECT
driver_id,
AVG(TIMESTAMPDIFF(MINUTE, order_time, delivered_time)) AS avg_delivery_time
FROM orders
GROUP BY driver_id;

💡 Approach:

• Calculate the time difference between order_time and delivered_time.
• Convert the difference into minutes using TIMESTAMPDIFF().
• Group records by driver_id.
• Use AVG() to find the average delivery time for each delivery partner.

📚 Concepts Tested:

• Date & Time Functions
• GROUP BY
• Aggregate Functions (AVG)
• Business Metrics

React ♥️ for more real interview questions