Drill 3 of 3 in Ride-sharing questions
0

Interview Question Bank · EXTRACT(HOUR) · conditional COUNT

3. Cancellation rate by hour of day

MediumRide-sharingQuestion #125

Concept: EXTRACT(HOUR) · conditional COUNTshow

To compare times of day across many dates, group by EXTRACT(HOUR FROM ts), which returns 0-23 and merges 8 a.m. on Monday with 8 a.m. on Tuesday. date_trunc('hour', ts) keeps the date, so it gives one row per calendar hour instead. When several status values mean the same thing, list them all with IN.

Example (different data):

SELECT EXTRACT(HOUR FROM placed_at)::int AS hour,
  COUNT(*) AS deliveries,
  COUNT(*) FILTER (WHERE status IN ('lost', 'damaged')) AS problems
FROM deliveries
GROUP BY 1
ORDER BY 1;

Your task

Rydora's operations team wants to know which hours of the day lose the most rides to cancellations, whether the rider or the driver cancels.

Using every trip request in mkt_trips, return hour (the hour of day of requested_at, 0-23, as an integer), requests, cancellations (trips cancelled by the rider or by the driver) and cancel_pct (cancellations as a percentage of requests, ROUND(..., 1)). One row per hour that has requests, sorted by hour.

Starting Postgres in your browser…

Checking your access…