# Endpoint With Most GET-Only Users

> Read-only users have a favorite endpoint.

Canonical URL: <https://datadriven.io/problems/endpoint_with_most_get_only_users>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Find the endpoint with the most users who have exclusively used the GET method (case-insensitive). Any user who has ever made a non-GET call is excluded entirely. Ignore records with null user_id. Return the endpoint and its count of GET-only users.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests multi-step reasoning: identify users who exclusively use GET, then find which endpoint those users hit most. It probes set difference logic combined with aggregation.

> **Trick to Solving**
>
> "Only GET" means zero non-GET requests. Use `GROUP BY user_id HAVING COUNT(DISTINCT UPPER(method)) = 1 AND MIN(UPPER(method)) = 'GET'` to find exclusive users.
> 
> 1. Find users whose method set contains only GET
> 2. Filter requests to those users
> 3. Aggregate by endpoint and pick the top one

---

### Break down the requirements

#### Step 1: Identify GET-only users (case-insensitive)

Group by `user_id` with `HAVING COUNT(DISTINCT UPPER(method)) = 1 AND MIN(UPPER(method)) = 'GET'`.

#### Step 2: Filter to those users' requests

Use the GET-only user set to filter `api_calls`.

#### Step 3: Find the top endpoint

`GROUP BY endpoint` and `ORDER BY COUNT(*) DESC LIMIT 1`.

---

### The solution

**GET-only users via set exclusion then top endpoint**

```sql
WITH get_only_users AS (
    SELECT user_id
    FROM api_calls
    GROUP BY user_id
    HAVING COUNT(DISTINCT UPPER(method)) = 1
       AND MIN(UPPER(method)) = 'GET'
)
SELECT endpoint, COUNT(*) AS request_count
FROM api_calls
WHERE user_id IN (SELECT user_id FROM get_only_users)
GROUP BY endpoint
ORDER BY request_count DESC
LIMIT 1
```

> **Cost Analysis**
>
> The CTE scans once for GET-only users. The main query re-scans with an IN filter. Indexes on `(user_id, method)` and `(user_id, endpoint)` help.

> **Interviewers Watch For**
>
> The critical reasoning is defining "GET-only" correctly. Filtering `WHERE method = 'GET'` without the exclusivity check is wrong.

> **Common Pitfall**
>
> Filtering `WHERE UPPER(method) = 'GET'` includes all GET requests from all users, not just users who exclusively use GET.

---

## Common follow-up questions

- How would you extend this to users who use exactly two methods? _(Tests HAVING COUNT(DISTINCT method) = 2.)_
- What if method had inconsistent casing? _(Tests UPPER/LOWER normalization.)_
- How would you solve this without a CTE? _(Tests correlated subquery alternatives.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/endpoint_with_most_get_only_users)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.