Build Taxi Driver Performance Features
Boston Consulting Group● EasyINTERNOAProblem statement
Build one performance-feature row for every driver using drivers, cars, and the four ride partitions rides_1 through rides_4.
Use 2023-04-15 as today. Join each driver to the assigned car and return the result columns in the declared order.
days_since_inspectionis the calendar-day difference between2023-04-15andlast_inspection_date.experienceis2023 - started_driving_year.number_of_rejected_ridescounts rides whose status is exactly"Rejected by the driver".number_of_upvotessums all true values across the four upvote columns.number_of_complaintsandnumber_of_incidentscount true values in their corresponding ride columns.
Concatenate all four ride partitions before aggregation. Retain drivers with no rides and set all four ride-derived counts to 0.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
drivers
Driver attributes as of 2023-04-15.
| Column | Type | Nullable | Description |
|---|---|---|---|
| driver_idPK | Integer | No | — |
| car_id | Integer | No | — |
| age | Integer | Yes | — |
| started_driving_year | Integer | No | — |
| second_language | Text | No | — |
| rating | Decimal | No | — |
| net_worth_of_tips | Decimal | No | — |
| driver_class | Text | No | — |
cars
Car attributes and inspection dates.
| Column | Type | Nullable | Description |
|---|---|---|---|
| car_idPK | Integer | No | — |
| model | Text | No | — |
| manufacture_year | Integer | No | — |
| last_inspection_date | Date | No | — |
rides_1
Ride partition 1.
| Column | Type | Nullable | Description |
|---|---|---|---|
| ride_idPK | Integer | No | — |
| driver_id | Integer | No | — |
| date | Date | No | — |
| status | Text | No | — |
| car_clearness_upvote_given | Boolean | No | — |
| politeness_upvote_given | Boolean | No | — |
| communication_upvote_given | Boolean | No | — |
| punctuality_upvote_given | Boolean | No | — |
| complaint_given | Boolean | No | — |
| incident_occurred | Boolean | No | — |
Foreign key: driver_id → drivers(driver_id)
rides_2
Ride partition 2.
| Column | Type | Nullable | Description |
|---|---|---|---|
| ride_idPK | Integer | No | — |
| driver_id | Integer | No | — |
| date | Date | No | — |
| status | Text | No | — |
| car_clearness_upvote_given | Boolean | No | — |
| politeness_upvote_given | Boolean | No | — |
| communication_upvote_given | Boolean | No | — |
| punctuality_upvote_given | Boolean | No | — |
| complaint_given | Boolean | No | — |
| incident_occurred | Boolean | No | — |
Foreign key: driver_id → drivers(driver_id)
rides_3
Ride partition 3.
| Column | Type | Nullable | Description |
|---|---|---|---|
| ride_idPK | Integer | No | — |
| driver_id | Integer | No | — |
| date | Date | No | — |
| status | Text | No | — |
| car_clearness_upvote_given | Boolean | No | — |
| politeness_upvote_given | Boolean | No | — |
| communication_upvote_given | Boolean | No | — |
| punctuality_upvote_given | Boolean | No | — |
| complaint_given | Boolean | No | — |
| incident_occurred | Boolean | No | — |
Foreign key: driver_id → drivers(driver_id)
rides_4
Ride partition 4.
| Column | Type | Nullable | Description |
|---|---|---|---|
| ride_idPK | Integer | No | — |
| driver_id | Integer | No | — |
| date | Date | No | — |
| status | Text | No | — |
| car_clearness_upvote_given | Boolean | No | — |
| politeness_upvote_given | Boolean | No | — |
| communication_upvote_given | Boolean | No | — |
| punctuality_upvote_given | Boolean | No | — |
| complaint_given | Boolean | No | — |
| incident_occurred | Boolean | No | — |
Foreign key: driver_id → drivers(driver_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| driver_id | Integer | No | — |
| car_model | Text | No | — |
| car_manufacture_year | Integer | No | — |
| days_since_inspection | Integer | No | — |
| age | Integer | Yes | — |
| experience | Integer | No | — |
| second_language | Text | No | — |
| rating | Decimal | No | — |
| net_worth_of_tips | Decimal | No | — |
| number_of_rejected_rides | Integer | No | — |
| number_of_upvotes | Integer | No | — |
| number_of_complaints | Integer | No | — |
| number_of_incidents | Integer | No | — |
| driver_class | Text | No | — |
Row order: any order is accepted. Numeric tolerance: 0.
Constraints
- Every driver references exactly one row in
cars. - Every ride references a row in
drivers. - Every date is a valid ISO calendar date no later than
2023-04-15. started_driving_yearis no later than2023.agemay be null; all other driver and car fields are non-null.- Result row order is ignored.