Verbose by Design

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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 distinct path, report its length once the outer slashes are ignored and how many meaningful segments it holds, deepest paths first, and when two paths run equally deep, list them in alphabetical order of the path.

Some paths say in six segments what others say in two.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

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.

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

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
ORDER BY word_count DESC, s.endpoint;

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`.)