Pickup Wait Quality by Zone

A ride-share company quotes riders an ETA before every pickup. For each city zone, the operations team wants two health numbers:

• avg_wait_ratio: the average, over the zone's pickups, of actual_wait_min / quoted_eta_min (1.00 means the quote was exact).
• poor_pct: the percentage of the zone's pickups whose rider rated the pickup 1 or 2. Unrated pickups stay in the total but are not poor.

Return zone, avg_wait_ratio, poor_pct, one row per zone that has pickups, both numbers rounded to 2 decimal places, in any order. Return no rows if there are no pickups.

Tables

pickups — One row per completed pickup.

Example

pickups

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 11 of 70 · SQLite

The idea

Everything is grouped by zone. The first metric is an average of row-level ratios, so the division happens inside AVG: AVG(actual_wait_min * 1.0 / quoted_eta_min). Multiplying by 1.0 matters because SQLite truncates integer division. Summing waits and quotes first would give a different number, since long quotes would dominate.

For the second metric, a CASE maps each pickup to 1 when the rating is 1 or 2 and to 0 otherwise. A NULL rating fails the comparison and falls into ELSE 0, so unrated pickups stay in the denominator COUNT(*) exactly as the statement asks. Shortcuts like AVG(rider_rating <= 2) look equivalent but silently drop NULL rows from the average, and return NULL for a zone where nobody rated.

Round both values with ROUND(…, 2) at the very end.

Solution

SELECT
  zone,
  ROUND(AVG(actual_wait_min * 1.0 / quoted_eta_min), 2) AS avg_wait_ratio,
  ROUND(100.0 * SUM(CASE WHEN rider_rating <= 2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_pct
FROM pickups
GROUP BY zone;

Another way

SELECT
  zone,
  ROUND(SUM(CAST(actual_wait_min AS REAL) / quoted_eta_min) / COUNT(*), 2) AS avg_wait_ratio,
  ROUND(AVG(iif(COALESCE(rider_rating, 5) <= 2, 100.0, 0.0)), 2) AS poor_pct
FROM pickups
GROUP BY zone;

Why common answers fail

AVG skips NULL ratings, so unrated pickups drop out of the denominator and zones with no ratings get NULL.

SELECT zone,
  ROUND(AVG(actual_wait_min * 1.0 / quoted_eta_min), 2) AS avg_wait_ratio,
  ROUND(100.0 * AVG(rider_rating <= 2), 2) AS poor_pct
FROM pickups
GROUP BY zone;

Computes a ratio of sums, which weights long quotes more heavily, instead of the average of per-pickup ratios.

SELECT zone,
  ROUND(SUM(actual_wait_min) * 1.0 / SUM(quoted_eta_min), 2) AS avg_wait_ratio,
  ROUND(100.0 * SUM(CASE WHEN rider_rating <= 2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_pct
FROM pickups
GROUP BY zone;

Divides two integers, so every ratio is truncated (6 / 5 becomes 1, 5 / 10 becomes 0).

SELECT zone,
  ROUND(AVG(actual_wait_min / quoted_eta_min), 2) AS avg_wait_ratio,
  ROUND(100.0 * SUM(CASE WHEN rider_rating <= 2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_pct
FROM pickups
GROUP BY zone;

What the interviewer is checking

Tests whether you can tell an average of ratios from a ratio of sums, and whether you notice how NULLs leak into AVG and SUM.

Mistakes to avoid

• Integer division truncating the ratio.
• Averaging a boolean that is NULL for unrated rows.
• Dividing total wait by total quote instead of averaging the per-pickup ratios.

Follow-ups to expect

• Only consider zones with at least 20 pickups (HAVING COUNT(*) >= 20).
• Compute poor_pct among rated pickups only (divide by COUNT(rider_rating)).
• Break the numbers down by hour of day (add strftime on a timestamp to the GROUP BY).

In real work

Delivery and dispatch teams track promised-versus-actual ratios and low-score rates per region to spot where ETAs need recalibrating.

Practice next