Delhivery easy TRIMUPPERREPLACEGROUP BY

Normalize dest_city with UPPER(TRIM(REPLACE(dest_city, ' ', ' '))) — collapse the double space, strip leading/trailing spaces, and make it upper case — and call it city. For each city return city, shipments (all shipments) and delivered (shipments whose transit_days is not NULL). Order by shipments descending, then city.

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

📖 Story Delhivery · Shipment Data Quality

Delhivery's planning team counts shipments per destination city to decide where to open new delivery centres. dest_city is free text from thousands of sellers, so the same city arrives in many spellings: 'Bengaluru', ' BENGALURU', 'bengaluru', 'Kolkata' and 'Kolkata ' with a trailing space, and even 'New Delhi' with two spaces in the middle. A straight GROUP BY dest_city treats every variation as a different city, so no city looks big enough to justify a new centre. The report also needs a delivered count, where transit_days IS NULL means the parcel hasn't been delivered yet.

🎯 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

Group by the raw city text and make hidden spaces visible

query.sql

SELECT REPLACE(dest_city, ' ', '_') AS city_spaces_shown,
       LENGTH(dest_city) AS len,
       COUNT(*) AS shipments
FROM delhivery_shipments
GROUP BY dest_city
ORDER BY dest_city;

💡 Explanation

16 shipments produce 16 groups — every single row is its own 'city' with a count of 1, so by this report no destination has more than one parcel.
REPLACE(dest_city, ' ', '_') swaps every space for an underscore so you can see whitespace a results table would otherwise hide: '_BENGALURU' has a leading space, 'Kolkata_' and 'mumbai_' have trailing spaces, and 'New__Delhi' (len 10, against 9 for 'New_Delhi') has two spaces between the words.
GROUP BY compares text exactly, so 'Bengaluru', ' BENGALURU' and 'bengaluru' are three different strings — different case and different spaces are enough to split one city into three groups.
The ORDER BY also shows the case problem: SQLite sorts upper-case letters before lower-case ones, so 'bengaluru', 'mumbai ' and 'new delhi' land at the very bottom, far away from their properly capitalized spellings.

Halfway there: UPPER(TRIM()) still misses the double space

query.sql

SELECT UPPER(TRIM(dest_city)) AS city, COUNT(*) AS shipments
FROM delhivery_shipments
GROUP BY city
ORDER BY city;

💡 Explanation

TRIM removes spaces from both ends and UPPER makes the case uniform, so BENGALURU now has 3 shipments, and KOLKATA and MUMBAI have 2 each — 16 groups shrink to 11.
NEW DELHI is still split: 'NEW DELHI' shows 2 and 'NEW DELHI' shows 1, because TRIM only touches the ends of a string and never looks at the spaces between words.
GROUP BY city works here because SQLite lets you group by a SELECT alias; the grouping happens on the cleaned value, not on the original dest_city, which is exactly what you want.
This version looks finished at a glance, which is what makes it dangerous — one leftover variant hides a third of New Delhi's volume, and nothing in the output flags it.

Fix it: REPLACE the double space first, then TRIM and UPPER

query.sql

SELECT UPPER(TRIM(REPLACE(dest_city, '  ', ' '))) AS city,
       COUNT(*) AS shipments,
       COUNT(transit_days) AS delivered
FROM delhivery_shipments
GROUP BY city
ORDER BY shipments DESC, city;

💡 Explanation

REPLACE(dest_city, ' ', ' ') turns every two-space run into one space, which makes 'New Delhi' identical to 'New Delhi' before TRIM and UPPER run; New Delhi now has 3 shipments and the result has 10 cities.
BENGALURU and NEW DELHI tie at the top with 3 shipments each, then KOLKATA and MUMBAI with 2 — the city with the most demand was invisible in step 1, where every city had exactly 1.
delivered = COUNT(transit_days) counts only non-NULL values, so MUMBAI shows 2 shipments but 1 delivered (DL1003 is still in transit), and BHUBANESWAR, GUWAHATI and SHILLONG show 0 delivered.
REPLACE makes one pass, so three spaces in a row would become two and still not match; for messier data you'd nest REPLACE twice or clean the column once on write instead of in every report.

▶ Practice this live ← Browse all questions