The timezone part is what trips people up.
Start by defining the metric and assumptions, then build a SQL query using CTEs to filter completed deliveries in the last 30 days, convert UTC timestamps to city-local time, and aggregate counts by city and date. Use window functions to compute the complaint rate as a percentage, ensuring proper handling of time zones and date boundaries.
Pro tip: Always state your timezone assumption explicitly (e.g., using the delivery's city timezone) and consider that 'last 30 days' should be relative to the current date in each city's local timezone to avoid off-by-one errors.
Define 'cold complaint rate' as the number of cold complaints divided by completed deliveries, and state that you will convert UTC timestamps to city-local time using the city's timezone.
Use a CTE to select completed deliveries from the last 30 days, converting the delivery timestamp from UTC to the city's local timezone.
In another CTE, group by city and local date, counting total deliveries and cold complaints (e.g., using a CASE statement or filtering on complaint type).
Use a window function (e.g., SUM OVER) to calculate the complaint rate as cold complaints divided by deliveries, or simply compute the ratio directly.
Select city, date, delivery count, cold complaint count, and complaint rate, ordering by city and date for readability.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
LAG usage was fine but the QUALIFY filter on the city-level difference is a bit awkward because you're qualifying on an aggregated value, not a row-level window.
First, use LAG to compute the time differences between consecutive order events (ready-to-pickup and pickup-to-dropoff) per order. Then, aggregate median pickup-to-dropoff times by city and cold complaint status, and use QUALIFY to filter cities where the difference exceeds 12 minutes. Focus on a one-month window and ensure proper partitioning and ordering.
Pro tip: When using LAG, ensure the window is ordered by event timestamp and partitioned by order_id to avoid mixing events from different orders. Also, consider using APPROX_PERCENTILE for median to handle large datasets efficiently.
Use LAG over order_events partitioned by order_id and ordered by event timestamp to calculate ready-to-pickup and pickup-to-dropoff durations for each order.
Restrict to a one-month window and join with complaint data to label each order as having a cold complaint or not.
Group by city and cold complaint flag, and compute the median pickup-to-dropoff duration for each group.
Calculate the difference in median pickup-to-dropoff time between orders with and without cold complaints per city, then use QUALIFY to keep only cities where the difference exceeds 12 minutes.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, use a QUALIFY clause with ROW_NUMBER() to select the last 100 completed deliveries per courier in each city up to the cutoff date. Then, compute the cold complaint rate per courier and the city median rate, and finally use another QUALIFY clause to flag couriers whose rate exceeds twice the city median and who have at least 100 deliveries.
Pro tip: When using QUALIFY for the last 100 deliveries, ensure the window function partitions by courier and city and orders by delivery timestamp descending. For the outlier filter, use a window function to compute the city median rate, and remember that QUALIFY can reference window functions directly, avoiding subqueries.
Filter deliveries to completed status and delivery date <= cutoff. Use ROW_NUMBER() OVER (PARTITION BY courier_id, city ORDER BY delivery_timestamp DESC) to rank each courier's deliveries.
Apply QUALIFY row_number <= 100 to restrict to the last 100 deliveries per courier in their city.
Calculate each courier's cold complaint rate (cold complaints / total deliveries) and the city median rate using PERCENTILE_CONT or MEDIAN window function.
Use QUALIFY to filter couriers where courier_rate > 2 * city_median_rate AND total_deliveries >= 100.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
P90 with PERCENTILE_CONT is straightforward.
First, compute the P90 of pickup-to-dropoff time for each city using a window function or aggregation. Then, for each courier, calculate the share of their deliveries that exceed their city's P90. Finally, use QUALIFY with a window function that orders by share descending and delivery count descending to select the top 5 couriers per city, explaining that the tie-breaker is applied within the same window ordering.
Pro tip: When using QUALIFY with ROW_NUMBER(), ensure the window's PARTITION BY city and ORDER BY includes both the share and delivery count to handle ties deterministically. Also, consider whether the P90 should be computed over all deliveries or only those from couriers with a minimum number of deliveries to avoid noise.
Calculate the 90th percentile of pickup-to-dropoff time for each city using an aggregate function like PERCENTILE_CONT(0.9) or APPROX_QUANTILES, grouped by city.
Join the city-level P90 back to the deliveries table and create a binary flag indicating whether each delivery's pickup-to-dropoff time exceeds the city's P90.
For each courier and city, compute the share of deliveries that exceed the P90 (i.e., average of the flag) and the total delivery count.
Use QUALIFY with ROW_NUMBER() OVER (PARTITION BY city ORDER BY share DESC, delivery_count DESC) to assign ranks, then filter for rank <= 5.
Describe that the window orders by share descending first, then by delivery count descending to break ties, ensuring deterministic selection.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.