Monthly Active Users per Endpoint
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- Maanams International
Problem
The api_calls log holds one row per request, recording the endpoint it hit, when it happened, and the user who made it. How many unique users called each endpoint during June 2026?
One endpoint, many users. Which ones showed up?
Tables
api_calls
Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT
| call_id | endpoint | method | status | latency | user_id | call_time | err_msg |
|---|---|---|---|---|---|---|---|
| 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL |
| 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL |
| 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL |
| 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL |
| 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL |
| 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found |
| 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized |
| 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded |
| 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error |
| 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway |
Expected output
| endpoint | user_count |
|---|---|
| /api/v1/auth/login | 2 |
| /api/v1/auth/logout | 0 |
| /api/v1/orders | 2 |
| /api/v1/users/ | 1 |
| /api/v2/products | 3 |
Worked solution and explanation
This is reach versus volume dressed up as an endpoint report. Anyone can write a per-endpoint count; what separates answers is counting each person once. June 2026 contains a retry storm: one user hit '/api/v1/auth/login' four times in six minutes. Count rows and login reports 5 callers when it reached 2. Meanwhile '/api/v1/auth/logout' reports 1 when its only June call carries no user_id at all.
Walk it
Step 1: Keep only June 2026 calls
call_time is TEXT shaped like '2026-06-15 09:00:00', so strftime('%Y-%m', call_time) trims it to the month and the comparison with '2026-06' keeps June. Skip the filter and the February traffic block plus three years of backfill pour into every count.
Step 2: One row per endpoint
GROUP BY endpoint over the June rows. An endpoint only appears if it saw June traffic, which is exactly the list being asked for: ten endpoints.
Step 3: Count people, not calls
COUNT(DISTINCT user_id) collapses the retries into one person and ignores NULL ids. That is why logout lands at 0: its group exists because a call happened, but there is nobody to count.
SELECT endpoint, COUNT(DISTINCT user_id) AS user_count FROM api_calls WHERE strftime('%Y-%m', call_time) = '2026-06' GROUP BY endpointOne filter, one group key, one distinct count.
- Counting rows
COUNT(*)gives login 5 (four storm retries plus one other caller) and logout 1 (an anonymous call). That is call volume, not reach.- Counting users
COUNT(DISTINCT user_id)gives login 2 and logout 0. Each person counts once per endpoint and anonymous calls count for no one.
Common follow-up questions
- Product also wants endpoints with no June calls listed at 0. What changes? (Grouping only emits groups that have rows; this needs a list of all endpoints and an outer join.)
- Would you keep `strftime('%Y-%m', call_time)` on a table partitioned by `call_time`? (Partition pruning and range predicates on the raw column.)
- How do you report reach for the whole quarter: add the three monthly numbers? (Distinct counts do not add; one person active in all three months would be counted three times.)
- '/api/v1/users' and '/api/v1/users/' both appear. One endpoint or two? (Normalizing a grouping key is a product decision, not a default.)