# One-Track Mind > Some users only ever read, never write. Find where the read-only crowd gathers. Canonical URL: 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. ## 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 simple 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 exclusivity check and your 'read-only' population balloons with users who are anything but, and every endpoint count comes out inflated and wrong. > **Trick to solving** > > 'Read-only' means the user appears with GET and never with any other method. Build the set of users who ever made a non-GET call, then keep only GET rows from users NOT in that set. Count distinct such users per endpoint, then keep every endpoint tied at the maximum. --- ### 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'`, non-null `user_id`) and drop anyone present in the exclusion set with `user_id NOT IN (...)`. What survives is exactly the read-only users' GET traffic. #### Step 3: Take the top endpoints, ties and all `GROUP BY endpoint` counting distinct 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 sample already has three-way ties at 24. --- ### The solution **GET-only users via set exclusion, then the top endpoints** ```sql WITH counts AS (SELECT endpoint, COUNT(DISTINCT user_id) AS get_only_users FROM api_calls WHERE UPPER(method)='GET' AND user_id IS NOT NULL 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 ``` > **Cost analysis** > > The inner subquery gathers every disqualified user once; the outer scan filters GET rows against it and counts distinct users per endpoint. Composite indexes on `(user_id, method)` and `(endpoint, method, user_id)` let the engine avoid re-reading the 200M-row table twice in full. > **Interviewers watch for** > > The whole problem lives in the word 'only'. A candidate who filters `WHERE UPPER(method) = 'GET'` and stops has answered a different question: most-hit endpoint among all GET traffic, not among users who exclusively read. Watch for whether they build the exclusion at all. > **Common pitfall** > > Filtering `WHERE UPPER(method) = 'GET'` alone counts every user who ever made a GET, including heavy writers who also GET. The exclusivity has to be enforced by removing anyone who appears with a non-GET method. --- ## 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 forget to normalize on both sides of the exclusion? _(Tests casing normalization and where it must be applied.)_ - How would you rewrite the exclusion as a LEFT JOIN ... IS NULL anti-join, and why might that be safer than NOT IN? _(Tests correlated-subquery and anti-join alternatives to NOT IN.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/one_track_mind) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.