Pick the Best Courier for Each Delivery Zone

Problem

A food-delivery company assigns one courier to each delivery zone. A zone lists the vehicle capabilities it needs and how critical each one is (level 1 to 5). A courier is eligible for a zone only if they possess every capability the zone requires. For each eligible courier compute a fit_score that starts at 100, adds 10 for every capability where the courier's skill_level is strictly greater than the zone's needed level, and subtracts 5 for every capability where the courier's skill_level is strictly less than the needed level. For each zone return the courier with the highest fit_score; break ties by the smaller courier_id. Return zone_id, courier_id and fit_score, ordered by zone_id.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns zone_id, courier_id, fit_score.

Starter code (SQL)

SELECT *
FROM couriers;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium