Build a Production Review Cohort
Find eligible candidates who joined during 2023–2024 and have a usable contact status.
- String functions
- Filtering
- Sorting
Exercise brief
Understand the request
Production access reviewer A review cohort combines general team membership, a salary-qualified Security exception, a time window, and contact readiness.
Return eligible candidates who joined during 2023–2024 and have a usable contact status.
Return
- Return candidate_id, team_code, salary, joined_at in this exact left-to-right order.
Constraints
- Allow DATA or PLATFORM, or SECURITY at $75,000 and above; then apply the half-open time window and reject NULL or blank status text.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
cohort_candidates
candidate_idINTEGERteam_codeVARCHAR(20)salaryDECIMAL(10,2)joined_atDATETIMEcontact_statusVARCHAR(20)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
First group the general team membership rule with the salary-qualified Security exception.
Hint 2
Apply the 2023–2024 half-open time window and both usable-contact checks to the entire eligibility group.
Hint 3
SELECT candidate_id, team_code, salary, joined_at FROM cohort_candidates WHERE (team_code IN (/* general teams */) OR (team_code = /* exception team */ AND salary /* boundary */ 75000)) AND joined_at /* lower operator */ '2023-01-01 00:00:00' AND joined_at /* upper operator */ '2025-01-01 00:00:00' AND contact_status /* NULL predicate */ AND TRIM(contact_status) /* blank comparison */ '' ORDER BY candidate_id;
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, joined_at FROM cohort_candidates WHERE (team_code IN ('DATA', 'PLATFORM') OR (team_code = 'SECURITY' AND salary >= 75000)) AND joined_at >= '2023-01-01 00:00:00' AND joined_at < '2025-01-01 00:00:00' AND contact_status IS NOT NULL AND TRIM(contact_status) <> '' ORDER BY candidate_id;Why this works
This capstone combines membership, a conditional salary exception, a half-open two-year timestamp window, NULL handling, blank-text handling, and explicit precedence. The fixture includes Security candidates on both sides of the salary boundary, rows just before and exactly after the time window, and otherwise eligible candidates with NULL or whitespace contact status. Missing parentheses or weakening any boundary changes the result. The predicates are portable across the executable engines, although timestamp literal and text-trimming details may differ in native production schemas.
Success check
Every returned candidate satisfies the grouped eligibility rule, time window, and contact-quality checks.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| candidate_id | team_code | salary | joined_at |
|---|---|---|---|
| 101 | DATA | 60000 | 2023-01-01 00:00:00 |
| 102 | PLATFORM | 60001 | 2023-06-15 09:00:00 |
| 110 | SECURITY | 78000 | 2024-08-01 08:00:00 |
| 112 | PLATFORM | 85000 | 2024-12-31 23:59:59 |
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.