Write SQL for fares and age-band counts
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.
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.