Drill 1 of 12 in Food delivery questions
0

Interview Question Bank · Timestamp subtraction · EXTRACT(EPOCH) · AVG

1. Average delivery time per restaurant

EasyFood deliveryQuestion #229

Concept: Timestamp subtraction · EXTRACT(EPOCH) · AVGshow

Subtracting two timestamps gives an interval. EXTRACT(EPOCH FROM interval) turns the whole interval into seconds, so dividing by 60 gives total minutes. EXTRACT(MINUTE FROM interval) is a trap: it reads only the minutes field, so 1 hour 12 minutes comes out as 12.

Example (different data):

SELECT clinic,
  ROUND(AVG(EXTRACT(EPOCH FROM seen_at - arrived_at) / 60)::numeric, 1) AS avg_wait_min
FROM er_visits
WHERE seen_at IS NOT NULL
GROUP BY clinic;

Your task

Brightbowl's ops team wants to know which restaurants are slowest from the moment a customer taps 'Place order' to the moment the food arrives.

Using delivered orders only, return restaurant (the restaurant's name), deliveries (number of delivered orders) and avg_minutes (average minutes from placed_at to delivered_at, ROUND(x::numeric, 1)). Sort by avg_minutes ascending, then restaurant.

Starting Postgres in your browser…

Checking your access…