Rate Limit Rules Per Endpoint

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

Threshold rules, endpoint by endpoint.

Tables

rate_limits

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

rate_limits: 10 of 200 rows
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

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

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