Instructions: solve the levels in ascending order, and send your code and data at guillemette@drivy.com when you’re done. We love SQL tricks, but we also value your ease to write code. You can use any langage you like as long as you have a strategy to deploy and maintain this code.
Goal: Provide the BI team a report of the occupancy rate of the fleet per city.
For a certain half-day, we define the occupancy rate as the nb of rented cars / nb of available cars.
For a half day T, a car is considered available by default. This default state can be changed to:
- rented: the car is booked over T
- unavailable: the owner has declared not wanting to rent over T. The car hence gets hidden from search results and cannot receive rental requests
Each unavailability is a period defined by a start datetime and end datetime. Level of detail is half a day (AM/PM).
Rentals also are periods defined by a start datetime and end datetime with a level of detail of half a day.
Cars have a created_at datetime: they do not exist on the website before this date.
Example of calendar:
Remarks regarding data quality: rentals and unavailabilities are clean: there is no overlap between rentals, between unavailabilities or between rentals and unavailabilities. car_id and dates are also clean: no null value or unexpected value. cars: all fields are clean except created_at (created_at can be NULL)
Compute the state of each car for each half day in 2015. State can be available , rented or unavailable. As a simplification you can consider that the cars with a NULL created_at field were created in 2014.
We now want to fix the NULL created_at: For each car with a NULL created_at, we will consider that they were created on the same datetime as the previous car in the database (ie. the car with the previous id). Fix the NULL values and recompute the state of each car for each half day in 2015.
Compute the occupancy rate per week per city
We now have the data needed by the BI team and would like to automate a weekly reporting. Describe in a few lines how you would automate calculations and automatically send an extract every week.
- cars: http://cl.ly/eDaw
- rentals: http://cl.ly/eDUn
- unavailabilities: http://cl.ly/eDd7