UptoSkills Logo
Login
Delivery Performance RankingMedium

Delivery Performance Ranking

MediumSQLPractice library · +20 XPwindow functionssqlranking

UptoSkills Arena helps delivery companies optimize their operations. As a data analyst, you need to identify the best performing delivery agents each month based on their average delivery time. The goal is to rank agents who have completed deliveries within a given month according to their average delivery completion time.

Tables:

  • deliveries: Contains delivery records.
    • delivery_id (integer, PK)
    • agent_id (integer)
    • delivery_date (date)
    • completion_time_minutes (numeric(5,2))

Return the agent_id, delivery_date (month and year), and their rank within that month, ordered by month, year, and rank.

Handle ties by assigning the same rank and leaving the next rank(s) blank (e.g., 1, 2, 2, 4). NULL completion_time_minutes should be ignored. If no deliveries were made in a month, that month should not appear in the result. If an agent has no deliveries in a month, they should not be ranked for that month. Results should be ordered by year, month, and then rank.

Tables

CREATE TABLE deliveries (
    delivery_id integer PRIMARY KEY,
    agent_id integer NOT NULL,
    delivery_date date NOT NULL,
    completion_time_minutes numeric(5,2)
);

Example data

INSERT INTO deliveries (delivery_id, agent_id, delivery_date, completion_time_minutes) VALUES
(1, 101, '2024-01-15', 30.50),
(2, 102, '2024-01-16', 25.00),
(3, 101, '2024-01-17', 35.00),
(4, 103, '2024-01-18', 28.00),
(5, 102, '2024-01-19', 22.50),
(6, 101, '2024-02-05', 40.00),
(7, 102, '2024-02-06', 38.00),
(8, 103, '2024-02-07', 45.00),
(9, 101, '2024-02-08', 35.00),
(10, 102, '2024-02-09', 32.00),
(11, 101, '2024-03-10', 33.00),
(12, 102, '2024-03-11', 29.00);

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

Hints

Similar problems

More in: SQL and Databases problems