Data Analyst Interview Questions & Answers: Set 4, Food Delivery App

SQL

1. 1.Find the average delivery time per restaurant for the last 30 days.

   Hide answer

   Answer: Filter delivered orders from the last 30 days, compute the minutes between order and delivery, and average per restaurant. Assume orders(order_id, restaurant_id, status, order_time, delivered_time). EXTRACT(EPOCH ...) gives seconds, so divide by 60. Only include delivered orders, because cancelled ones have no delivered_time and would distort the metric. Trap: averages hide long tails, so it is worth also reporting the median or 90th percentile.

   SELECT restaurant_id,
          COUNT(*) AS delivered_orders,
          ROUND(AVG(EXTRACT(EPOCH FROM delivered_time - order_time)) / 60, 1)
            AS avg_delivery_min
   FROM orders
   WHERE status = 'delivered'
     AND order_time >= CURRENT_DATE - INTERVAL '30 days'
   GROUP BY restaurant_id
   ORDER BY avg_delivery_min DESC;

2. 2.Find customers who ordered at least once every week in the last month.

   Hide answer

   Answer: Define "last month" as the last 4 complete weeks, truncate each order to its week, and keep customers whose count of distinct weeks equals 4. Assume orders(customer_id, order_time). Using COUNT(DISTINCT week) is the key: a customer with 10 orders in one week should not qualify. Using complete weeks avoids penalising everyone for a partial current week.

   WITH weekly AS (
     SELECT DISTINCT customer_id,
            DATE_TRUNC('week', order_time) AS wk
     FROM orders
     WHERE order_time >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '4 weeks'
       AND order_time <  DATE_TRUNC('week', CURRENT_DATE)
   )
   SELECT customer_id
   FROM weekly
   GROUP BY customer_id
   HAVING COUNT(*) = 4;