Distance per User
ASKED IN INTERVIEWTesla
Problem Statement
Find the total mileage for every car in the registry.
Rules:
- Output columns: car_id, total_miles_driven.
- Include cars with 0 miles.
- Round to 2 decimal places.
- Sort by total_miles_driven DESC.
Tests your understanding of
Aggregation, Joins, COALESCE and Rounding
Input Tables
cars
| car_id(INTEGER) | model(VARCHAR) |
|---|---|
| 1 | Model S |
| 2 | Model 3 |
| 3 | Model X |
trips
| trip_id(INTEGER) | car_id(INTEGER) | miles(DECIMAL) |
|---|---|---|
| 101 | 1 | 12.55 |
| 102 | 1 | 8.45 |
| 103 | 2 | 100.2 |
battery_logs
| car_id(INTEGER) | charge_level(INTEGER) |
|---|---|
| 1 | 85 |
Expected Output
| car_id(INTEGER) | total_miles_driven(DECIMAL) |
|---|---|
| 2 | 100.2 |
| 1 | 21 |
| 3 | 0 |
Tags
HardASKED IN INTERVIEWAggregationJoinsCOALESCERounding
15-25 min
52%