Bottom 2% Services by Spend

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

British Airways

Problem

Zombie services cost almost nothing yet keep generating billing line items in cloud_costs, and the FinOps team is sweeping May 2026 for them. Split that month's services into four equally sized tiers by total net spend, credits included, and return the cheapest tier with each service name and its total spend, lowest first.

The bottom 2% of spenders. Who are they?

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 really is

This is an equal-size tiering of an aggregate dressed up as a FinOps cleanup. The skill being probed: can you collapse cloud_costs line items to one net total per svc_name for May, and only then cut those totals into four tiers by position? Anyone can write NTILE(4). The trick is running it over service totals, not billing rows. Get that wrong on this data and Cloud Run, which nets to 1,339.2 for the month, lands in the zombie tier because one of its lines is a -268.5 credit.

Build it in order

Step 1: Filter to May of the target year

strftime('%Y', bill_date) and strftime('%m', bill_date) pin both parts. If you pin only the month, prior Mays leak in: Lambda picks up a 946.49 line from an earlier year and drops out of the cheapest tier.

Step 2: Roll up to service grain

SUM(amount) per svc_name is the grain change. Negative amounts are credits and belong in the net: Blob Storage's only May line is a -224.8 credit, which is why it heads the list.

Step 3: Cut the totals into four equal tiers

NTILE(4) OVER (ORDER BY total_spend) deals the ten services into tiers of 3, 3, 2 and 2. When the count does not divide evenly, the earliest tiers take the extra rows, so tier 1 is the cheapest three.

Step 4: Keep tier 1, cheapest first

WHERE spend_tier = 1 sits outside the CTE that computes the tier, because a window value cannot be filtered in the same SELECT that defines it. Then ORDER BY total_spend.

The solution

WITH monthly_totals AS (
  SELECT svc_name, SUM(amount) AS total_spend
  FROM cloud_costs
  WHERE strftime('%Y', bill_date) = '2026'
    AND strftime('%m', bill_date) = '05'
  GROUP BY svc_name
),
service_tiers AS (
  SELECT svc_name,
         total_spend,
         NTILE(4) OVER (ORDER BY total_spend) AS spend_tier
  FROM monthly_totals
)
SELECT svc_name, total_spend
FROM service_tiers
WHERE spend_tier = 1
ORDER BY total_spend;

Tiering billing lines

NTILE(4) over the 14 May line items returns four rows: Cloud Run -268.5, Blob Storage -224.8, BigQuery 72.82, Cloud Run 90.65. Cloud Run appears twice, and BigQuery, which nets to 1,750.34, looks like a zombie.

Tiering service totals

NTILE(4) over the ten service totals returns Blob Storage -224.8, Lambda 233.29 and RDS 393.76: one row per service, each a real net total.

Follow-up questions

Common follow-up questions

• How would `PERCENT_RANK` change which services land in the cheapest tier? (Tests equal-size tiers versus relative position under ties.)
• What changes if a service bills only `amount = 0` lines in May? (Probes whether zero totals crowd tier 1.)
• Make this incremental: recompute tiers only when a service's May spend changes. (Pushes toward a materialized `monthly_totals` and a watermark on `bill_date`.)
• How would you rewrite the date filter so the partition prune fires? (Confirms you know a function on the partition column kills elimination.)