Uber Data Challenge

Part 1 - SQL Syntax

[2 points]

Given the below subset of Uber's schema, write executable SQL queries to answer the questions below.

Please answer in a single query for each question and assume read-only access to the database (i.e. do not use CREATE TABLE).

1. For each of the cities 'Qarth' and 'Meereen', calculate the 90th percentile difference between Actual and Predicted ETA for all completed trips within the last 30 days.
2. A signup is defined as an event labeled 'sign_up_success' within the events table. For each city ('Qarth' and 'Meereen') and each day of the week, determine the percentage of signups in the first week of 2016 that resulted in a completed trip within 168 hours of the sign up date.

Assume a PostgreSQL database, server timezone is UTC.

Table: trips

Column Name	Datatype
id	integer
client_id	integer (Foreign keyed to events.rider_id)
driver_id	integer
city_id	integer (Foreign keyed to cities.city_id)
client_rating	integer
driver_rating	integer
request_at	Timestamp with timezone
predicted_eta	Integer
actual_eta	Integer
status	Enum('completed', 'cancelled_by_driver', 'cancelled_by_client')

Table: cities

Column Name	Datatype
city_id	integer
city_name	string

Table: events

Column Name	Datatype
device_id	integer
rider_id	integer
city_id	integer
event_name	Enum('sign_up_success', 'attempted_sign_up', 'sign_up_failure')
_ts	Timestamp with timezone

---

Part 2 - Data analysis

[5 points]

Uber's Driver team is interested in predicting which driver signups are most likely to start driving. To help explore this question, we have provided a sample dataset of a cohort of driver signups in January 2015.

The data was pulled a few months after they signed up to include the result of whether they actually completed their first trip. It also includes several pieces of background information gathered about the driver and their car.

We would like you to use this data set to help understand what factors are best at predicting whether a signup will start to drive, and offer suggestions to operationalize those insights to help Uber.

See below for a detailed description of the dataset. Please include any code you wrote for the analysis in your submission PDF and delete the dataset when you have finished with the challenge. Please also call out any data related assumptions or issues that you encounter.

1. Perform any cleaning, exploratory analysis, and/or visualizations to use the provided data for this analysis (a few sentences/plots describing your approach will suffice). What fraction of the driver signups took a first trip? (2 points)
2. Build a predictive model to help Uber determine whether or not a driver signup will start driving. Discuss why you chose your approach, what alternatives you considered, and any concerns you have. How valid is your model? Include any key indicators of model performance. (2 points)
3. Briefly discuss how Uber might leverage the insights gained from the model to generate more first trips (again, a few ideas/sentences will suffice). (1 point)

Please note that this data is fake and does not represent actual driver signup behavior.

Data description (corrected — see "Changes" below)

• id: driver_id
• city_name: name of the city this driver signed up in (anonymized: Strark, Berton, Wrouver)
• signup_os: signup platform of the user (ios web, android web, windows, mac, other; blank for some records where the OS wasn't captured)
• signup_channel: channel the driver signed up from (Paid, Organic, Referral)
• signup_date: date of account creation; local time, in the form M/D/YY
• bgc_date: date of background check consent; in the form M/D/YY (NA if the driver never completed a background check)
• vehicle_added_date: date when the driver's vehicle information was uploaded; in the form M/D/YY (NA if no vehicle was ever added)
• vehicle_make: make of vehicle uploaded, e.g. Honda, Ford, Kia (NA if no vehicle was added)
• vehicle_model: model of vehicle uploaded, e.g. Accord, Prius, 350z (NA if no vehicle was added)
• vehicle_year: year the car was made, in the form YYYY (NA if no vehicle was added)
• first_completed_date: date of the driver's first completed trip; in the form M/D/YY (NA if the driver never completed a first trip)

Deliverable

Submit exactly one PDF.

Your PDF must include:

• your answers to both Part 1 and Part 2
• the SQL queries for Part 1
• your analysis, discussion, and any visualizations for Part 2
• any assumptions or data issues you encountered
• all code you wrote for the analysis, embedded in the PDF

If you include code, it must be executable and sufficient to regenerate every solution artifact shown in the PDF.

Do not submit any files other than the single PDF.