The Extremes

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

City and County of San Francisco

Problem

Which three services in cloud_costs cost the most in total, and which three cost the least, when only charges count as spend and the negative-amount credit rows are left out of every total? All six share one executive slide, cheapest service first.

Only the biggest and the smallest reach the executive review.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

Worked solution and explanation

What this problem really is

This is two opposite orderings of one aggregate forced into a single six-row slot, dressed up as a finance slide. Anyone can sum spend per service. The trap is the cut: sort by total, keep six, and you get the six biggest services while the cheap end never shows up. The move is to measure each service's distance from the nearer end of the distribution, so the three biggest and three smallest tie for the closest positions and the cut falls cleanly between them and the middle.

Build it in order

Step 1: Keep only real spend

Filter amount > 0 before summing. The negative rows in cloud_costs are credits, and adding them shrinks a service's total, which can move it from the cheap end into the middle. Rows with a NULL amount drop out of the same filter.

Step 2: Total each service once

Group by svc_name and SUM(amount) into service_spend. Provider is noise here: the same service billed under 'aws' and 'AWS' has to land in one total, so never group or clean on provider.

Step 3: Position every service from both ends

Cross join service_spend to itself and count, per service, how many totals are at least as large (rank_high, 1 for the biggest) and how many are at most as large (rank_low, 1 for the cheapest). Both positions come from the same pass, so neither end needs the population size.

Step 4: Cut on the nearer end

LEAST(rank_high, rank_low) is how close a service sits to either edge. Sorting by it and keeping six takes positions 1 to 3 from each end; the fourth from either side scores 4 and falls off. Then re-sort the six by total_amount ascending for the slide.

The solution

WITH service_spend AS (
  SELECT svc_name, SUM(amount) AS total_amount
  FROM cloud_costs
  WHERE amount > 0
  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_spend a
  CROSS JOIN service_spend b
  GROUP BY a.svc_name, a.total_amount
),
extremes AS (
  SELECT svc_name, total_amount
  FROM ranked
  ORDER BY LEAST(rank_high, rank_low)
  LIMIT 6
)
SELECT svc_name, total_amount
FROM extremes
ORDER BY total_amount ASC

Sort and keep six

ORDER BY total_amount DESC LIMIT 6 keeps the six largest services. The three cheapest never enter the result, and the slide quietly becomes a top-six list.

Cut on the nearer end

ORDER BY LEAST(rank_high, rank_low) LIMIT 6 treats both edges as equally close, so the same LIMIT takes three from each end.

Common follow-up questions

• How would you tag each row as top or bottom so a BI tool can color it? (A label derived from whether `rank_high` or `rank_low` is the smaller position.)
• What changes if two services tie on `total_amount` right at the cut? (Inclusive counts push both tied services to the same position; decide which one the slide keeps.)
• How would you rewrite the positioning with `ROW_NUMBER()`? (Two window orderings over `service_spend` replace the self-join.)