Top Endpoint by Power Users

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Stitch Fix

Problem

Each row of api_calls is one request a user made to an endpoint, with its HTTP method as the client sent it, and only the uppercase GET, POST, PUT and DELETE count as standard (a lowercase 'get' does not). A user is a power user of an endpoint when standard methods make up at least half of their calls to that endpoint; list every endpoint with its number of power users, most first.

Power users have a favorite 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

Expected output

Worked solution and explanation

What this really is

This is a two-level count wearing a traffic-analytics costume. The standard-method share is defined per user on a single endpoint, so you have to qualify each caller at the (user_id, endpoint) grain first, then count the survivors per endpoint. The trap is computing the standard-method share over all of an endpoint's rows at once. That answers a different question and quietly promotes endpoints dominated by a few heavy callers. Get the grain wrong, or let integer division floor every ratio to 0, and the whole board reorders under you.

Working through it

Step 1: Qualify each user on each endpoint

A power user is defined for a (user_id, endpoint) pair, not for a caller overall. GROUP BY user_id, endpoint so the qualifying test runs once per caller per endpoint; the same person can be a power user of one endpoint and an ordinary caller on another. Filter WHERE user_id IS NOT NULL first so anonymous rows never form a phantom group.

Step 2: Conditional sum, then HAVING

Count the standard calls with SUM(CASE WHEN method IN ('GET', 'POST', 'PUT', 'DELETE') THEN 1 ELSE 0 END) and divide by COUNT(*). Both are aggregates, so the >= 0.5 test belongs in HAVING, never WHERE, where aggregates are invisible. The IN list is case-sensitive, so a lowercase 'get' is correctly treated as non-standard.

Step 3: CAST to REAL before dividing

SUM(...) / COUNT(*) is integer over integer, which floors to 0 for every pair that is not 100% standard. CAST(... AS REAL) on the numerator forces real division, so the 0.5 comparison means what you intend.

Step 4: Count power users, then order endpoints

The qualified cohort holds one row per (user_id, endpoint) pair, so from it you GROUP BY endpoint and take COUNT(*): each row is already a distinct power user of that endpoint. Order the board with ORDER BY power_user_count DESC; endpoints tied on the count may come back in any order.

The solution

WITH power_users AS (
  SELECT
    user_id,
    endpoint
  FROM api_calls
  WHERE user_id IS NOT NULL
  GROUP BY user_id, endpoint
  HAVING CAST(SUM(CASE WHEN method IN ('GET', 'POST', 'PUT', 'DELETE') THEN 1 ELSE 0 END) AS REAL) / COUNT(*) >= 0.5
)
SELECT
  endpoint,
  COUNT(*) AS power_user_count
FROM power_users
GROUP BY endpoint
ORDER BY power_user_count DESC

Common follow-up questions

Common follow-up questions

• How would you return only the single busiest endpoint instead of the full board? (Wrap the board and cut it with `LIMIT 1`. Three endpoints tie at the top on this data, so you must first name a tie rule, such as the endpoint name, and add it to the `ORDER BY` before the cut.)
• What changes if a power user is defined across a user's whole history rather than per endpoint? (The inner grain becomes `GROUP BY user_id` only, then you rejoin to `api_calls` to attribute each qualified caller to the endpoints they touched.)
• How would you scope this to a 30-day rolling window? (Add a `call_time >= '2026-08-13'` predicate before the inner `GROUP BY` and confirm the planner prunes by `call_time`.)
• If `method` could be `NULL`, how would the ratio behave? (`CASE WHEN method IN (...)` yields `0` for a `NULL` method, so nulls count against the share; decide whether to drop them with an extra `WHERE method IS NOT NULL` first.)