Top 10% Underperforming Zones | Dataford Interview Questions

Your question is Top 10% Underperforming Zones. 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

Swiggy Operations wants to identify active delivery zones with the longest completed-order delivery times. Write a PostgreSQL query that returns the top 10% of qualifying zones by average delivery duration.

Requirements

Join swiggy_zones with swiggy_deliveries using zone_id.
Consider only active zones and deliveries with status = 'DELIVERED' and non-null timestamps.
Calculate average delivery time in minutes and the number of completed deliveries per zone.
Rank zones from slowest to fastest and return CEIL(10% of qualifying zones), with ties resolved by zone_id.

Schema

swiggy_zones

Column	Type	Description
zone_idPK	INTEGER	Unique Swiggy delivery zone identifier
zone_name	VARCHAR(80)	Name of the delivery zone
city	VARCHAR(60)	City containing the zone
is_active	BOOLEAN	Whether the zone is currently active

swiggy_deliveries

Column	Type	Description
delivery_idPK	INTEGER	Unique delivery identifier
zone_id	INTEGER	Delivery zone associated with the order
status	VARCHAR(20)	Delivery lifecycle status
accepted_at	TIMESTAMP	Timestamp when the delivery was accepted
delivered_at	TIMESTAMP	Timestamp when the order was delivered