Most Efficient Region by Token Usage

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

Meta

Problem

Each row of api_tokens is one access token: its scope, the text dates it was issued and expires, and the requests it has served, where a missing expiration marks a token that never expires and has no lifetime to measure. Per scope, over the tokens that do expire, report the average lifetime in days, the total requests in thousands, and requests per day as average requests over average lifetime, highest requests per day first.

Some regions squeeze more out of every token.

Tables

api_tokens

Columns: token_id INTEGER, owner_id INTEGER, scope TEXT, status TEXT, issued TEXT, expires TEXT, last_used TEXT, requests INTEGER

Expected output

Worked solution and explanation

This is a ratio of two group averages dressed up as token efficiency, with two quieter traps along for the ride. Anyone can write AVG(requests) per scope. What separates candidates is dividing the two averages instead of averaging each token's own requests per day, and remembering that SUM(requests) / 1000 on an integer column throws away everything after the decimal point. Get the ratio wrong and requests per day halves on this data, because half the expiring tokens expire the day they are issued. Get the division wrong and every total comes back as a whole number of thousands.

The decisions that move the numbers

Step 1: Drop the never-expiring tokens first

A NULL in expires means the token never expires, so it has no lifetime. Filter it in WHERE expires IS NOT NULL so it leaves the request total and the request average too. AVG would skip the null lifetime on its own but not the token's requests, and the ratio would then divide averages taken over two different sets of tokens.

Step 2: Turn text dates into days

issued and expires are text. JULIANDAY(expires) - JULIANDAY(issued) turns each pair into a day count. Subtract the strings directly and SQLite reads only the leading year, so a two-year token comes out as 2; Postgres refuses outright.

Step 3: Divide the averages, not each token

Requests per day is AVG(requests) / AVG(lifetime) at the scope level. AVG(requests / lifetime) is a different statistic, and on this seed it meets tokens whose lifetime is 0 days. SQLite turns those divisions into NULL and quietly drops them; Postgres raises an error.

Step 4: Keep the thousands fractional

SUM(requests) over an INTEGER column is an integer, and an integer divided by 1000 is integer division in SQLite and Postgres. CAST(SUM(requests) AS REAL), or dividing by 1000.0, keeps 54.238 from becoming 54.

The solution

SELECT
  scope,
  AVG(JULIANDAY(expires) - JULIANDAY(issued)) AS avg_lifetime_days,
  CAST(SUM(requests) AS REAL) / 1000 AS total_requests_k,
  AVG(requests) / AVG(JULIANDAY(expires) - JULIANDAY(issued)) AS requests_per_day
FROM api_tokens
WHERE expires IS NOT NULL
GROUP BY scope
ORDER BY requests_per_day DESC;

Average of each token's ratio

AVG(requests / (JULIANDAY(expires) - JULIANDAY(issued))) weights every token equally and divides by zero on the same-day tokens. On this seed it reports about 3.38 for 'full_access'.

Ratio of the scope's averages

AVG(requests) / AVG(JULIANDAY(expires) - JULIANDAY(issued)) divides volume by lifetime with every token counted once. It reports about 6.75 for 'full_access'.

Common follow-up questions

Common follow-up questions

• How would you count never-expiring tokens at a 10-year cap instead of dropping them? (Probes `COALESCE` on `expires` and whether you flag that the answer is now a policy choice.)
• Only report scopes with at least 20 expiring tokens. (Tests a `HAVING` on a count versus filtering rows in `WHERE`.)
• What changes if `requests` can be `NULL`? (`AVG` skips the `NULL`, while a `SUM` divided by a row count does not, so the two spellings stop agreeing.)
• Leave out tokens that expired the day they were issued. (Tests moving a zero-lifetime filter into `WHERE`, which takes the lifetime average to 730.)