API Call Distribution Fraction

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

Lyft

Problem

Our traffic breakdown shows 'get' and 'GET' as separate methods, splitting what is really one verb across two slices. From api_calls, give each HTTP method and response status pairing its share of all calls as a fraction between 0 and 1, biggest slices first.

Not all endpoints are created equal.

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 part-of-whole problem presented as a dashboard. Almost everyone writes the count per pairing. What separates candidates is keeping the grand total available after GROUP BY has collapsed the rows: once 670 calls fold into one row per pairing, no row knows the total any more. Two quieter traps sit beside it. If you divide two whole counts, every share truncates to 0. If you group on the raw method, 'get' gets its own slice next to 'GET', and each holds part of what is really one verb.

Building it

Step 1: Clean the method key before grouping

The log stores 'get' and 'post' alongside 'GET' and 'POST'. HTTP verbs are case-insensitive, so UPPER(method) in a normalized_calls CTE folds them together before any group forms. If you uppercase in the final SELECT instead, the groups were already split, and you get two 'GET' rows.

Step 2: Count each pairing

GROUP BY method, status over the cleaned rows gives COUNT(*) per pairing. That is the numerator, and it is the easy half.

Step 3: Bring the grand total back with a window

SUM(COUNT(*)) OVER () is evaluated after grouping. It sums the per-pairing counts across the whole grouped result, so every row carries 670 without a second pass over api_calls. The empty OVER () makes one frame that spans every group.

Step 4: Keep the division fractional

COUNT(*) and the windowed sum are both integers, and 484 divided by 670 in integer arithmetic is 0. Casting the numerator with CAST(COUNT(*) AS REAL) makes the whole ratio real, so 'GET' with 200 comes out at about 0.722.

WITH normalized_calls AS (
    SELECT UPPER(method) AS method, status
    FROM api_calls
)
SELECT
    method,
    status,
    CAST(COUNT(*) AS REAL) / SUM(COUNT(*)) OVER () AS fraction
FROM normalized_calls
GROUP BY method, status
ORDER BY fraction DESC

Scalar subquery total

COUNT(*) * 1.0 / (SELECT COUNT(*) FROM api_calls) is also correct, and the subquery runs once. But it reads the base table a second time, and any filter later added to the outer query has to be copied into it, or the shares stop summing to 1.

Window over grouped counts

SUM(COUNT(*)) OVER () sums exactly the groups the query produced, so a filter added to normalized_calls moves the numerator and the denominator together. One scan, and one place to change.

Common follow-up questions

• The dashboard wants percentages rounded to one decimal place. Where do you round, and how do you keep the column summing to 100? (Tests rounding after the division and awareness of rounding drift.)
• Add each pairing's share within its own method as a second column. What changes in the window? (Tests partitioning the same windowed sum by `method`.)
• `endpoint` also has whitespace-padded values. If the breakdown moved to endpoint and status, what would you clean? (Tests spotting the same dirty-key trap on another column.)
• How would you produce these shares per day over a year of logs without scanning the table once per day? (Tests adding the day to the grouping key and partitioning the window by it.)