# The Weight of Between > Between the floor and the ceiling, the ledger keeps its quiet accounts. Sort what rests there. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The FinOps team is auditing mid-tier cost allocations, where mid-tier means an amount between 500 and 1,000 inclusive. Show each one as a single team-and-service label next to its amount and region, from the smallest amount up. ## Worked solution and explanation ### What this really is Strip off the FinOps costume and this is a contiguous range filter feeding a string assembly: keep the rows whose amount sits inside a closed interval, then glue two text columns into one label. There is no grouping and no join anywhere in it. The whole problem lives in two small decisions that fail silently: whether your range keeps its endpoints, and whether you leave the text alone. Reach for amount > 500 AND amount < 1000 and you quietly drop the 500 and 1000 rows; lowercase the team name to 'tidy it up' and you rewrite data the auditors expect verbatim. Neither mistake throws an error, so the output just comes back wrong. --- ### Break it down #### Step 1: Filter to the target rows Keep only mid-tier rows with WHERE amount BETWEEN 500 AND 1000. BETWEEN is inclusive on both ends, so the boundary rows at exactly 500 and 1000 stay in. This is the decision that separates a correct answer from a near-miss. #### Step 2: Build the team-service label Build the label by concatenating team_name and svc_name with a ' - ' separator (team_name || ' - ' || svc_name). Use the column values as stored: 'data-eng' and 'DATA-ENG' are different labels here, and normalizing casing would corrupt the audit output. #### Step 3: Order the final output Sort the result by amount ascending so the auditors read from the smallest spend up to the largest, matching the sample rows. --- ### The solution **Concatenation label with BETWEEN filter** ```sql SELECT team_name || ' - ' || svc_name AS label, amount, region FROM cost_allocs WHERE amount BETWEEN 500 AND 1000 ORDER BY amount ``` > **Interviewers Watch For** > > The tell is BETWEEN. A strong candidate says out loud that BETWEEN is inclusive on both ends, so the 500 and 1000 rows survive. Reaching for amount > 500 AND amount < 1000 is the reflex the interviewer is waiting to see you avoid, because it silently discards the boundary allocations. > **Common Pitfall** > > Normalizing the casing. 'data-eng' and 'DATA-ENG' look like the same team, but the label must preserve the value exactly as stored. Wrapping team_name in LOWER() or UPPER() changes the output and fails the check, even though the query still runs clean. > **Cost Analysis** > > On 10M rows with no index this is a full scan. Because the filter is one contiguous range on amount, a B-tree index on amount lets the engine seek to 500 and walk to 1000, reading only the matching slice instead of every row. --- ## Common follow-up questions - How would the result change if you wrote amount > 500 AND amount < 1000 instead of using BETWEEN? _(Tests boundary awareness: exclusive comparisons drop the endpoint rows.)_ - If two allocations share the same amount, how would you make the order deterministic, say by region alphabetically? _(Tests tie-breaking and multi-key ordering.)_ - If team_name could be NULL, what would the concatenated label look like, and how would you guard against it? _(Tests NULL propagation through string concatenation.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_weight_of_between) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.