Idle Forklift Shifts
Problem
A warehouse tracks every forklift action in the table `forklift_log`, one row per action, with the `entry_id`, the `vehicle_id`, the `logged_at` timestamp, the `action_kind` (such as 'startup', 'travel', 'lift', or 'load'), the `shift_code` the action belongs to, and an optional `payload_kg`. Operations wants to flag *idle shifts* that look busy but accomplish little. A shift is idle when ALL of these hold: it spans more than 30 minutes from its first to its last action; it has at least 5 'travel' actions; the ratio of 'lift' actions to 'travel' actions is below 0.2; and it recorded no 'load' actions at all. Return each idle shift's `shift_code` and its number of travel actions as `travel_count`. Order by `travel_count` descending, then by `shift_code` ascending.
Tables
Example rows — the live problem includes the full dataset.
| entry_id | vehicle_id | logged_at | action_kind | shift_code | payload_kg |
|---|---|---|---|---|---|
| 1 | 7 | 2024-02-01 08:00:00 | startup | F001 | |
| 2 | 7 | 2024-02-01 08:05:00 | travel | F001 | |
| 3 | 7 | 2024-02-01 08:12:00 | travel | F001 |
Expected output
Your answer should return 2 rows with the columns shift_code, travel_count.
Starter code (SQL)
SELECT *
FROM forklift_log;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock