# Daily Top Endpoints

> Three winners each day.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Surface the top 3 endpoints by call count for each day. If endpoints are tied, they share the same rank. Each day should have at least ranks 1, 2, and 3. Format dates as YYYY-MM-DD. Return the date, endpoint, and rank.

## Worked solution and explanation

### Why this problem exists in real interviews

Operations dashboards rank endpoints daily so on-call engineers can spot the heaviest traffic at a glance. Interviewers use this prompt to see whether you can mix an aggregate (COUNT per day per endpoint) with a partitioned ranking window (DENSE_RANK per day) and respect the no-gaps tie semantics.

---

### Break down the requirements

#### Step 1: Bucket call_time to the day

DATE(call_time) extracts YYYY-MM-DD in SQLite. GROUP BY DATE(call_time), endpoint to get per-day per-endpoint counts. This is the inner aggregate.

#### Step 2: Rank endpoints within each day with DENSE_RANK

DENSE_RANK() OVER (PARTITION BY DATE(call_time) ORDER BY COUNT(*) DESC) ranks endpoints inside each day with no gaps; ties share a rank and the next distinct count gets the immediately next rank. The PARTITION BY day is what makes the rank reset every day.

#### Step 3: Filter to rnk <= 3 in an outer SELECT

You cannot reference a window function in a WHERE clause directly, so wrap the windowed query in a subquery and filter in the outer SELECT. ORDER BY day ASC, rnk ASC, endpoint ASC for deterministic tie-breaks.

---

### The solution

**Aggregate per day-endpoint, rank per day, filter outside**

```sql
SELECT call_day, endpoint, rnk FROM (
  SELECT DATE(call_time) AS call_day, endpoint, COUNT(*) AS call_count,
         DENSE_RANK() OVER (PARTITION BY DATE(call_time) ORDER BY COUNT(*) DESC) AS rnk
  FROM api_calls
  GROUP BY DATE(call_time), endpoint
) ranked
WHERE rnk <= 3
ORDER BY call_day ASC, rnk ASC, endpoint ASC
```

> **Cost Analysis**
>
> api_calls has 300M rows. GROUP BY DATE(call_time), endpoint reduces to days * 150 endpoints, so the window over the aggregated set is cheap. The dominant cost is the scan and aggregate; an expression index on DATE(call_time) helps the aggregate avoid a full sort.

> **Interviewers Watch For**
>
> Did you use DENSE_RANK (not RANK or ROW_NUMBER) for the no-gaps tie semantics, partition by day, and wrap in a subquery to filter the rank? RANK leaves gaps and ROW_NUMBER breaks ties arbitrarily; both fail the prompt.

> **Common Pitfall**
>
> Trying to filter WHERE rnk <= 3 in the same SELECT that defines the window fails because window functions are computed after WHERE. The fix is the outer SELECT (or a CTE), not a HAVING clause.

---

## Common follow-up questions

- How would you handle a tie that produces 5 rows at rank 3? _(DENSE_RANK <= 3 returns all five; that is the intended behavior. If you needed exactly 3 rows per day, you'd ROW_NUMBER instead and accept the arbitrary tie-break.)_
- What if the requirement was top 3 endpoints per (day, region)? _(Add region to both the GROUP BY and the PARTITION BY. The window resets per (day, region) instead of per day.)_
- How do you prevent low-volume days from dominating the leaderboard? _(Add a HAVING COUNT(*) >= N inside the aggregate to drop endpoints below a volume floor before ranking.)_

## Related

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