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…