SQL: 90th Percentile by Metro | Dataford Interview Questions

Your question is SQL: 90th Percentile by Metro. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Instacart Operations needs to compare delivery performance across metros. Write a PostgreSQL query that calculates the 90th percentile delivery duration for each metro area.

Requirements

Calculate duration in minutes as the difference between delivered_at and pickup_at.
Exclude records with missing timestamps or negative durations.
Join deliveries to metro_areas and return one row per metro with at least one valid delivery.
Use PostgreSQL's continuous percentile function and sort the output alphabetically by metro name.

Schema

order_deliveries

Column	Type	Description
delivery_idPK	INTEGER	Unique delivery identifier
metro_id	INTEGER	References metro_areas.metro_id
pickup_at	TIMESTAMPTZ	Shopper pickup timestamp
delivered_at	TIMESTAMPTZ	Customer delivery timestamp

metro_areas

Column	Type	Description
metro_idPK	INTEGER	Unique metro identifier
metro_name	VARCHAR(100)	Instacart metro name