Unfinished Parts
Tesla is investigating bottlenecks in their production, and they need your help to extract the relevant data. Write a SQL query that determines which parts have begun the assembly process but are not yet finished.
Assumption
- Table contains all parts in production.
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.
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 to obtain the unique parts.
PostgreSQL 14