Back to questions
Unfinished Parts [Tesla SQL Interview Question]
Tesla is investigating production bottlenecks and they need your help to extract the relevant data. Write a query that determines which parts with the assembly steps have initiated the assembly process but remain unfinished.
Assumptions:
- table contains all parts currently in production, each at varying stages of the assembly process.
- An unfinished part is one that lacks a .
This question is straightforward, so let's approach it with simplicity in both thinking and solution.
Effective April 11th 2023, the problem statement and assumptions were updated to enhance clarity.
Table
| Column Name | Type |
|---|---|
| part | string |
| finish_date | datetime |
| assembly_step | integer |
Example Input
| part | finish_date | assembly_step |
|---|---|---|
| battery | 01/22/2022 00:00:00 | 1 |
| battery | 02/22/2022 00:00:00 | 2 |
| battery | 03/22/2022 00:00:00 | 3 |
| bumper | 01/22/2022 00:00:00 | 1 |
| bumper | 02/22/2022 00:00:00 | 2 |
| bumper | 3 | |
| bumper | 4 |
Example Output
| part | assembly_step |
|---|---|
| bumper | 3 |
| bumper | 4 |
Explanation
The bumpers in step 3 and 4 are the only item that remains unfinished as it lacks a recorded finish date.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Great news! The parts table already includes all parts currently in production, so no additional filtering is necessary to exclude non-production parts.
To extract unfinished parts, we can simply filter for rows where the column contains no data, indicated by a NULL value.
PostgreSQL 14