Top 2 Callers 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

L4

Problem

For every endpoint in api_calls, we want the users behind its two highest call volumes and the place that volume holds, so users tied on a volume share a place. List the endpoints alphabetically, top place first, and treat calls logged without a user ID as one unattributed caller.

Two top callers per endpoint.

Tables

api_calls

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really is

This is a per-group top-N where N counts call volumes, not users, wearing an endpoint-audit costume. Counting calls per endpoint and user_id is the easy part; the cut is where candidates split. On '/api/v1/auth/login', user 197 leads and users 294 and 391 tie for second. Take exactly two rows per endpoint and one of the tied pair vanishes, with the engine choosing which. Number with gaps instead, and a shared first place labels the next volume 3, which falls out of the cut entirely.

The data hides a second trap: calls logged without a user_id. Grouping keeps them together as one unattributed caller, and on '/api/v1/users' and '/api/v1/auth/logout' that caller makes more calls than anyone else. Filter the NULLs out and both endpoints report a different leader.

Walk to the answer

Step 1: Count calls per endpoint and user_id

GROUP BY endpoint, user_id with COUNT(*) gives one row per caller per endpoint. COUNT(*) counts rows, so the NULL user_id group gets its real call count; COUNT(user_id) would score it zero. The users table adds nothing because the answer is the ID, not the username, so skip the join.

Step 2: Number the volumes inside each endpoint

DENSE_RANK() OVER (PARTITION BY endpoint ORDER BY COUNT(*) DESC) restarts at 1 on every endpoint, gives equal volumes the same place, and hands the next volume the next number. The window runs after the grouping, which is why COUNT(*) can sit inside its ORDER BY.

Step 3: Cut at place 2, one level up

The window value only exists once the SELECT is evaluated, so the rnk <= 2 filter lives in the outer query. Sort by endpoint, then rnk. Users sharing a place can come in any order, so no extra sort key is owed.

SELECT endpoint, user_id, rnk
FROM (
  SELECT
    endpoint,
    user_id,
    DENSE_RANK() OVER (
      PARTITION BY endpoint
      ORDER BY COUNT(*) DESC
    ) AS rnk
  FROM api_calls
  GROUP BY endpoint, user_id
) ranked
WHERE rnk <= 2
ORDER BY endpoint, rnk;

Two rows per endpoint

ROW_NUMBER() or a per-endpoint LIMIT 2 hands out 1 and 2 once each. On '/api/v1/auth/login' that keeps 197 and only one of 294 or 391, and a rerun can swap which. RANK() fails the other way: a tie at 1 jumps to 3.

Two volumes per endpoint

DENSE_RANK() numbers volume levels, so 294 and 391 both get 2 and both survive rnk <= 2. The row count per endpoint follows the ties in the data, not a fixed quota.

SELECT endpoint, user_id, rnk
FROM (
  SELECT
    endpoint,
    user_id,
    ROW_NUMBER() OVER (
      PARTITION BY endpoint
      ORDER BY COUNT(*) DESC
    ) AS rnk
  FROM api_calls
  GROUP BY endpoint, user_id
) numbered
WHERE rnk <= 2
ORDER BY endpoint, rnk;

Common follow-up questions

• How would you return exactly two users per endpoint, the same two every run? (Switch to `ROW_NUMBER()` and add `user_id` to the window's `ORDER BY` so ties break the same way every time.)
• What changes if unattributed calls should not compete for a place? (Filter `user_id IS NOT NULL` before grouping; the leaders of '/api/v1/users' and '/api/v1/auth/logout' change.)
• How would you limit this to the last 30 days on a table partitioned by `call_time`? (Filter `call_time` before the `GROUP BY` so partition pruning cuts the scan, then number the volumes as before.)