Query carpool ride metrics
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
##### 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.
Overview: 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.
Using the tables below for a ride-sharing product with carpool capability, compute daily carpool metrics:
1) The number of completed pooled rides per calendar date.
2) 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).
3) 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.
Tables
users(user_id INTEGER, name VARCHAR(100), signup_date DATE)
drivers(driver_id INTEGER, user_id INTEGER)
pooled_rides(pool_id INTEGER, driver_id INTEGER, start_time TIMESTAMP, end_time TIMESTAMP, status VARCHAR(20), seat_capacity INTEGER)
pool_bookings(booking_id INTEGER, pool_id INTEGER, rider_id INTEGER, seats_booked INTEGER, status VARCHAR(30), created_at TIMESTAMP, cancel_time TIMESTAMP)
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.
Community answers
Answer by ginb
WITH ride_stats AS (
-- Metrics for completed pooled rides
SELECT
DATE(r.end_time) AS report_date,
COUNT(DISTINCT r.ride_id) AS completed_pooled_rides,
AVG(ride_seat_sums.total_seats) AS avg_seats_utilized
FROM rides r
JOIN (
-- Calculate total utilized seats per ride ID
SELECT
ride_id,
SUM(seats_booked) AS total_seats
FROM bookings
WHERE status = 'completed'
GROUP BY ride_id
) ride_seat_sums ON r.ride_id = ride_seat_sums.ride_id
WHERE r.status = 'completed'
AND r.is_carpool = TRUE
GROUP BY 1
),
booking_stats AS (
-- Metrics for daily cancellations
SELECT
DATE(created_at) AS report_date,
COUNT(*) AS total_bookings,
COUNT(CASE WHEN status IN ('cancelled_by_rider', 'cancelled_by_driver') THEN 1 END) AS cancellations
FROM bookings
GROUP BY 1
)
SELECT
COALESCE(r.report_date, b.report_date) AS calendar_date,
COALESCE(r.completed_pooled_rides, 0) AS completed_pooled_rides,
ROUND(COALESCE(r.avg_seats_utilized, 0), 2) AS avg_seats_utilized,
ROUND(
COALESCE(b.cancellations, 0)::DECIMAL / NULLIF(b.total_bookings, 0),
4
) AS cancellation_rate
FROM ride_stats r
FULL OUTER JOIN booking_stats b ON r.report_date = b.report_date
ORDER BY calendar_date;
Answer by ginb
with avg_seats as (
select end_time::date as ride_date,
count(*) as completed_pooled_rides,
round(avg(total_seats),2) as avg_seats_utilized
from (
select pool_id,
sum(seats_booked) as total_seats
from pool_bookings
where status='completed'
group by 1
) t join pooled_rides pon t.pool_id=p.pool_idgroup by 1
),
cancellations as (
select
created_at::date as ride_date,
count(case when status like '%cancelled%' then 1 else null end) 1.0 / (count()) as cancellation_rate
from pool_bookings
group by 1
)
select
avg_seats_utilized,
cancellation_rate,
completed_pooled_rides,
c.ride_date
from cancellations c
full outer join avg_seats a
on c.ride_date=a.ride_date