Cloud Bill

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

Problem

The cost_allocs table holds one cloud-spend line item per row, tagged with a spending category, and allocations with no account ID are left out entirely. Report each category's total dollar amount, number of line items and average cost per line item, from the most expensive category to the least.

Which cost buckets are bleeding money?

Tables

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

Worked solution and explanation

What this really is

This is a row-level exclusion dressed up as a budget report. Anyone can write SUM, COUNT and AVG per category. What separates candidates is where the missing-account rule lives: it has to drop the whole allocation in WHERE acct_id IS NOT NULL, before grouping. The shortcut is to lean on NULL-skipping, write COUNT(acct_id) for the line count, and trust the rest to follow. It does not. SUM(amount) and AVG(amount) never look at acct_id, so the 21 unattributed allocations still pour their dollars into every total, the average stops matching total over count, and the most-expensive-first order can shift.

Step 1: Drop unattributed allocations before anything is totaled

WHERE acct_id IS NOT NULL throws out the entire row, so the 21 allocations with no account never reach SUM, COUNT or AVG. Swapping COUNT(*) for COUNT(acct_id) looks like the same fix, but it repairs one of the three columns and leaves the other two wrong.

Step 2: Total, count and average per category

Every row is its own line item, so COUNT(*) is the count. Nothing repeats, and COUNT(DISTINCT ...) would answer a different question. AVG(amount) divides the same filtered sum by the same filtered count, so the three figures stay consistent with each other.

Step 3: Sort by the dollar total, biggest first

ORDER BY total_amount DESC sorts on the aggregate, not on the category name. Order is graded here, so an alphabetical list with the right numbers still fails.

SELECT
    category,
    SUM(amount) AS total_amount,
    COUNT(*) AS line_items,
    AVG(amount) AS avg_cost_per_item
FROM cost_allocs
WHERE acct_id IS NOT NULL
GROUP BY category
ORDER BY total_amount DESC

Per-column NULL skipping

COUNT(acct_id) drops the unattributed rows from the line count, but SUM(amount) and AVG(amount) still include their dollars. The total overstates spend, and the average no longer equals total divided by count.

Row-level filter

WHERE acct_id IS NOT NULL removes each unattributed row once, before grouping, so all three metrics describe the same set of allocations.

Common follow-up questions

• What if you also had to report how many allocations the missing-account rule excluded? (Tests conditional aggregation: count rows where `acct_id IS NULL` alongside the totals, which means moving the exclusion out of `WHERE` and into the aggregates.)
• How would you add each category's share of total spend? (Tests a grand-total denominator, such as `SUM(total_amount) OVER ()` over the grouped rows.)
• What if missing accounts arrive as an empty string instead of `NULL`? (`'' IS NOT NULL` is true, so the filter also needs `acct_id <> ''`, or `NULLIF(acct_id, '')` before the null test.)