All Known Endpoints

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

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

The platform team is assembling a service catalog of every endpoint that appears in either the API request logs or the rate limit rules. Authentication has several sub-routes nested under /api/v1/auth, but the catalog should show the auth service just once as /api/v1/auth; likewise, paths that differ only by a trailing slash are the same endpoint. Return the full list of endpoints.

Two tables. One truth. Every endpoint accounted for.

Tables

api_calls

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

rate_limits

Columns: limit_id INTEGER, client TEXT, endpoint TEXT, allowed INTEGER, blocked INTEGER, window TEXT, checked TEXT

Expected output

Worked solution and explanation

What this really is

Strip the service-catalog costume and this is a set union with two namespace roll-ups hiding inside it. The two tables each carry an endpoint column; the ask is the union of those two sets. Anyone can write that union. The trick is what counts as one endpoint. /api/v1/auth, /api/v1/auth/login and /api/v1/auth/logout are the same auth service written three ways, and /api/v1/users and /api/v1/users/ are the same service separated only by a trailing slash. A plain dedup keeps all of them. You have to rewrite each raw path to its canonical form BEFORE the dedup, on both axes. Miss either one and your catalog ships nine or ten endpoints where the truth is seven.

Walk it

Step 1: Pool both sources without losing rows

Stack the endpoint column from api_calls and rate_limits with UNION ALL, not UNION, and TRIM off any stray whitespace as you go. You are going to dedup on the fully rolled-up value anyway, so paying for a distinct pass here on the raw strings is wasted work and, worse, it dedups before the normalizations can do their job.

Step 2: Strip the trailing slash

Fold the trailing-slash twins together. When an endpoint ends in a slash (LIKE '%/'), drop that last character with SUBSTR(endpoint, 1, LENGTH(endpoint) - 1); otherwise leave it alone. Now /api/v1/users/ and /api/v1/users are the same string, and /api/v2/products/ collapses into /api/v2/products. Skip this and those twins each keep a separate catalog row.

Step 3: Roll auth sub-routes up to the service

Roll auth sub-routes up to the service. Wrap the cleaned path in a CASE: when it matches LIKE '/api/v1/auth/%', rewrite it to '/api/v1/auth'; otherwise leave it. The slash right before the wildcard is the quiet correctness detail: it matches only nested sub-routes, so the bare /api/v1/auth service passes through untouched. Drop that slash (LIKE '/api/v1/auth%') and you would also swallow anything like /api/v1/authorize, misgrouping unrelated services.

Step 4: Collapse and order

Apply DISTINCT to the fully normalized expression so the auth sub-routes and the slash twins each fold into one row, then ORDER BY the result ascending. The sort runs on the normalized string, so each service sorts in exactly one place.

The solution

SELECT DISTINCT
    CASE
        WHEN service LIKE '/api/v1/auth/%'
            THEN '/api/v1/auth'
        ELSE service
    END AS endpoint
FROM (
    SELECT
        CASE
            WHEN cleaned LIKE '%/'
                THEN SUBSTR(cleaned, 1, LENGTH(cleaned) - 1)
            ELSE cleaned
        END AS service
    FROM (
        SELECT TRIM(endpoint) AS cleaned FROM api_calls
        UNION ALL
        SELECT TRIM(endpoint) AS cleaned FROM rate_limits
    )
)

Plain UNION

SELECT endpoint FROM api_calls UNION SELECT endpoint FROM rate_limits. Dedups on the raw string, so /api/v1/auth, /api/v1/auth/login, /api/v1/auth/logout, /api/v1/users and /api/v1/users/ all survive. Returns nine or ten rows: several are the same service written multiple ways.

Normalize then dedup

Strip the trailing slash and roll auth sub-routes up inside CASEs, then DISTINCT over that. The variants normalize to a single canonical form and fold together. Returns the correct 7 rows, one per real service.

Common follow-up questions

• Suppose other services also grow sub-routes, like /api/v1/users/{id}/orders. How would you generalize the roll-up so it is not hardcoded to auth? (Tests whether the candidate generalizes the roll-up rule beyond a single hardcoded namespace and thinks about extracting a path prefix.)
• The catalog team now wants, next to each service, which source it came from: `api_calls` only, `rate_limits` only, or both. How does the query change? (Pushes from a pure union toward tracking membership, which forces a full outer join on the normalized key or a grouped tag aggregation.)
• Query parameters appear on some logged paths, like /api/v1/search?q=foo. Should those collapse to /api/v1/search, and how would you do it safely? (Tests string handling (split on '?') and judgment about what 'the same endpoint' means once you go beyond path structure.)