# Poles Apart > Two extremes, one result: the busiest tokens and the quietest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We audit API tokens at both ends of the usage spectrum: the busiest and the quietest, counting only tokens that have been used at least once. Return those tokens with their request counts, busiest first. ## Worked solution and explanation ### What this problem really is This is a two-headed extreme-value query wearing a security-audit costume: pull BOTH the max and the min of requests in one result set, keeping every tied row at either end. Anyone can write a single subquery for one boundary. The trick is that the never-used filter (last_used IS NOT NULL) has to live inside the MIN and MAX subqueries, not just the outer WHERE. Miss that and the MIN collapses to a never-used token's stored 0, and your quiet extreme is suddenly full of tokens you were told to exclude. --- ### Break down the requirements #### Step 1: Define the candidate population Only tokens with last_used IS NOT NULL count. Apply that filter inside the MIN and MAX subqueries as well as the outer query, otherwise the MIN comes back as the smallest requests value among never-used tokens. #### Step 2: Match every row at the extremes, not just one Comparing requests to (SELECT MAX(requests) ...) and (SELECT MIN(requests) ...) returns all tied rows automatically. ORDER BY requests DESC + LIMIT 1 would silently drop ties, which is the most common wrong answer. #### Step 3: Project token_id and requests, sorted high to low The output is a flat two-column list ordered by requests DESC, so highest-traffic tokens come first and the lowest extreme appears at the bottom. --- ### The solution **Two scalar subqueries plus an OR for both extremes** ```sql SELECT token_id, requests FROM api_tokens WHERE last_used IS NOT NULL AND (requests = (SELECT MAX(requests) FROM api_tokens WHERE last_used IS NOT NULL) OR requests = (SELECT MIN(requests) FROM api_tokens WHERE last_used IS NOT NULL)) ORDER BY requests DESC ``` > **Cost Analysis** > > api_tokens has about 250,000 rows. Each MIN and MAX scalar subquery is one full scan, but SQLite can satisfy them with an index on requests if one exists. Without an index it is three scans total (MIN, MAX, outer). At this size the cost is negligible. Keep the last_used IS NOT NULL predicate in all three places so the planner does not pick a wrong extreme. > **Interviewers Watch For** > > They want to see that you preserve ties at both ends and that the NULL filter is applied to the subqueries, not just the outer query. A strong candidate also notes that this can be expressed with a single window function pass (MIN OVER (), MAX OVER ()) and discusses the trade-off. > **Common Pitfall** > > Forgetting the last_used IS NOT NULL filter inside the (SELECT MIN(requests) FROM api_tokens) subquery is the classic bug. If never-used tokens have requests = 0 stored, the MIN extreme becomes 0 and the result is dominated by tokens that should have been excluded. --- ## Common follow-up questions - How would you rewrite this with window functions instead of three subqueries? _(Tests fluency with MIN(requests) OVER () and MAX(requests) OVER () as a single-scan alternative, and the candidate should compare the cost on a 250k-row table.)_ - How would the query change if you also needed to return the owner_id for each extreme token? _(Trivial column addition, but the candidate should note the OR semantics still hold and there is no risk of duplicating rows.)_ - What if the spec changed to include the top 5 and bottom 5 instead of just the extremes? _(Forces a switch to ORDER BY + LIMIT or to two ranked CTEs unioned together, and the candidate should mention DENSE_RANK to handle ties at the boundary.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/poles_apart) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.