DB Exercises
0
← Exercises/
Aggregations•5 of 12
PrevNext
AggregationsIntermediatePRO

Fleet Route Delivery SLA Adherence

Scenario

QuickRide Logistics guarantees delivery windows across urban delivery zones. Regional fleet dispatchers need to audit delivery completion times and identify zones failing their 90% on-time target.

Task
Group deliveries in `courier_deliveries` by `delivery_zone`. Calculate: 1. `delivery_zone` 2. `total_packages`: Count of all deliveries 3. `avg_transit_minutes`: Average `transit_minutes` rounded to 1 decimal place 4. `on_time_pct`: Percentage of deliveries marked as on-time (`is_on_time = true`): `ROUND(100.0 * SUM(CASE WHEN is_on_time THEN 1 ELSE 0 END) / COUNT(*), 1)` Order results by `on_time_pct` ascending to see troubled zones first.
Expected Output Format

Delivery metrics per zone showing total packages, average transit time, and on-time percentage.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Fleet Route Delivery SLA Adherence” SQL Problem

Business Scenario

QuickRide Logistics guarantees delivery windows across urban delivery zones. Regional fleet dispatchers need to audit delivery completion times and identify zones failing their 90% on-time target.

Expected Output & Criteria

Delivery metrics per zone showing total packages, average transit time, and on-time percentage.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE courier_deliveries (
        package_id INT PRIMARY KEY,
        courier_id INT NOT NULL,
        delivery_zone VARCHAR(30) NOT NULL,
        transit_minutes INT NOT NULL,
        is_on_time BOOLEAN NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYCOUNTAVGROUND

Want more Aggregations practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Aggregations Track→