Trim Search Terms Left
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
- Amazon
Problem
A data quality check flagged leading whitespace in some search terms. Return a cleaned-up list showing each search term with the leading spaces stripped, by query ID.
Leading whitespace. Clean it up.
Tables
search_queries
Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT
| query_id | user_id | search_term | results_count | clicked_result | query_time |
|---|---|---|---|---|---|
| 2071 | 100 | laptop deals | 17 | 2 | 2026-02-02 01:11:00 |
| 2142 | 197 | phone case iphone 15 | 34 | 3 | 2026-03-03 02:22:00 |
| 2213 | 294 | usb-c cable 6ft | 51 | NULL | 2026-04-04 03:33:00 |
| 2284 | NULL | gaming mouse | 68 | 5 | 2026-05-05 04:44:00 |
| 2355 | 488 | mechanical keyboard | 85 | 6 | 2026-06-06 05:55:00 |
| 2426 | 585 | 4k monitor | 102 | NULL | 2026-07-07 06:06:00 |
| 2497 | 682 | standing desk | 119 | 8 | 2026-08-08 07:17:00 |
| 2568 | NULL | webcam 1080p | 136 | 9 | 2026-09-09 08:28:00 |
| 2639 | 876 | bluetooth speaker | 153 | NULL | 2026-01-10 09:39:00 |
| 2710 | 973 | smart watch | 170 | 1 | 2026-02-11 10:50:00 |
Expected output
| query_id | search_term |
|---|---|
| 2071 | laptop deals |
| 2142 | phone case iphone 15 |
| 2213 | usb-c cable 6ft |
| 2284 | gaming mouse |
| 2355 | mechanical keyboard |
Worked solution and explanation
Why this problem exists in real interviews
This tests basic string function knowledge. Interviewers use data quality cleanup scenarios to check that you know LTRIM and distinguish it from TRIM and RTRIM.
Break down the requirements
Step 1: Apply LTRIM to strip leading spaces
LTRIM(search_term) removes leading whitespace from each search term.
Step 2: Return by query ID
Select query_id and the cleaned search term.
The solution
SELECT query_id, LTRIM(search_term) AS search_term
FROM search_queriesCommon follow-up questions
- How would you find all search terms that have leading whitespace? (Filter WHERE `search_term` != LTRIM(`search_term`).)
- What if you needed to normalize both leading and trailing whitespace? (Use TRIM(`search_term`) instead of LTRIM.)
- How would you also collapse multiple internal spaces to one? (Tests REPLACE or `REGEXP_REPLACE` for internal whitespace normalization.)