Start by clarifying the schema and definitions (e.g., late = actual_delivery_date > expected_delivery_date, last 30 days relative to today or max date). Then write a SQL query that filters orders in the last 30 days, calculates the percentage of late orders, and for the follow-up, aggregates late counts per customer and returns the top 3. Walk through the logic step-by-step, mentioning edge cases like NULL delivery dates and time zone considerations.
Pro tip: Mention that you would validate the metric by checking the distribution of delivery delays and ensure the 30-day window is based on order date, not delivery date, to avoid bias. Also, consider if 'late' should be defined with a grace period (e.g., > 1 hour) as per business rules.
Ask clarifying questions about the table structure, definitions of 'late', and the reference date for 'last 30 days'. Confirm whether to use order date or delivery date for the window.
Use a CASE statement to flag late orders, filter to the last 30 days, and compute the percentage as (sum of late flags / total orders) * 100. Use COUNT or AVG with appropriate casting.
Filter to the same 30-day window, group by customer_id, count late orders, order descending, and limit to 3. Join with customer table if names are needed.
Address NULL actual delivery dates (e.g., undelivered orders), time zones, and whether to include only completed orders. Suggest sanity checks like comparing with overall late rate.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.