The Weight of Between

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

Amazon

Problem

Our small-charge review keeps missing allocations that sit exactly on the edges of its 100 to 300 band. Pull every allocation in that band from cost_allocs, edges included, as one team-and-service label beside its amount and region, smallest amount first.

Between the floor and the ceiling, the ledger keeps its quiet accounts. Sort what rests there.

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

Strip off the review costume and this is a closed-interval filter feeding a string assembly: keep the rows whose amount lands in 100 to 300 with both edges in, then glue team_name and svc_name into one label. Everyone gets the label. The edge is what separates answers. Two of the seven allocations in the band sit exactly on it: 'legal - EC2' at 100.00 and 'marketing - EC2' at 300.00. Write amount > 100 AND amount < 300 and both rows vanish without an error. The review then shows five rows, which is the very symptom the prompt describes.

Break it down

Step 1: Keep the band, edges included

Filter with amount BETWEEN 100 AND 300. BETWEEN is closed on both ends, so the 100.00 and 300.00 allocations stay. amount >= 100 AND amount <= 300 is the same predicate. Only the strict form is wrong.

Step 2: Assemble one label column

Build team_name || ' - ' || svc_name AS label. The preview shows both names in one column with ' - ' between them. Two separate columns fail the shape check even when every value is right.

Step 3: Sort smallest charge first

ORDER BY amount sorts ascending, matching how the review reads the band. The seven amounts in range are all different, so one key fully determines the sequence.

SELECT
    team_name || ' - ' || svc_name AS label,
    amount,
    region
FROM cost_allocs
WHERE amount BETWEEN 100 AND 300
ORDER BY amount

Strict comparison

amount > 100 AND amount < 300 returns five rows. 'legal - EC2' and 'marketing - EC2' are gone, and nothing in the output says so.

Closed band

amount BETWEEN 100 AND 300 returns all seven, with the two edge allocations first and last.

Common follow-up questions

• What does the result lose if you write `amount > 100 AND amount < 300`? (Boundary awareness: names the two edge rows.)
• If two allocations shared an amount, how would you make the order deterministic? (Tie rules and a secondary sort key.)
• If `svc_name` could be `NULL`, what would `label` show, and how would you guard it? (`NULL` propagation through `||` and `COALESCE`.)