# The Extremes > Only the biggest and the smallest reach the executive review. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem For an executive review, pull the top 5 highest-spending and the top 5 lowest-spending cloud services by total amount in 2025, listed from lowest total to highest. Show each service name and its total amount. ## Worked solution and explanation ### Why this problem exists in real interviews This is two opposite rankings of the same aggregate forced into one report, dressed up as a finance ask. Anyone can sum spend per service. The skill being probed is how you rank WITHOUT a window function: for each service, count how many services spend at least as much and that count is its position from the top, then count how many spend at most as much and that is its position from the bottom. The trap is the low end. It is tempting to grab the five highest and then bolt on a second pass that has to know the total service count just to locate where the tail begins. Rank from both ends in the same pass and the bottom five fall out as cleanly as the top five, with no population count to track and no way for the two ends to silently overlap. --- ### Break down the requirements #### Step 1: Window the year correctly Constrain bill_date to the prior calendar year with a sargable half-open range: >= '2025-01-01' AND < '2026-01-01'. Wrapping bill_date in YEAR() reads the same rows but blocks partition pruning, so the planner scans all 48 monthly partitions instead of the year's twelve. #### Step 2: Aggregate per service once Collapse to one row per service with GROUP BY svc_name and SUM(amount). region, acct_id, and provider are noise for an exec view and drop out. Name this aggregate (service_year) so every later step reads the same 400-row result instead of re-scanning 18M rows. #### Step 3: Rank by counting, not by LIMIT Cross join service_year to itself and, per service, add up two conditional counts: how many rows have b.total_amount >= a.total_amount (its position from the top) and how many have b.total_amount <= a.total_amount (its position from the bottom). The biggest spender scores 1 from the top; the smallest scores 1 from the bottom. This is how you rank before window functions exist, and it parses everywhere because there is no ORDER BY or LIMIT buried inside a subquery. #### Step 4: Cut both ends in one filter With both positions in hand, the five highest are rank_high <= 5 and the five lowest are rank_low <= 5. A single WHERE with OR keeps exactly those ten services, so there is no second query arm to assemble and nothing that needs the population size. Finish with one ascending ORDER BY over the result. --- ### The solution **THE EXTREMES** ```sql WITH service_year AS ( SELECT svc_name, SUM(amount) AS total_amount FROM cloud_costs WHERE bill_date >= '2025-01-01' AND bill_date < '2026-01-01' GROUP BY svc_name ), ranked AS ( SELECT a.svc_name, a.total_amount, SUM(CASE WHEN b.total_amount >= a.total_amount THEN 1 ELSE 0 END) AS rank_high, SUM(CASE WHEN b.total_amount <= a.total_amount THEN 1 ELSE 0 END) AS rank_low FROM service_year a CROSS JOIN service_year b GROUP BY a.svc_name, a.total_amount ) SELECT svc_name, total_amount FROM ranked WHERE rank_high <= 5 OR rank_low <= 5 ORDER BY total_amount ASC LIMIT 10 ``` > **Cost Analysis** > > The service_year CTE expresses the heavy 18M-row aggregation once; the self-join then consumes its roughly 400-row output, so the expensive scan never repeats. The sargable bill_date range keeps that single scan cheap: a YEAR(bill_date) = ... rewrite would touch every monthly partition instead of the year's twelve. The 400 by 400 self-join is noise next to the base scan. > **Interviewers Watch For** > > Whether you can rank without reaching for LIMIT. Many engines reject ORDER BY or LIMIT placed directly inside a CTE or a UNION arm, which is exactly where a LIMIT-based answer wants to put them. Counting positions with a self-join sidesteps that entirely, and computing the bottom rank directly, rather than deriving it from the total service count, shows you understand the tail is just the mirror image of the head. > **Common Pitfall** > > The >= and <= joins treat ties as mutual: two services on the same total each count the other, inflating both positions and possibly pushing a row past the five-row cutoff on its end. With this seed every service total is distinct, so it stays clean. If ties matter to the business, switch to strict comparisons for a competition ranking, or keep the inclusive ones for a dense ranking, and decide which behavior each cutoff should follow. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you tag rows as top or bottom so a BI tool can color them? _(Probes adding a literal column derived from rank_high and rank_low.)_ - If cloud_costs is partitioned monthly on bill_date, does this prune the scan? _(Tests sargable ranges versus function-wrapped columns.)_ - How would you rewrite the ranking with ROW_NUMBER() if window functions were allowed? _(Moves from self-join counting to native window ranking.)_ > **Trick worth knowing** > > The reason this avoids the LIMIT-in-subquery trap is that all the ordering work lives in the conditional counts, not in ORDER BY. The only ORDER BY is the final one over the ten assembled rows, where it is always legal. If you ever DO need an inner top-N, name it as its own CTE so the sort sits in a CTE body rather than inside a UNION arm, which is where strict parsers reject it. ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_extremes) - [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.