Battery Cycle Count
ASKED IN INTERVIEWTesla
Problem Statement
Tesla engineers are analyzing the durability of battery packs. We need to find all cars in the fleet that have been utilized more than the average car.
Write an SQL query to find the vehicle_id and cycle_count of all vehicles where the cycle_count is strictly greater than the fleet-wide average.
Rules:
- The "fleet average" is the average cycle_count across all vehicles in the Battery_Stats table.
- Output columns: vehicle_id, cycle_count.
- Results must be sorted by cycle_count in descending order.
Tests your understanding of
Subqueries, Aggregation and IoT Analysis
Input Tables
Vehicles
| vehicle_id(INTEGER) | model(VARCHAR) |
|---|---|
| 1 | Model 3 |
| 2 | Model Y |
| 3 | Model S |
| 4 | Model X |
| 5 | Cybertruck |
Battery_Stats
| vehicle_id(INTEGER) | cycle_count(INTEGER) |
|---|---|
| 1 | 500 |
| 2 | 200 |
| 3 | 1000 |
| 4 | 300 |
| 5 | 100 |
Expected Output
| vehicle_id(INTEGER) | cycle_count(INTEGER) |
|---|---|
| 3 | 1000 |
| 1 | 500 |
Tags
MediumASKED IN INTERVIEWSubqueriesAggregationIoT Analysis
10-15 min
72%