# Verbose by Design > Some paths say in six segments what others say in two. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: City and County of San Francisco ## Problem A platform team is auditing endpoint paths in the request log, where the outer slashes are noise but every inner segment marks a real routing level. For each unique path, report its length once the outer slashes are ignored and how many meaningful segments it holds. ## Tables ### api_calls - call_id INTEGER - endpoint TEXT - method TEXT - status INTEGER - latency REAL - user_id INTEGER - call_time TEXT - err_msg TEXT Sample rows (10 of 670): | 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 First 5 of 12 rows: | endpoint | trimmed_len | word_count | | --- | --- | --- | | /api/v2/products | 17 | 4 | | /api/v1/auth/login | 17 | 4 | | /api/v1/auth/logout | 18 | 4 | | /api/v1/orders | 13 | 3 | | /api/v1/orders | 14 | 3 | ## Worked solution and explanation ### What this problem really is Strip the business framing and this is string tokenization with no tokenizer. SQLite has no `SPLIT_PART`, so you have to cut a path into segments yourself and count only the ones that carry meaning. The move everyone reaches for first is the one-liner: count the slashes with LENGTH minus LENGTH(REPLACE) and add one. It passes on tidy paths and quietly lies the moment a path has an empty segment. '/api//v1' has three slashes but two real segments, and '/api/v1/users/' has a trailing slash that is not a segment at all. Get this wrong and the audit inflates segment counts, firing off cleanup work against routes that were never too deep. > **Trick to solving** > > Tokenize for real with a recursive walk. Seed each path with a trailing '/' appended, then peel one segment at a time using substr and instr on the next '/'. Keep only tokens where seg is not empty, so the empty pieces left by an outer slash or a doubled slash simply drop out. The surviving token count is the true segment count, and LENGTH(TRIM(endpoint, '/')) gives the length with the outer slashes gone. --- ### The one-liner, and where it cracks **LENGTH minus LENGTH(REPLACE)** Count the '/' characters and add one. Fast, single pass, no recursion. But it counts separators, not segments: an empty piece from a doubled slash or an un-trimmed outer slash each adds a phantom to the total. You have to TRIM first, and even then it cannot tell '/a//b' (two real segments) from '/a/b/c' (three). **Recursive tokenization** Walk the path, emit each segment, and discard the empty ones with seg <> ''. It costs a recursive CTE, but it is the only form that counts MEANINGFUL segments: doubled slashes and outer slashes vanish instead of inflating the count. ### Building the query #### Step 1: Collapse to the distinct paths endpoint repeats across millions of log rows, so pull SELECT DISTINCT endpoint first. That is the universe the audit cares about, and it shrinks the recursive work to one pass per unique path rather than per log row. #### Step 2: Seed the split with a trailing slash Start the recursive CTE with an empty seg and rest set to endpoint || '/'. The appended slash guarantees the final segment has a delimiter to stop on, so nothing is lost off the end. #### Step 3: Peel one segment per step Each step takes substr(rest, 1, instr(rest, '/') - 1) as the next segment and substr(rest, instr(rest, '/') + 1) as the remainder, stopping when rest is empty. This is the split that `SPLIT_PART` hands you for free in Postgres. #### Step 4: Keep only meaningful tokens, then count Filter to seg <> '' so leading, trailing, and doubled slashes contribute nothing, then GROUP BY endpoint with COUNT(*) for the segment count and LENGTH(TRIM(endpoint, '/')) for the trimmed length. Order by the count descending, breaking ties by endpoint. --- ### The solution **Tokenize, filter empties, count** ```sql WITH RECURSIVE endpoints AS ( SELECT DISTINCT endpoint FROM api_calls ), split(endpoint, seg, rest) AS ( SELECT endpoint, '', endpoint || '/' FROM endpoints UNION ALL SELECT endpoint, substr(rest, 1, instr(rest, '/') - 1), substr(rest, instr(rest, '/') + 1) FROM split WHERE rest <> '' ), segments AS ( SELECT endpoint, seg FROM split WHERE seg <> '' ) SELECT s.endpoint, LENGTH(TRIM(s.endpoint, '/')) AS trimmed_len, COUNT(*) AS word_count FROM segments s GROUP BY s.endpoint ; ``` > **Cost at 400M rows** > > `api_calls` holds 400M rows but only about 200 distinct endpoints, so the SELECT DISTINCT up front is what saves you: the recursion runs a few hundred times, not 400M. No index helps the distinct scan itself, since every row has to be read to find the distinct values, which is why in production this rides a daily rollup keyed by endpoint rather than the raw log. > **Interviewers watch for** > > Two tells. Does the candidate normalize the outer slashes before measuring length, or special-case them one at a time. And does the candidate notice that counting separators is not the same as counting non-empty segments, then ask what '/a//b' should return. Raising the empty-segment question unprompted reads as senior. > **Common pitfall** > > Skipping the TRIM inflates `trimmed_len` by every outer slash, so '/api/v1/users/' reads 14 instead of 12. The subtler miss is trusting LENGTH minus LENGTH(REPLACE) for the count: it silently overcounts any path with an empty segment, and the bug stays hidden until a doubled slash shows up in production. --- ## Common follow-up questions - How would you extract just the second path segment after trimming? _(In SQLite: `SUBSTR` combined with `INSTR` locates the first and second slash. In Postgres: `SPLIT_PART(TRIM(endpoint, '/'), '/', 2)` does it directly.)_ - What should `word_count` return for an endpoint like `/a//b` with an empty segment? _(`LENGTH - LENGTH(REPLACE)` counts delimiter characters, not non-empty segments, so `/a//b` reports 3 while the recursive filter reports 2. Deciding which is right is a product question.)_ - How would you return only endpoints with more than five segments? _(Wrap the query as a subquery and filter on `word_count > 5` in the outer `WHERE`. You cannot reference the alias directly in the same-level `WHERE`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/verbose_by_design) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.