Audit Candidates Across Numeric Boundaries
Find the approved audit slice for candidates 102 through 109: compensation above $60,000 and capped at $75,000.
- Filtering
Exercise brief
Understand the request
Candidate audit analyst A reconciliation sample is limited by both candidate identifier and salary boundaries.
Return candidates 102–109 who fall inside the approved compensation band.
Return
- Return candidate_id, team_code, salary in this exact left-to-right order.
Constraints
- Include ID 102 but exclude ID 110; exclude $60,000 but include $75,000.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
cohort_candidates
candidate_idINTEGERteam_codeTEXTsalaryDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Translate “from” into an inclusive lower ID boundary, “through 109” into an exclusive upper boundary of 110, “above” into a strict comparison, and “at most” into an inclusive comparison.
Hint 2
Combine the two candidate_id comparisons and two salary comparisons with AND.
Hint 3
SELECT candidate_id, team_code, salary FROM cohort_candidates WHERE candidate_id /* inclusive lower */ 102 AND candidate_id /* exclusive upper */ 110 AND salary /* strict lower */ 60000 AND salary /* inclusive upper */ 75000;
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT candidate_id, team_code, salary FROM cohort_candidates WHERE candidate_id >= 102 AND candidate_id < 110 AND salary > 60000 AND salary <= 75000;Why this works
The ID window uses `>= 102` and `< 110`, while the salary band uses `> 60000` and `<= 75000`. The fixture includes rows exactly at 102, 110, 60000, and 75000, so changing any boundary operator changes the result. Row order is deliberately not graded because this exercise assesses numeric filtering only.
Success check
Only candidates inside both numeric windows are returned; row order is not assessed.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| candidate_id | team_code | salary |
|---|---|---|
| 102 | PLATFORM | 60001 |
| 103 | IT | 70000 |
| 104 | SALES | 70001 |
| 105 | IT | 72000 |
| 106 | HR | 75000 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.