Who's Holding Up Traffic

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Workday

Problem

Find the average number of unique users hitting each endpoint on a given day of June 2026, with endpoints listed alphabetically so the result works as a lookup reference. Endpoint paths come straight from the gateway log, where a path ending in a slash is routed separately from the same path without one, so the two stay separate endpoints.

Some endpoints carry the product. Others are barely touched.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a per-day aggregation wearing an API-metrics costume. The trap: group by endpoint in one pass and you average over the wrong denominator. You have to count the unique users within each calendar day first, then average those daily counts per endpoint. Skip the inner day bucket and a user who hits /search on ten days of June collapses into a single distinct value, dragging the answer down toward unique-visitors-over-active-days instead of true daily traffic.

Break down the requirements

Step 1: Filter to June of the target year

strftime('%Y-%m', call_time) = '2026-06' keeps exactly June of the target year. It reads cleanly, but wrapping the column in a function hides it from partition pruning; on the production table you would write the half-open range call_time >= '2026-06-01' AND call_time < '2026-07-01' instead.

Step 2: Aggregate per (endpoint, day)

Inner query computes COUNT(DISTINCT user_id) grouped by endpoint and DATE(call_time). That gives one row per endpoint per active day, the daily unique-user count.

Step 3: Average across days

Outer query takes AVG(daily_users) grouped by endpoint. Days where the endpoint had zero calls are absent, so the denominator is days-active, not a fixed 30.

The solution

SELECT endpoint,
       AVG(daily_users) AS avg_daily_active_users
FROM (
  SELECT endpoint,
         DATE(call_time) AS call_day,
         COUNT(DISTINCT user_id) AS daily_users
  FROM api_calls
  WHERE strftime('%Y-%m', call_time) = '2026-06'
  GROUP BY endpoint, DATE(call_time)
) sub
GROUP BY endpoint
ORDER BY endpoint;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you change this if the team wanted the average over all 30 calendar days, including days with zero traffic? (Cross join endpoints to a generated date series, left join the per-day counts, and `COALESCE` missing days to zero before averaging.)
• What if `user_id` can be null for unauthenticated traffic? (`COUNT(DISTINCT user_id)` already ignores NULLs, but the team may want anonymous sessions bucketed by IP or `session_id` as a separate identity column.)
• How would you rank the endpoints by week-over-week change in this metric? (Compute the daily-uniques aggregation per ISO week, then `LAG` the average per endpoint ordered by week to get the delta.)