# Where the Bill Settles > When a handful of giant charges skew the average, the middle tells the truth. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem We're reviewing spend across our US regions, where every cloud charge sits as one row in `cloud_costs` tagged with its service and the amount billed. A handful of enormous bills drag the average up, so for each service we track the median charge instead. Looking only at charges in the us-east-1, us-west-2, and us-central1 regions, return the five services with the highest median, largest first. ## Worked solution and explanation ### What this really is Strip the cloud-billing costume and this is 'find the middle value of each group' in an engine with no median function. The US-region filter and the five-service cut are the easy bookends; the median in the middle is what separates candidates. The tell: do you pick ONE middle row (wrong for even counts) or AVERAGE the two central values? Pick one middle row and every service with an even number of charges reports a median off by half the gap between its two central bills, and the highest-to-lowest ordering (and therefore which five make the cut) shifts with it. There is no PERCENTILE_CONT here, so the portable move is the rank-window pattern: ROW_NUMBER over the partition, COUNT over the partition, then average every row whose position sits in the middle band. The robust way to express that band is integer-only, 2 * rn between cnt and cnt + 2, which keeps one row for odd counts and the two central rows for even ones with no division rounding to trip over. --- ### Break down the requirements #### Step 1: Scope to the US regions, then tag every row with its rank and partition size Filter to region IN ('us-east-1', 'us-west-2', 'us-central1') FIRST, in a CTE, so only in-scope charges reach the windows. Do the filter after ranking and every partition is polluted by non-US charges and the medians are wrong. Inside the filtered set, ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY amount) gives a 1-indexed position within each service, and COUNT(*) OVER (PARTITION BY svc_name) attaches the partition total to every row, so the middle-band test has its group size without a self join. #### Step 2: Select the middle band with 2 * rn The condition 2 * rn >= cnt AND 2 * rn <= cnt + 2 is pure integer arithmetic, so it does not depend on whether the engine treats / as integer or float division. For odd cnt it isolates the single middle row; for even cnt it keeps both central rows. This is where the naive rn = (cnt+1)/2 test quietly breaks: on an engine that evaluates that division as a float, the x.5 position for even counts matches no integer rank and you silently keep only the upper-middle row. #### Step 3: Average the picked rows per service, then take the top five The WHERE keeps one or two rows per svc_name. AVG(amount) GROUP BY svc_name averages whatever it kept, giving the true median for either parity. ORDER BY median_amount DESC then svc_name ASC gives the highest-first ordering with the alphabetical tie break, and LIMIT 5 takes the five services with the largest medians. --- ### The solution **Single-pass median with a rank window** ```sql WITH us_charges AS ( SELECT svc_name, amount FROM cloud_costs WHERE region IN ('us-east-1', 'us-west-2', 'us-central1') ), ranked AS ( SELECT svc_name, amount, ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY amount) AS rn, COUNT(*) OVER (PARTITION BY svc_name) AS cnt FROM us_charges ) SELECT svc_name, AVG(amount) AS median_amount FROM ranked WHERE 2 * rn >= cnt AND 2 * rn <= cnt + 2 GROUP BY svc_name ORDER BY median_amount DESC, svc_name ASC LIMIT 5 ``` > **Cost Analysis** > > The region filter shrinks the input before the sort, then the window functions sort the surviving rows by (svc_name, amount), which is the dominant cost. After windowing, the WHERE keeps at most 2 rows per service, so the final aggregation operates on a couple of rows per service and LIMIT 5 trims to five. If cloud_costs were partitioned on svc_name, the sort would be partition-local and the plan would parallelize naturally. > **Interviewers Watch For** > > Whether the candidate averages the two central values for even counts instead of grabbing one row, and whether their middle-row test survives an engine that does float division. The robust move avoids division entirely: keep rows where 2 * rn lands between cnt and cnt + 2. Strong candidates also apply the region filter before ranking and reason through the odd and even parity cases out loud before writing the predicate. > **Common Pitfall** > > Reaching for PERCENTILE_CONT(0.5) WITHIN GROUP reads cleanly but is not portable to engines without it. Worse, a hand-rolled rn = (cnt+1)/2 middle test silently drops to the upper-middle row for even counts on any engine that evaluates that division as a float, so every even-count service comes back half a gap too high, which can bump the wrong service into the top five. The integer band 2 * rn between cnt and cnt + 2 sidesteps both. --- ## Common follow-up questions - Walk through why 2 * rn between cnt and cnt + 2 selects one middle row for odd counts and both middle rows for even counts. _(Tests algorithm understanding. cnt=5 keeps rn=3 only; cnt=4 keeps rn=2 and rn=3. Because the band is integer-only there is no rounding to reason about, which is exactly why it is safe across engines.)_ - How would you compute the 90th percentile instead? _(Tests pattern generalization. Keep the same windowing and target the position near 0.9 * cnt rather than the center; for a single interpolated value you scale the rank offset instead of picking a symmetric middle band.)_ - Why not use NTILE(2) and pick the boundary? _(Tests awareness of NTILE semantics. NTILE distributes rows into N buckets but does not guarantee the boundary row is the median; for small partitions the boundary diverges from the true middle value.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_bill_settles) - [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.