Write SQL for car rental utilization by city

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).