The median ETA part is what slowed me down.
Start by clarifying the schema and metric definitions, then structure the SQL using CTEs to compute daily aggregates and window functions for medians. Finally, join with population data to calculate merchant coverage and apply the at-risk flag based on thresholds.
Pro tip: Use PERCENTILE_CONT for median ETA and ensure you handle edge cases like days with zero orders by using LEFT JOINs from a date spine. Also, consider timezone consistency for 'per-day' metrics.
Confirm table structures, column names, and metric definitions (e.g., delivery ETA, cancel rate). Ask about timezone and whether 'per-day' is based on order creation date.
Create a date series for the 7-day window and filter to the specific city. This ensures all days are represented, even with no orders.
Use CTEs to calculate orders created, delivered, cancelled, and median delivery ETA per day. Join orders with merchants and couriers as needed.
Count distinct merchants per day and divide by (city population / 10000) to get coverage per 10k population. Join with population data.
Flag days where median ETA > 35 minutes or cancel rate > 8%. Use CASE statements and ensure all metrics are correctly aggregated.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
NULLIF was explicitly hinted in the prompt so that part wasn't a gotcha, but the day-level aggregation from 15-min buckets is easy to mess up if you're not careful about how you truncate the timestamp.
Start by clarifying the table schema and metric definitions, then write a SQL query that computes the ratio of active couriers to demand requests per 15-minute interval, flags intervals below 0.8, aggregates by day to get the share of undersupplied intervals, and finally flags days where that share exceeds 25%. Explain each step and consider edge cases like missing data or zero demand.
Pro tip: Mention that you would validate the query by checking a few days manually and consider using a CTE for readability; also note that the 0.8 threshold and 25% cutoff are business rules that might need adjustment based on context.
Confirm the table columns (e.g., timestamp, active_couriers, demand_requests) and define what 'active couriers' and 'demand requests' mean. Ensure the 15-minute interval granularity and how to handle missing or zero demand.
Write a subquery or CTE that calculates the ratio of active couriers to demand requests for each 15-minute interval and flags intervals where the ratio is below 0.8. Handle division by zero (e.g., using NULLIF).
Group the flagged intervals by day (using DATE(timestamp)) and compute the total number of intervals and the number of undersupplied intervals. Calculate the share as undersupplied intervals divided by total intervals.
In the final SELECT, add a boolean flag (e.g., is_undersupply_day) that is TRUE when the share of undersupplied intervals is greater than 0.25. Optionally, filter to only show flagged days.
Check results for a few days manually, ensure the query handles edge cases (e.g., days with no intervals), and consider indexing or partitioning for performance if the table is large.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This one tripped me up more than I expected.
Start by defining the cohort as couriers who signed up in the specified city and date range, then compute activation (e.g., first login or first dash) within 14 days, first delivery completion, and median time to first delivery. Use a single SQL query with CTEs to filter, aggregate, and return one summary row, ensuring proper handling of time zones and denominators.
Pro tip: Always clarify the definition of 'activation' and 'first delivery' with the interviewer, as these can vary by company; also, use median (not average) for time-to-delivery to avoid skew from outliers.
Identify the exact signup date range and city filter, and confirm what constitutes 'activation' (e.g., first login, first dash) and 'first delivery' (e.g., first completed delivery).
Use a CTE to select couriers who signed up in the given city and date range, then left join to activation and delivery events to get timestamps.
Calculate the percentage of couriers who activated within 14 days of signup and the percentage who completed at least one delivery, using conditional aggregation.
For couriers with a first delivery, compute the time difference in minutes between signup and first delivery, then take the median across the cohort.
Combine all metrics into one row using a final SELECT that outputs activation_rate, delivery_rate, and median_time_to_first_delivery_minutes.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.