Back to questions
Unfinished Parts [Tesla SQL Interview Question]
Tesla is investigating bottlenecks in their production, and they need your help to extract the relevant data. Write a query that determines which parts have begun the assembly process but are not yet finished.
Assumptions
- Table contains all parts in production.
- A part with no is an unfinished part.
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 |
|---|
| bumper |
Explanation
The only item in the output is "bumper" because step 3 didn't have a finish date.
The dataset you are querying against may have different input & output - this is just an example!
Solution
The table already contains all of the parts that are currently in production, meaning that we do not have to do any additional filtering for the parts that are not in production.
All we need to do is extract the parts that are not yet finished. We can accomplish this by filtering for rows with no data present in the column. We call these missing values .
Some parts might be represented multiple times in the query data because they have several assembly steps that are not yet complete. To solve this, we can group or apply function to obtain the unique parts.
OR
PostgreSQL 14