UptoSkills Logo
Login
Consecutive Delivery DelaysHard

Consecutive Delivery Delays

HardSQLPractice library · +30 XPlag and leadsqlwindow functions

UptoSkills Arena's food delivery partner, 'QuickBites', wants to identify drivers who are consistently late. They are particularly interested in drivers who have had at least two consecutive delayed deliveries. Each delivery record includes the driver ID, the scheduled delivery time, the actual delivery time, and a flag indicating if the delivery was delayed.

Tables:

  • deliveries (delivery_id INT, driver_id INT, scheduled_time TIMESTAMP, actual_time TIMESTAMP, is_delayed BOOLEAN)

Return the delivery_id, driver_id, and scheduled_time for all deliveries that were part of a sequence of at least two consecutive delayed deliveries for the same driver. The results should be ordered by driver_id and then by scheduled_time.

Handle NULLs: is_delayed can be NULL, which should not be considered a delay. Empty results are possible. Ties in scheduled_time for the same driver are handled by the database's default timestamp ordering.

Tables

CREATE TABLE deliveries (
    delivery_id SERIAL PRIMARY KEY,
    driver_id INTEGER NOT NULL,
    scheduled_time TIMESTAMP NOT NULL,
    actual_time TIMESTAMP NOT NULL,
    is_delayed BOOLEAN
);

Example data

INSERT INTO deliveries (delivery_id, driver_id, scheduled_time, actual_time, is_delayed) VALUES
(1, 101, '2025-03-14 10:00:00', '2025-03-14 10:15:00', TRUE),
(2, 101, '2025-03-14 11:00:00', '2025-03-14 11:20:00', TRUE),
(3, 101, '2025-03-14 12:00:00', '2025-03-14 12:10:00', FALSE),
(4, 102, '2025-03-15 09:00:00', '2025-03-15 09:30:00', TRUE),
(5, 102, '2025-03-15 10:00:00', '2025-03-15 10:15:00', TRUE),
(6, 102, '2025-03-15 11:00:00', '2025-03-15 11:10:00', TRUE),
(7, 103, '2025-03-16 14:00:00', '2025-03-16 14:05:00', FALSE),
(8, 103, '2025-03-16 15:00:00', '2025-03-16 15:10:00', TRUE),
(9, 104, '2025-03-17 08:00:00', '2025-03-17 08:10:00', TRUE),
(10, 104, '2025-03-17 09:00:00', '2025-03-17 09:25:00', FALSE),
(11, 105, '2025-03-18 13:00:00', '2025-03-18 13:15:00', TRUE);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems