Trim Endpoints Right
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- Stripe
Problem
Our data quality check flagged trailing whitespace on some endpoint values in api_calls. Return every call's call_id with its endpoint stripped of trailing whitespace only, leaving the rest of each value exactly as logged.
Trailing whitespace. Clean it up.
Tables
api_calls
Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT
| call_id | endpoint | method | status | latency | user_id | call_time | err_msg |
|---|---|---|---|---|---|---|---|
| 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL |
| 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL |
| 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL |
| 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL |
| 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL |
| 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found |
| 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized |
| 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded |
| 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error |
| 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway |
Expected output
| call_id | endpoint |
|---|---|
| 8079 | /api/v1/orders |
| 8158 | /api/v2/products |
| 8237 | /api/v1/search |
| 8316 | /api/v1/auth/login |
| 8395 | /api/v1/auth/logout |
Worked solution and explanation
What this really is
This is a one-sided cleanup dressed up as a data quality ticket. The skill being probed is whether you change exactly what the check flagged and nothing else. Anyone can reach for a trim function. The trap is reaching for TRIM, which strips both ends. On this seed, call 9738 is logged as ' /api/v2/products' with a leading space, so a two-sided trim rewrites a value the ticket never mentioned, and that row stops matching.
Step 1: Read the scope off the symptom
The check flagged trailing whitespace, and call 8869 shows it: '/api/v1/orders ' ends in a space. The fix covers the right side only, so the function has to be direction-specific: RTRIM(endpoint).
Step 2: Keep every call
Nothing is filtered out. A clean endpoint passes through RTRIM unchanged, so all 670 calls come back next to their call_id, the key a downstream fix would join on.
Step 3: Alias the result back to endpoint
Naming the cleaned value endpoint makes the output read as a corrected copy of the same column. There is no ORDER BY: the ticket asks for corrected values, not a sorted report.
SELECT call_id, RTRIM(endpoint) AS endpoint
FROM api_calls- Two-sided trim
TRIM(endpoint)fixes call 8869 but also turns ' /api/v2/products' on call 9738 into '/api/v2/products'. One row is off, and the fix has edited data outside its scope.- Right-side trim
RTRIM(endpoint)fixes call 8869 and leaves call 9738 exactly as logged, which is all the check asked for.
Common follow-up questions
- What if you needed to remove trailing tabs and newlines as well? (Some engines' `RTRIM` only handles spaces; you may need `RTRIM` with a character set or `REGEXP_REPLACE`.)
- How would you find which rows actually had trailing whitespace? (Compare `endpoint <> RTRIM(endpoint)` to identify affected rows.)
- Should you update the data in place or create a view? (Tests understanding of data mutation vs. transformation-on-read patterns.)