Distinguish NULL Ownership from Named Teams
Use searched CASE to label NULL owner_team as Unassigned, platform as Platform-Owned, and all other values as Assigned.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Operations governance analyst Ownership reporting must separate unassigned work from platform-owned work and all other assigned teams.
Ownership reporting must separate unassigned work from platform-owned work and all other assigned teams. Use searched CASE to label NULL owner_team as Unassigned, platform as Platform-Owned, and all other values as Assigned.
Return
- Return event_id, owner_team, and ownership_state.
- Order by event_id.
Constraints
- Detect missing ownership with IS NULL.
- Do not attempt to match NULL with simple CASE equality.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_events
event_idINTEGERpipeline_nameTEXTstatus_codeTEXTseverityTEXTactual_msINTEGERtarget_msINTEGERretry_countINTEGERprocessed_rowsINTEGERfailed_rowsINTEGERowner_teamTEXTis_customer_facingINTEGERsource_systemTEXTmaintenance_modeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
NULL is a missing marker, not a value that equals another NULL.
Hint 2
Use WHEN owner_team IS NULL before testing named teams.
Hint 3
Let ELSE cover every other non-NULL owner.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, owner_team, CASE WHEN owner_team IS NULL THEN 'Unassigned' WHEN owner_team = 'platform' THEN 'Platform-Owned' ELSE 'Assigned' END AS ownership_state FROM pipeline_events ORDER BY event_id;Why this works
A searched CASE can express IS NULL directly. This is the portable way to distinguish missing values because simple CASE uses equality and NULL equals NULL is unknown.
Success check
Every NULL owner is Unassigned and no named team is mislabeled as missing.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | owner_team | ownership_state |
|---|---|---|
| 101 | core | Assigned |
| 102 | core | Assigned |
| 103 | core | Assigned |
| 104 | core | Assigned |
| 208 | NULL | Unassigned |
| 209 | NULL | Unassigned |
| 210 | data | Assigned |
| 303 | finance | Assigned |
| 304 | finance | Assigned |
| 305 | finance | Assigned |
Previewing 10 of 14 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
Basic SQL Functions
Practice production-oriented string cleanup, numeric transformations, NULL handling, tolerant conversion, and delimiter parsing.
Database Changes
Practice safe SQL data changes, schema evolution, retry-safe upserts, transactions, rollback, views, and indexes in isolated databases.
Open the interactive workspace and practice across SQL topics.