# Rate Limit Rules Per Endpoint

> Threshold rules, endpoint by endpoint.

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

Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Vesta Innovations

## Problem

We're documenting the rate-limit configuration for every API endpoint. For each endpoint, surface the smallest and largest allowed values alongside a summary string in the format 'Allowed > X AND Allowed <= Y => Endpoint = /path', so the reference covers every endpoint's range in one place.

## Tables

### 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 6 rows:

| endpoint | min_allowed | max_allowed | summary |
| --- | --- | --- | --- |
| /api/v1/auth | 128 | 800 | Allowed > 128 AND Allowed <= 800 => Endpoint = /api/v1/auth |
| /api/v1/orders | 114 | 786 | Allowed > 114 AND Allowed <= 786 => Endpoint = /api/v1/orders |
| /api/v1/payments | 135 | 765 | Allowed > 135 AND Allowed <= 765 => Endpoint = /api/v1/payments |
| /api/v1/search | 107 | 779 | Allowed > 107 AND Allowed <= 779 => Endpoint = /api/v1/search |
| /api/v1/users | 142 | 772 | Allowed > 142 AND Allowed <= 772 => Endpoint = /api/v1/users |

## Worked solution and explanation

### Why this problem exists in real interviews

This analytics problem uses the `rate_limits` table to evaluate string concatenation for formatted output. Watch how the `endpoint` and `allowed` columns interact in the grouping and filtering logic.

---

### Break down the requirements

#### Step 1: Group by endpoint

`GROUP BY endpoint` aggregates all rate limit records per endpoint.

#### Step 2: Compute min and max allowed

`MIN(allowed)` and `MAX(allowed)` give the range.

#### Step 3: Build the summary string

Concatenate the values into the specified format using `||` string concatenation.

#### Step 4: Order by endpoint

`ORDER BY endpoint` for alphabetical listing.

---

### The solution

**Group by endpoint to find rate limit rules per endpoint**

```sql
SELECT
    endpoint,
    MIN(allowed) AS min_allowed,
    MAX(allowed) AS max_allowed,
    'Allowed > ' || MIN(allowed) || ' AND Allowed <= ' || MAX(allowed) || ' => Endpoint = ' || endpoint AS summary
FROM rate_limits
GROUP BY endpoint
```

> **Cost Analysis**
>
> The query scans `rate_limits` (3,000,000 rows). A covering index on the filter and group columns would reduce I/O. At this scale, the full scan is acceptable but becomes costly if the table grows 10x.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- What would happen to your result if `rate_limits.blocked` contained duplicate values that you did not expect? _(Tests whether the candidate considers data quality issues in `blocked` and uses DISTINCT or deduplication where needed.)_
- If `rate_limits` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `allowed`? _(Tests ability to identify performance hotspots related to `rate_limits.allowed` at scale.)_
- If this query ran as a scheduled job, how would you add monitoring to detect when the result set is suspiciously empty? _(Tests operational awareness around scheduled query jobs.)_

## Related

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