High-Traffic Endpoints in February

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

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

City and County of San Francisco

Problem

An endpoint only counts as high-traffic once it logs 80 or more calls, every call included, within a single month. Reading the api_calls log, how many endpoints were high-traffic in February 2026?

When traffic spikes, some endpoints get buried. How many crossed the line?

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 really is

This is a threshold on groups dressed up as a traffic report. The question never asks for a list of endpoints. It asks how many clear a bar, and the bar belongs to each endpoint's February total, not to any single row of api_calls. Anyone can count endpoints. What separates candidates is counting only the ones whose own volume survives the cut, measured inside the month. Skip the cut and you report 10, three of them with a single call. Skip the month and '/api/v1/search' gets in on its all-time 86.

Step 1: Scope the rows to February 2026

The window is a fact about each call: it happened in February or it did not. That makes it a WHERE condition, applied before anything is counted. strftime('%Y-%m', call_time) = '2026-02' keeps every day and hour of that month and nothing else.

Step 2: Total each endpoint, then apply the bar

GROUP BY endpoint turns the month into one row per endpoint, and HAVING COUNT(*) >= 80 keeps the ones whose total clears the bar. COUNT(*) counts calls, not callers: ten calls from one user are ten calls, and an error response still hit the endpoint.

Step 3: Count the survivors

The inner query is the list of busy endpoints, and the outer COUNT(*) collapses it to the single number asked for. Naming the inner result busy_endpoints makes the two levels read like the sentence they implement.

SELECT COUNT(*) AS endpoint_count
FROM (
    SELECT endpoint
    FROM api_calls
    WHERE strftime('%Y-%m', call_time) = '2026-02'
    GROUP BY endpoint
    HAVING COUNT(*) >= 80
) AS busy_endpoints

Counts every endpoint seen

COUNT(DISTINCT endpoint) over February returns 10. '/api/v1/payments', '/api/v1/users/' and '/api/v2/products/' each logged one call there and still count.

Counts endpoints that cleared 80

Totalling per endpoint first and filtering with HAVING returns 3: '/api/v1/users' (111), '/api/v1/orders' (106) and '/api/v2/products' (101). '/api/v1/search' stops at 61.

Common follow-up questions

• The log also holds padded spellings such as '/api/v2/products' with a leading space. What changes if those start landing in February? (Tests normalizing a dirty grouping key with `TRIM` before totals are compared to the bar.)
• Return the qualifying endpoints with their call counts, busiest first, instead of a single number. (Tests dropping the outer count and ordering the grouped result.)
• For every month of 2026, how many endpoints cleared the bar? (Tests moving the month from a filter into the grouping key.)
• If the bar should count only successful calls, what would you confirm before adding a `status` filter? (Tests scoping a business definition before changing a row-level predicate.)