Write SQL for fares and age-band counts
Company: Uber
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
drivers(driver_id VARCHAR(10), name VARCHAR(100), date_of_birth DATE)
trips(trip_id VARCHAR(10), driver_id VARCHAR(10), trip_date DATE, trip_fare_dollars DECIMAL(10,2), trip_status VARCHAR(20))
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
Tables
drivers(driver_id VARCHAR(10), name VARCHAR(100), date_of_birth DATE)
trips(trip_id VARCHAR(10), driver_id VARCHAR(10), trip_date DATE, trip_fare_dollars DECIMAL(10,2), trip_status VARCHAR(20))
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.
Community answers
Answer by SS
Part(a)
WITH filtered AS (
SELECT
driver_id,
ROUND(AVG(trip_fare_dollars), 2) AS avg_fare_2dp
FROM trips
WHERE trip_fare_dollars IS NOT NULL
AND trip_status = 'completed'
GROUP BY driver_id
HAVING AVG(trip_fare_dollars) > 10
)
SELECT
d.name AS driver_name,
f.avg_fare_2dp
FROM filtered f
INNER JOIN drivers d ON f.driver_id = d.driver_id
ORDER BY f.avg_fare_2dp DESC, d.name ASC;
Part(b)
WITH driver_ages AS (
SELECT
driver_id,
EXTRACT(YEAR FROM DATE '2025-09-01') - EXTRACT(YEAR FROM date_of_birth) -
CASE
WHEN EXTRACT(MONTH FROM date_of_birth) > EXTRACT(MONTH FROM DATE '2025-09-01') THEN 1
WHEN EXTRACT(MONTH FROM date_of_birth) = EXTRACT(MONTH FROM DATE '2025-09-01')
AND EXTRACT(DAY FROM date_of_birth) > EXTRACT(DAY FROM DATE '2025-09-01') THEN 1
ELSE 0
END AS age
FROM drivers
),
age_bands AS (
SELECT
driver_id,
CASE
WHEN age BETWEEN 20 AND 35 THEN '20-35'
WHEN age BETWEEN 36 AND 45 THEN '36-45'
END AS age_band
FROM driver_ages
WHERE age BETWEEN 20 AND 45
)
SELECT
ab.age_band,
COUNT(t.trip_id) AS total_completed_trips
FROM age_bands ab
INNER JOIN trips t ON ab.driver_id = t.driver_id
WHERE t.trip_status = 'completed'
GROUP BY ab.age_band
ORDER BY ab.age_band;