Coding questions · Uber
Write SQL for fares and age-band counts
This question evaluates SQL competencies in aggregation, filtering, joins, numeric formatting, and precise date-based age calculation, including handling NULL fares, canceled trips, leap-year birthdays, and boundary-age cases.
You have two tables.
Schema:
- drivers(driver_id VARCHAR PRIMARY KEY, name VARCHAR, date_of_birth DATE)
- trips(trip_id VARCHAR PRIMARY KEY, driver_id VARCHAR, trip_date DATE, trip_fare_dollars DECIMAL(10,2), trip_status VARCHAR)
Sample data:
drivers
| driver_id | name | date_of_birth |
|---|---|---|
| D1 | Jane Doe | 1996-03-14 |
| D2 | Mark S | 1963-09-16 |
| D3 | Adam L | 1965-05-19 |
| D4 | Jaime L | 1976-05-19 |
trips
| trip_id | driver_id | trip_date | trip_fare_dollars | trip_status |
|---|---|---|---|---|
| T1 | D1 | 2019-01-01 | 17.52 | completed |
| T2 | D1 | 2019-01-02 | 4.40 | completed |
| T3 | D1 | 2019-01-03 | NULL | canceled |
| T4 | D2 | 2019-01-01 | 25.00 | completed |
| T5 | D3 | 2019-01-01 | 8.00 | completed |
| T7 | D2 | 2019-01-02 | NULL | canceled |
Write SQL (one query or two CTEs is fine) to: (a) Return driver names whose average fare over completed trips is strictly greater than 10.00. Exclude non-completed trips and rows where trip_fare_dollars IS NULL. Output columns: driver_name, avg_fare_2dp (rounded to 2 decimals). Order by avg_fare_2dp DESC, then driver_name ASC. Drivers with zero completed trips must not appear. (b) Count completed trips by driver age bands as of 2025-09-01. Use inclusive boundaries for bands: 20–35 and 36–45 (i.e., ages in [20,35] and [36,45]). Compute age from date_of_birth accurately (no 365-day approximation). Output two rows with columns: age_band ('20-35' or '36-45'), total_completed_trips. Ignore drivers outside 20–45. Edge cases to handle: null fares, canceled trips, drivers without completed trips, leap-year birthdays, and band boundary birthdays on 2025-09-01.
Drivers with average completed-trip fare > $10
You are given two tables: drivers and trips.
Write a SQL query to return driver names whose average fare over completed trips is strictly greater than 10.00.
Rules:
- Only include trips where trip_status = 'completed'.
- Exclude rows where trip_fare_dollars IS NULL.
- Drivers with zero qualifying completed trips must not appear.
Output columns:
- driver_name
- avg_fare_2dp (average fare rounded to 2 decimals)
Sort by avg_fare_2dp DESC, then driver_name ASC.
Table drivers
| column | type | constraints |
|---|---|---|
| driver_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| date_of_birth | DATE |
Sample data:
| date_of_birth | driver_id | name |
|---|---|---|
| 1996-03-14 | D1 | Jane Doe |
| 1963-09-16 | D2 | Mark S |
| 1965-05-19 | D3 | Adam L |
| 1976-05-19 | D4 | Jaime L |
| 2004-02-29 | D5 | Lily Leap |
| 1992-07-01 | D6 | Ten Avg |
| 1991-01-15 | D7 | Null Fare |
| 1993-11-10 | D8 | Aaron Z |
| 1990-09-01 | D9 | Boundary35 |
| 1989-09-01 | D10 | Boundary36 |
| 1980-09-01 | D11 | Boundary45 |
| 2005-09-01 | D12 | Boundary20 |
| 1994-06-01 | D13 | Cancel Only |
| 1979-09-01 | D14 | Outside46 |
Table trips
| column | type | constraints |
|---|---|---|
| trip_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| driver_id | VARCHAR(10) | NOT NULL |
| trip_date | DATE | NOT NULL |
| trip_fare_dollars | DECIMAL(10,2) | |
| trip_status | VARCHAR(20) | NOT NULL |
Sample data:
| driver_id | trip_date | trip_fare_dollars | trip_id | trip_status |
|---|---|---|---|---|
| D1 | 2019-01-01 | 17.52 | T1 | completed |
| D1 | 2019-01-02 | 4.4 | T2 | completed |
| D1 | 2019-01-03 | NULL | T3 | canceled |
| D2 | 2019-01-01 | 25 | T4 | completed |
| D3 | 2019-01-01 | 8 | T5 | completed |
| D2 | 2019-01-02 | NULL | T6 | canceled |
| D6 | 2019-02-01 | 10 | T7 | completed |
| D6 | 2019-02-02 | 10 | T8 | completed |
| D7 | 2019-03-01 | NULL | T9 | completed |
| D7 | 2019-03-02 | 12 | T10 | canceled |
| D8 | 2019-04-01 | 30 | T11 | completed |
| D8 | 2019-04-02 | 20 | T12 | completed |
| D5 | 2025-01-05 | NULL | T13 | completed |
| D10 | 2024-12-31 | 15 | T14 | completed |
| D10 | 2025-01-01 | 5 | T15 | completed |
| D11 | 2025-05-20 | 0 | T16 | completed |
| D12 | 2025-02-15 | 7.5 | T17 | completed |
| D13 | 2025-03-10 | 9 | T18 | canceled |
Expected output
One row per driver whose average fare across qualifying completed trips (excluding NULL fares) is > 10.00, with the average rounded to 2 decimals.
| avg_fare_2dp | driver_name |
|---|---|
| 25 | Aaron Z |
| 25 | Mark S |
| 10.96 | Jane Doe |
Hints
- Filter to completed trips and non-NULL fares before aggregating.
- Use HAVING for the average-fare threshold after GROUP BY.
Completed trip counts by age band as of 2025-09-01
Using the same drivers and trips tables, count completed trips by driver age bands as of 2025-09-01.
Requirements:
- Compute age from date_of_birth accurately (do not approximate using 365 days).
- Use inclusive age bands: 20–35 and 36–45.
- Ignore drivers outside ages 20–45.
- Count trips where trip_status = 'completed' (fares may be NULL; still count the trip).
- Output exactly two rows for the age bands, even if a band has 0 trips.
Output columns:
- age_band (either '20-35' or '36-45')
- total_completed_trips
Table drivers
| column | type | constraints |
|---|---|---|
| driver_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| date_of_birth | DATE |
Sample data:
| date_of_birth | driver_id | name |
|---|---|---|
| 1996-03-14 | D1 | Jane Doe |
| 1963-09-16 | D2 | Mark S |
| 1965-05-19 | D3 | Adam L |
| 1976-05-19 | D4 | Jaime L |
| 2004-02-29 | D5 | Lily Leap |
| 1992-07-01 | D6 | Ten Avg |
| 1991-01-15 | D7 | Null Fare |
| 1993-11-10 | D8 | Aaron Z |
| 1990-09-01 | D9 | Boundary35 |
| 1989-09-01 | D10 | Boundary36 |
| 1980-09-01 | D11 | Boundary45 |
| 2005-09-01 | D12 | Boundary20 |
| 1994-06-01 | D13 | Cancel Only |
| 1979-09-01 | D14 | Outside46 |
Table trips
| column | type | constraints |
|---|---|---|
| trip_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| driver_id | VARCHAR(10) | NOT NULL |
| trip_date | DATE | NOT NULL |
| trip_fare_dollars | DECIMAL(10,2) | |
| trip_status | VARCHAR(20) | NOT NULL |
Sample data:
| driver_id | trip_date | trip_fare_dollars | trip_id | trip_status |
|---|---|---|---|---|
| D1 | 2019-01-01 | 17.52 | T1 | completed |
| D1 | 2019-01-02 | 4.4 | T2 | completed |
| D1 | 2019-01-03 | NULL | T3 | canceled |
| D2 | 2019-01-01 | 25 | T4 | completed |
| D3 | 2019-01-01 | 8 | T5 | completed |
| D2 | 2019-01-02 | NULL | T6 | canceled |
| D6 | 2019-02-01 | 10 | T7 | completed |
| D6 | 2019-02-02 | 10 | T8 | completed |
| D7 | 2019-03-01 | NULL | T9 | completed |
| D7 | 2019-03-02 | 12 | T10 | canceled |
| D8 | 2019-04-01 | 30 | T11 | completed |
| D8 | 2019-04-02 | 20 | T12 | completed |
| D5 | 2025-01-05 | NULL | T13 | completed |
| D10 | 2024-12-31 | 15 | T14 | completed |
| D10 | 2025-01-01 | 5 | T15 | completed |
| D11 | 2025-05-20 | 0 | T16 | completed |
| D12 | 2025-02-15 | 7.5 | T17 | completed |
| D13 | 2025-03-10 | 9 | T18 | canceled |
Expected output
Two rows (one per band) containing the total number of completed trips taken by drivers whose age on 2025-09-01 falls within that band.
| age_band | total_completed_trips |
|---|---|
| 20-35 | 9 |
| 36-45 | 3 |
Hints
- To compute accurate age in years, subtract 1 when the birthday in the reference year has not occurred yet.
- To guarantee both bands appear, build a two-row "bands" CTE and LEFT JOIN the computed counts.