# All Known Endpoints

> Two tables. One truth. Every endpoint accounted for.

Canonical URL: <https://datadriven.io/problems/all_known_endpoints>

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.

## 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 |

### rate_limits

- limit_id INTEGER
- client TEXT
- endpoint TEXT
- allowed INTEGER
- blocked INTEGER
- window TEXT
- checked TEXT

Sample rows (10 of 200):

| limit_id | client | endpoint | allowed | blocked | window | checked |
| --- | --- | --- | --- | --- | --- | --- |
| 129 | client-002 | /api/v1/search | 107 | NULL | 5m | 2026-02-02 01:03:00 |
| 158 | client-003 | /api/v1/orders | 114 | NULL | 15m | 2026-03-03 02:06:00 |
| 187 | mobile-app | /api/v2/analytics | 121 | NULL | 1h | 2026-04-04 03:09:00 |
| 216 | partner-api | /api/v1/auth | 128 | 12 | 1d | 2026-05-05 04:12:00 |
| 245 | internal-svc | /api/v1/payments | 135 | NULL | 1m | 2026-06-06 05:15:00 |
| 274 | batch-worker | /api/v1/users | 142 | NULL | 5m | 2026-07-07 06:18:00 |
| 303 | admin-ui | /api/v1/search | 149 | NULL | 15m | 2026-08-08 07:21:00 |
| 332 | webhook-svc | /api/v1/orders | 156 | 24 | 1h | 2026-09-09 08:24:00 |
| 361 | client-010 | /api/v2/analytics | 163 | NULL | 1d | 2026-01-10 09:27:00 |
| 390 | client-001 | /api/v1/auth | 170 | NULL | 1m | 2026-02-11 10:30:00 |

## Expected output

First 5 of 7 rows:

| endpoint |
| --- |
| /api/v1/orders |
| /api/v2/products |
| /api/v1/search |
| /api/v1/auth |
| /api/v2/analytics |

## 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.

> **Trick to solving**
>
> Normalize FIRST, dedup SECOND, and normalize on BOTH axes. Pool every endpoint with UNION ALL (cheap, no early dedup), fold trailing-slash twins together, roll each auth sub-route up to its service root /api/v1/auth, then apply DISTINCT on that fully normalized value. Dedup before normalizing and every variant survives, because as raw strings they are genuinely distinct.

---

### 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

**Union, strip the slash, roll up auth, dedup, order**

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

*The inner query folds the trailing-slash twins; the outer CASE rolls up auth; DISTINCT then collapses to one row per real service.*

**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 pitfall**
>
> Reaching for UNION (which dedups) and assuming you are done. UNION only dedups identical strings; it cannot know that /api/v1/auth/login is the same service as /api/v1/auth, or that a trailing slash is meaningless. The dedup has to happen AFTER you rewrite the variants to a common form, which is exactly why the canonical solution uses UNION ALL plus DISTINCT over normalized CASEs instead.

> **Interviewers watch for**
>
> The candidate who pauses and asks 'do the auth sub-routes roll up, and does a trailing slash matter?' before writing anything. Those two clarifying questions separate people who eyeball the sample output from people who think about what a service catalog actually needs. Bonus tell: guarding the auth match so the bare /api/v1/auth service is not itself swallowed.

> **In production**
>
> `api_calls` is 500M rows partitioned by `call_time`, but this query touches only the endpoint column, which has a few hundred distinct values. The engine hashes that down fast; the dominant cost is the scan, not the dedup or the string work. If this fed a live catalog UI, you would materialize it nightly rather than scan 128 GB on every page load.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/all_known_endpoints)
- [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.