# Bottom Endpoints by POST Volume

> The quietest POST endpoints.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The API deprecation team is looking for endpoints that barely receive any POST traffic, since those are candidates for sunsetting. Show endpoints in the bottom 2 positions by POST call volume, including ties. Return the endpoint, call count, and position.

## Worked solution and explanation

### Why this problem exists in real interviews

Working with `api_calls`, this problem isolates dense ranking to handle ties combined with nested subqueries. The interviewer expects candidates to articulate why `endpoint`, `method`, `status` matter for correctness before touching the keyboard.

> **Trick to Solving**
>
> The prompt mentions ties or "include all". This signals `DENSE_RANK()` instead of `ROW_NUMBER()` or `LIMIT`. `DENSE_RANK` assigns the same rank to tied values without gaps.
> 
> 1. Spot the tie-inclusion language
> 2. Use `DENSE_RANK()` over `ROW_NUMBER()`
> 3. Filter on rank in a wrapping CTE

---

### Break down the requirements

#### Step 1: Filter to qualifying rows

Apply any WHERE filters to build the working set.

#### Step 2: Aggregate by endpoint

`GROUP BY` with `COUNT(*)` computes the metric at the correct grain before ranking.

#### Step 3: Rank with DENSE_RANK

`DENSE_RANK() OVER (ORDER BY COUNT(*) ASC)` assigns ranks. DENSE_RANK preserves ties without gaps.

#### Step 4: Filter to the target rank

Wrap in a CTE and filter `WHERE rnk <= 2` to extract the desired position.

---

### The solution

**Dense-rank for bottom endpoints post volume**

```sql
WITH ranked AS (
    SELECT
        endpoint,
        COUNT(*) AS cnt,
        DENSE_RANK() OVER (ORDER BY COUNT(*) ASC) AS rnk
    FROM api_calls
    GROUP BY endpoint
)
SELECT *
FROM ranked
WHERE rnk <= 2
ORDER BY rnk
```

> **Cost Analysis**
>
> The main table has 80M rows (20 GB). Partitioned on `call_time`, so queries filtering on that column skip most partitions. The window function runs after grouping, so it operates on the reduced result set.

> **Interviewers Watch For**
>
> Interviewers check whether the candidate chooses the right window function variant and can articulate why.

> **Common Pitfall**
>
> Using `ROW_NUMBER()` or `LIMIT` instead of `DENSE_RANK()` silently drops tied rows, producing incorrect results when ties exist.

---

## Common follow-up questions

- The `err_msg` column in `api_calls` has roughly 93% NULLs. How does your query handle those rows, and would the result change if NULLs were replaced with zeros? _(Tests whether the candidate understands how NULLs propagate through aggregation functions and whether their WHERE/JOIN conditions implicitly filter them out.)_
- You used DENSE_RANK here. What changes in the output if you switch to ROW_NUMBER, and which is correct for this problem? _(Tests understanding of tie-handling semantics: DENSE_RANK preserves ties while ROW_NUMBER breaks them arbitrarily.)_
- `call_id` in `api_calls` has ~80M distinct values. What index strategy keeps your query from doing a full table scan? _(Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)_
- Could you express this same logic as a single query without CTEs or subqueries? What readability trade-off does that introduce? _(Tests whether the candidate can flatten nested logic and understands when decomposition aids maintainability.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/bottom_endpoints_by_post_volume)
- [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.