Start by clarifying the schema and definitions (e.g., delivery delay = actual_delivery - estimated_delivery, late threshold > 10 minutes). Then write a query that groups orders by delivery date (filtered to last 7 days), calculates total orders and the percentage late, and finally extends it with a second query that ranks restaurants by average delay over the same period.
Pro tip: Mention that you would handle edge cases like null timestamps or timezone differences, and that you'd validate the query with a quick sanity check (e.g., total orders per day should match known volumes).
Confirm the table structure, column names, and definitions: what constitutes a delivery delay, how to handle nulls, and whether 'last 7 days' includes today. Also clarify if the percentage should be based on all orders or only delivered ones.
Use a CTE or subquery to filter orders from the last 7 days, compute delay in minutes, and flag orders with delay > 10. Then group by delivery date to get total orders and percentage late.
Write a second query that groups by restaurant, calculates average delay over the same 7-day window, orders by average delay descending, and limits to top 5.
Consider indexing on delivery timestamp and restaurant ID for performance. Validate results by checking for anomalies (e.g., days with zero orders) and ensuring the percentage is between 0 and 100.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.