Coding questions · Meta
Query carpool ride metrics
This question evaluates proficiency in data manipulation and analytical querying using SQL and Python to compute ride-sharing metrics such as daily completed pooled rides, average seats utilized, and cancellation rate.
Question
For a ride-sharing product with carpool capability, answer a series of SQL questions (e.g., daily completed pooled rides, average seats utilized, cancellation rate). Assume typical trip, user, and pooling tables; no CTEs are required.
Daily Carpool Ride Metrics
Using the tables below for a ride-sharing product with carpool capability, compute daily carpool metrics:
- The number of completed pooled rides per calendar date.
- The average number of seats utilized per completed pooled ride per date (seats utilized are the sum of seats from bookings with status = 'completed' for that pool; exclude 'no_show' and any cancelled bookings from utilized seats).
- The booking cancellation rate per date, defined as (number of bookings created that day with status in ('cancelled_by_rider', 'cancelled_by_driver')) divided by total bookings created that day.
Output one row per calendar date that appears either in completed pooled ride end_time or in booking created_at.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY |
| name | VARCHAR(100) | NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| name | signup_date | user_id |
|---|---|---|
| Alice Rider | 2025-07-20 | 1 |
| Bob Rider | 2025-07-21 | 2 |
| Carol Rider | 2025-07-22 | 3 |
| Dave Rider | 2025-07-23 | 4 |
| Eve Rider | 2025-07-24 | 5 |
| Frank Rider | 2025-07-25 | 6 |
| Dan Driver | 2025-07-01 | 1001 |
| Dana Driver | 2025-07-01 | 1002 |
Table drivers
| column | type | constraints |
|---|---|---|
| driver_id | INTEGER | PRIMARY KEY |
| user_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
Sample data:
| driver_id | user_id |
|---|---|
| 1 | 1001 |
| 2 | 1002 |
Table pooled_rides
| column | type | constraints |
|---|---|---|
| pool_id | INTEGER | PRIMARY KEY |
| driver_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES drivers(driver_id) |
| start_time | TIMESTAMP | NOT NULL |
| end_time | TIMESTAMP | |
| status | VARCHAR(20) | NOT NULL |
| seat_capacity | INTEGER | NOT NULL |
Sample data:
| driver_id | end_time | pool_id | seat_capacity | start_time | status |
|---|---|---|---|---|---|
| 1 | 2025-08-01 08:45:00 | 10 | 4 | 2025-08-01 08:00:00 | completed |
| 1 | 2025-08-01 10:10:00 | 11 | 4 | 2025-08-01 09:15:00 | completed |
| 2 | NULL | 12 | 3 | 2025-08-01 17:30:00 | cancelled |
| 2 | 2025-08-02 09:30:00 | 13 | 3 | 2025-08-02 08:30:00 | completed |
| 2 | 2025-08-02 18:50:00 | 14 | 2 | 2025-08-02 18:00:00 | completed |
Table pool_bookings
| column | type | constraints |
|---|---|---|
| booking_id | INTEGER | PRIMARY KEY |
| pool_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES pooled_rides(pool_id) |
| rider_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| seats_booked | INTEGER | NOT NULL |
| status | VARCHAR(30) | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| cancel_time | TIMESTAMP |
Sample data:
| booking_id | cancel_time | created_at | pool_id | rider_id | seats_booked | status |
|---|---|---|---|---|---|---|
| 100 | NULL | 2025-08-01 07:20:00 | 10 | 1 | 1 | completed |
| 101 | NULL | 2025-08-01 07:25:00 | 10 | 2 | 1 | completed |
| 102 | 2025-08-01 07:45:00 | 2025-08-01 07:40:00 | 10 | 3 | 2 | cancelled_by_rider |
| 103 | NULL | 2025-08-01 07:55:00 | 10 | 4 | 1 | completed |
| 104 | NULL | 2025-08-01 08:50:00 | 11 | 5 | 1 | completed |
| 105 | NULL | 2025-08-01 08:55:00 | 11 | 6 | 1 | completed |
| 106 | 2025-08-01 09:05:00 | 2025-08-01 09:00:00 | 11 | 2 | 1 | cancelled_by_driver |
| 107 | NULL | 2025-08-01 09:05:00 | 11 | 3 | 1 | no_show |
| 108 | 2025-08-01 17:30:00 | 2025-08-01 17:00:00 | 12 | 1 | 1 | cancelled_by_driver |
| 109 | 2025-08-01 17:30:00 | 2025-08-01 17:05:00 | 12 | 4 | 1 | cancelled_by_driver |
| 110 | NULL | 2025-08-02 07:50:00 | 13 | 1 | 1 | completed |
| 111 | NULL | 2025-08-02 07:55:00 | 13 | 5 | 2 | completed |
| 112 | 2025-08-02 08:10:00 | 2025-08-02 08:05:00 | 13 | 3 | 1 | cancelled_by_rider |
| 113 | NULL | 2025-08-02 17:30:00 | 14 | 2 | 1 | completed |
| 114 | NULL | 2025-08-02 17:35:00 | 14 | 6 | 1 | completed |
| 115 | 2025-08-02 17:45:00 | 2025-08-02 17:40:00 | 14 | 4 | 1 | cancelled_by_driver |
Expected output
One row per calendar date. completed_pooled_rides and avg_seats_utilized are based on pooled ride end_time for rides with status = 'completed'. avg_seats_utilized is the average, over completed pools that day, of the total completed seats booked in that pool (sum of seats_booked where booking status = 'completed'). cancellation_rate is cancellations divided by total bookings created that day (statuses counted as cancellations: 'cancelled_by_rider', 'cancelled_by_driver'). Exclude 'no_show' from cancellations and from utilized seats.
| avg_seats_utilized | cancellation_rate | completed_pooled_rides | ride_date |
|---|---|---|---|
| 2.5 | 0.4 | 2 | 2025-08-01 |
| 2.5 | 0.3333 | 2 | 2025-08-02 |
Hints
- Treat each pooled ride (vehicle journey) as one unit and sum completed seat bookings per pool to get seats utilized.
- Use conditional aggregation on pool_bookings.created_at to compute cancellations and total bookings per day.
- Join the per-day aggregates without CTEs by using inline subqueries and a FULL OUTER JOIN on date.
- Count cancellations using statuses IN ('cancelled_by_rider','cancelled_by_driver'); exclude 'no_show' from cancellations and from utilized seats.