Write SQL for car rental utilization by city
Company: Meta
Role: Data Engineer
Category: Coding & Algorithms
Difficulty: hard
Interview Round: Onsite
## SQL / Data Query Prompt (Car Rental)
You are given four tables:
### `user`
- `user_id`
### `location`
- `location_id`
- `city`
### `car`
- `car_id`
- `car_size` (e.g., compact, midsize, suv)
- `location_id` (the car’s current/home location)
- `is_active` (1 if available in fleet)
### `fct_rental`
- `rental_id`
- `car_id`
- `pickup_location_id`
- `pickup_ts`
- `dropoff_ts` (nullable if not yet returned)
## Task
For a given date `D` (e.g., `'2025-01-15'`), compute for **each (city, car_size)**:
1. `rented_cars`: how many **distinct cars** were **rented at any time during date D** (i.e., the rental interval overlaps that date).
2. `inventory_cars`: how many cars of that `car_size` exist in that city’s inventory (based on `car.location_id`, only `is_active = 1`).
3. `utilization_rate = rented_cars / inventory_cars` (as a decimal).
Return rows grouped by `city` and `car_size`.
### Notes
- A rental overlaps date D if it started before the end of D and ended after the start of D. Treat `dropoff_ts` NULL as "still ongoing".
- If `inventory_cars = 0`, return `utilization_rate` as NULL (or avoid division-by-zero).
Overview: This question evaluates proficiency in SQL querying and relational data modeling, focusing on time-interval overlap logic, distinct aggregation across joined tables, null handling, and safe computation of utilization metrics.
Read the full Meta Data Engineer interview experience this question came from
Community answers
Answer by ginb
WITH daily_rentals AS (
-- Count distinct cars rented during the overlap period
SELECT
l.city,
c.car_size,
COUNT(DISTINCT f.car_id) AS rented_cars
FROM fct_rental f
JOIN car c ON f.car_id = c.car_id
JOIN location l ON f.pickup_location_id = l.location_id
WHERE
-- Overlap logic: Start of rental < Day + 1 AND (End of rental > Day OR still out)
f.pickup_ts < '2025-01-16'
AND (f.dropoff_ts >= '2025-01-15' OR f.dropoff_ts IS NULL)
GROUP BY 1, 2
),
inventory AS (
-- Count all active cars currently assigned to those cities
SELECT
l.city,
c.car_size,
COUNT(DISTINCT c.car_id) AS inventory_cars
FROM car c
JOIN location l ON c.location_id = l.location_id
WHERE c.is_active = 1
GROUP BY 1, 2
)
SELECT
i.city,
i.car_size,
COALESCE(r.rented_cars, 0) AS rented_cars,
i.inventory_cars,
-- Handle division by zero and nulls
CASE
WHEN i.inventory_cars > 0 THEN COALESCE(r.rented_cars, 0) * 1.0 / i.inventory_cars
ELSE NULL
END AS utilization_rate
FROM inventory i
LEFT JOIN daily_rentals r
ON i.city = r.city
AND i.car_size = r.car_size
ORDER BY i.city, i.car_size;
Answer by sanikommuharshitha
WITH rented AS (
SELECT
l.city,
c.car_size,
COUNT(DISTINCT r.car_id) AS rented_cars
FROM fct_rental r
JOIN car c
ON r.car_id = c.car_id
JOIN location l
ON c.location_id = l.location_id
WHERE
r.pickup_ts <= DATE '2025-01-15'
AND (r.dropoff_ts IS NULL OR r.dropoff_ts >= DATE '2025-01-15')
GROUP BY l.city, c.car_size
),
inventory AS (
SELECT
l.city,
c.car_size,
COUNT(*) AS inventory_cars
FROM car c
JOIN location l
ON c.location_id = l.location_id
WHERE c.is_active = 1
GROUP BY l.city, c.car_size
)
SELECT
i.city,
i.car_size,
COALESCE(r.rented_cars, 0) AS rented_cars,
i.inventory_cars,
case when i.inventory_cars > 0 then COALESCE(r.rented_cars, 0) * 1.0 / i.inventory_cars
else null end AS utilization
FROM inventory i
LEFT JOIN rented r
ON i.city = r.city AND i.car_size = r.car_size;