Response Buckets

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

L5

Problem

An on-call engineer found the latency report counting calls that never recorded a latency as slow, and those calls should not appear in it at all. From api_calls, label each remaining call 'fast' under 100, 'normal' from 100 through 500 and 'slow' above 500, returning its endpoint, latency and that label.

Fast, normal, or slow. Every API call gets a verdict.

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

This is a three-way split of a continuous measure dressed up as a dashboard fix. Anyone can write three labels. What separates candidates is where the edges land and what happens to the rows that have no number at all. A CASE reads top to bottom and stops at the first true branch, so once latency < 100 has claimed the fast calls, the middle branch only needs latency <= 500. The trap is the ELSE: a call whose latency is NULL fails every comparison and drops straight into 'slow', inflating exactly the tier the report is about.

Building it

Step 1: Drop calls that never recorded a latency

Six rows in api_calls have latency set to NULL. Any comparison against NULL is unknown, so no WHEN fires and the ELSE would call them 'slow'. Filter them with WHERE latency IS NOT NULL before the label is computed: 670 rows become 664.

Step 2: Let branch order supply the lower bound

Write fast first with latency < 100. Anything that reaches the second branch is already at least 100, so latency <= 500 alone defines normal, and both 100 and 500 sit inside it. Whatever is left is above 500 and takes the ELSE.

Step 3: Name the label column

AS latency_bucket gives the output a header a reader can use. Without it the column is titled with the whole expression, which nobody downstream can reference.

SELECT
  endpoint,
  latency,
  CASE
    WHEN latency < 100 THEN 'fast'
    WHEN latency <= 500 THEN 'normal'
    ELSE 'slow'
  END AS latency_bucket
FROM api_calls
WHERE latency IS NOT NULL

Self-contained ranges

WHEN latency >= 100 AND latency <= 500 restates a lower bound the first branch already guarantees, and repeated edges are where off-by-one slips hide: type latency > 100 there and a call at exactly 100 falls through to 'slow'.

Ordered branches

WHEN latency < 100 then WHEN latency <= 500 states each edge exactly once. Every value from 100 through 500 is normal, and a boundary can only be wrong if one operator in one place is wrong.

Common follow-up questions

• How would you return how many calls land in each tier instead of one row per call? (Moves from a per-row label to a tally per `latency_bucket`.)
• Two `endpoint` values in `api_calls` differ only by a stray space. Does that matter here, and when would it start to? (Projecting raw values is harmless; counting per `endpoint` would split one endpoint in two unless it is trimmed.)
• What if the report should keep calls with no latency and label them 'unknown'? (Replaces the filter with a first branch on `latency IS NULL`.)