Delhivery mid NULLS LASTORDER BYAVGGROUP BY

For each hub, return hub, shipments (all shipments), delivered (shipments with a non-NULL transit_days) and avg_transit_days (average transit_days, rounded to 2 decimals). Order fastest first by avg_transit_days ascending, but hubs with no deliveries yet (NULL average) must appear at the bottom — use NULLS LAST — and break ties by hub.

Delhivery · Shipment Data Quality — practice this real-world SQL scenario live in your browser.

📖 Story Delhivery · Shipment Data Quality

Delhivery's ops dashboard ranks sorting hubs from fastest to slowest by average transit days, and the top of that list is what regional managers read first. transit_days is filled in only when a parcel is delivered; for parcels still on the road it is NULL — 'not delivered yet', not 0 days. The new Guwahati hub went live this week and hasn't completed a single delivery, so its average is NULL. After launch, the dashboard showed Guwahati as the number-one fastest hub in the country.

🎯 Your Mission

📋 Table Structure

🗂 delhivery_shipments

awb TEXT DL1001

hub TEXT Bhiwandi

dest_city TEXT Mumbai

pincode TEXT 400001

transit_days INTEGER 2

⚡ Step-by-Step Walkthrough

See each hub's shipments and their transit days

query.sql

SELECT hub, awb, transit_days
FROM delhivery_shipments
ORDER BY hub, awb;

💡 Explanation

16 shipments across 5 hubs: Bengaluru 4, Bhiwandi 3, Guwahati 2, Gurugram 4 and Kolkata 3 — these are the shipments counts the final result should show.
Four rows have transit_days = NULL (DL1003, DL1013, DL1015 and DL1016): those parcels are still in transit, so there is no transit time to report yet.
Both Guwahati shipments are NULL, so Guwahati has zero completed deliveries; its average isn't 'very fast', it's genuinely unknown.
Bhiwandi and Kolkata each have one in-transit parcel too, so their averages will be built from 2 delivered parcels rather than 3 — AVG quietly ignores the NULL rows.

The trap: plain ORDER BY puts the NULL average on top

query.sql

SELECT hub,
       COUNT(*) AS shipments,
       COUNT(transit_days) AS delivered,
       ROUND(AVG(transit_days), 2) AS avg_transit_days
FROM delhivery_shipments
GROUP BY hub
ORDER BY avg_transit_days;

💡 Explanation

Guwahati is row 1, ahead of Bengaluru at 1.5 days — a hub with zero deliveries is presented as the fastest in the network.
SQLite treats NULL as smaller than every other value when sorting, so in ascending order NULLs come first; MySQL does the same, while PostgreSQL and Oracle do the opposite and put NULLs last in ascending order.
That means the same ORDER BY can give a different 'fastest hub' depending on which database runs the report, so relying on the default is fragile even when it happens to look right.
Add LIMIT 3 for a 'top 3 fastest hubs' widget and the damage gets worse: Guwahati, Bengaluru and Gurugram are shown, and Bhiwandi, the real number three at 2.5 days, is pushed off the list.

Fix it: ORDER BY ... ASC NULLS LAST

query.sql

SELECT hub,
       COUNT(*) AS shipments,
       COUNT(transit_days) AS delivered,
       ROUND(AVG(transit_days), 2) AS avg_transit_days
FROM delhivery_shipments
GROUP BY hub
ORDER BY avg_transit_days ASC NULLS LAST, hub;

💡 Explanation

Result: Bengaluru 1.5, Gurugram 2.25, Bhiwandi 2.5, Kolkata 3.5, then Guwahati with delivered = 0 and avg_transit_days = NULL at the bottom, where 'no data yet' belongs.
NULLS LAST (and NULLS FIRST) overrides the database's default NULL placement for that one sort key; SQLite has supported it since 3.30, and it makes the query behave the same on SQLite, MySQL 8 and PostgreSQL.
Guwahati stays in the result instead of being filtered out, which matters for a dashboard — managers should still see that the hub exists and has 2 parcels in flight, just not ranked above hubs that actually delivered.
Bhiwandi's 2.5 and Kolkata's 3.5 each come from only 2 delivered parcels out of 3, which is why the delivered column sits next to the average — a reader can tell how many real data points support each number.

▶ Practice this live ← Browse all questions