One-Track Mind

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

We're profiling read-only API users: people who have only ever called the API with GET and never any other method. For each endpoint, count these read-only users, and return the endpoint or endpoints with the highest count, listing them in alphabetical order by endpoint.

Some users only ever read, never write. Find where the read-only crowd gathers.

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

This is a per-user set difference wearing an API-analytics costume. The real question: can you count the users who did GET and nothing but GET, broken down by endpoint? Anyone can write WHERE method = 'GET' and group by endpoint. The trap is that a plain GET filter counts every user who ever made a GET, including the ones who also POST, PUT and DELETE all day. A row filter cannot express 'has GET and has no non-GET'; you need an exclusion against the set of users who ever did something else. Skip that and your 'read-only' population balloons with users who are anything but, and every endpoint count comes out inflated.

Break down the requirements

Step 1: Identify who is NOT read-only (case-insensitive)

Build the exclusion set: every user_id that ever made a non-GET call (UPPER(method) <> 'GET', non-null user_id). Any user in this set is disqualified, no matter how many GETs they also made. Normalize with UPPER so 'get' and 'GET' collapse together.

Step 2: Keep only the read-only users' GET rows

Scan the GET rows (UPPER(method) = 'GET') and drop anyone in the exclusion set with user_id NOT IN (...). A GET row with no user_id falls out on its own: NULL NOT IN (...) is unknown, and COUNT(DISTINCT user_id) would skip it anyway, so this side needs no second guard.

Step 3: Take the top endpoints, ties and all

GROUP BY endpoint counting unique users, then keep every endpoint whose count equals (SELECT MAX(get_only_users) FROM counts) and order by endpoint. Using ORDER BY ... LIMIT 1 here silently drops tied endpoints, and the seed has a three-way tie at 24.

The solution

WITH counts AS (
  SELECT endpoint,
         COUNT(DISTINCT user_id) AS get_only_users
  FROM api_calls
  WHERE UPPER(method) = 'GET'
    AND user_id NOT IN (
      SELECT DISTINCT user_id
      FROM api_calls
      WHERE UPPER(method) <> 'GET'
        AND user_id IS NOT NULL
    )
  GROUP BY endpoint
)
SELECT endpoint, get_only_users
FROM counts
WHERE get_only_users = (SELECT MAX(get_only_users) FROM counts)
ORDER BY endpoint

Common follow-up questions

• How would you extend this to users who only ever used read methods (GET and HEAD)? (Tests generalizing the exclusion set to a method allowlist.)
• What breaks if `method` casing is inconsistent and you normalize only one side of the exclusion? (Tests casing normalization and where it must be applied.)
• How would you rewrite the exclusion as `NOT EXISTS` or a `LEFT JOIN ... IS NULL`, and why is that safer than `NOT IN`? (Tests anti-join alternatives to `NOT IN` and their NULL behavior.)