Data Analyst Interview Questions & Answers: Set 6, Ride-Sharing Platform

SQL

1. 1.Find drivers who completed more than 20 trips but have an average rating below 4.0.

   Hide answer

   Answer: Aggregate completed trips per driver, then filter the groups with HAVING, because WHERE cannot filter on aggregates. Assume trips(trip_id, driver_id, status, driver_rating). AVG ignores NULL ratings, which is what we want since unrated trips should not count as zero. Trap: putting COUNT(*) > 20 in WHERE causes an error.

   SELECT driver_id,
          COUNT(*)                     AS completed_trips,
          ROUND(AVG(driver_rating), 2) AS avg_rating
   FROM trips
   WHERE status = 'completed'
   GROUP BY driver_id
   HAVING COUNT(*) > 20
      AND AVG(driver_rating) < 4.0
   ORDER BY avg_rating;

2. 2.Calculate the percentage of trips cancelled by riders vs by drivers each day.

   Hide answer

   Answer: Group trips by day and use conditional aggregation: count cancellations by each party and divide by all trips that day. Assume trips(trip_id, requested_at, status) where status is 'completed', 'cancelled_rider' or 'cancelled_driver'. Multiply by 100.0 to avoid integer division, which would otherwise return 0.

   SELECT CAST(requested_at AS DATE) AS trip_date,
          ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled_rider')
                / COUNT(*), 2) AS pct_rider_cancel,
          ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled_driver')
                / COUNT(*), 2) AS pct_driver_cancel
   FROM trips
   GROUP BY CAST(requested_at AS DATE)
   ORDER BY trip_date;
   -- Other dialects: SUM(CASE WHEN status = '...' THEN 1 ELSE 0 END)