Cost Efficiency Ratio

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Forbes

Problem

Our chargeback sheet quotes each cloud service's average charge per billing line in whole dollars, and the figures we recompute by hand from cloud_costs keep landing a dollar high on some services. Rebuild that number for every service, counting only the lines that actually charged something.

Dollars in, value out. What's the ratio?

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 a per-service average dressed up as a chargeback reconciliation, and the whole problem is in the symptom. Anyone can write SUM(amount) / COUNT(*) per svc_name. What separates candidates is reading 'a dollar high' correctly: the sheet drops the cents, so the hand checks were rounding. Round and RDS reads 840 where the sheet says 839, and EC2 reads 944 instead of 943. Forget which lines count and it gets worse: credits and lines with no amount drag EC2 all the way down to 660.

Build it in three decisions

Step 1: Keep only lines that charged something

WHERE amount > 0 removes credits (negative amount) and lines where amount is NULL in one predicate, because a comparison with NULL is never true. This matters because COUNT(*) counts every row it is handed: EC2 has three lines with no amount, and leaving them in divides its spend by 10 lines instead of 7.

Step 2: Average per service

Group by svc_name and divide SUM(amount) by COUNT(*). Once the filter has run, COUNT(*) and COUNT(amount) agree, so this is the same number as an average of amount; writing the ratio keeps the denominator, 'billing lines', visible to whoever audits it.

Step 3: Drop the cents

CAST(... AS INTEGER) truncates toward zero. Every average here is positive, so truncation is the floor, which is what the sheet does: 839.51 becomes 839.

SELECT
    svc_name,
    CAST(SUM(amount) / COUNT(*) AS INTEGER) AS avg_charge_dollars
FROM cloud_costs
WHERE amount > 0
GROUP BY svc_name

Rounded (the hand check)

RDS 839.51 becomes 840, Cloud Run 803.85 becomes 804, EC2 943.94 becomes 944. Six of ten services land a dollar high, and only the ones whose cents are 50 or more.

Cents dropped (the sheet)

CAST(... AS INTEGER) gives 839, 803 and 943. Services with small cents, like CloudFront at 996.02, agree either way, which is why the mismatch looked random.

Common follow-up questions

• Finance now wants credits netted against spend but still divided only by charged lines. What changes? (Moves the credit rows from the filter into the numerator: a conditional sum, not a WHERE.)
• How would you show the dropped cents so the sheet can be audited? (Return the raw ratio next to the truncated one and reason about which one feeds downstream totals.)
• What if `amount` were stored as integer cents instead? (Now `SUM(amount_cents) / COUNT(*)` is integer division, and the truncation happens whether you wanted it or not.)