Keep identifiers as text and parse yes/no flags · SQL
Task: the rules
Input
members(member_id, postcode, opted_in) — text. member_id is unique and always present.
Rules
member_id and postcode are identifiers: keep them as text exactly as given, except that postcode has
ASCII whitespace removed at both ends. Never convert them to numbers (00123 must stay 00123).
opted_in: remove ASCII whitespace at both ends, then lowercase, then:
y, yes, true, 1 → true
n, no, false, 0 → false
Quarantine with reason bad_flag when opted_in is missing or anything else (a missing answer is not "no").
Outputs (row order does not matter)
clean(member_id text, postcode text, opted_in bool)
exceptions(member_id text, reason text)Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
| # | member_idstring | postcodestring | opted_instring |
|---|---|---|---|
| 1 | 008417 | 98506· (raw value "98506 ") | true |
| 2 | 000002 | ·02139· (raw value " 02139 ") | maybe |
| 3 | 002939 | missing | 0 |
| 4 | 015246 | ·51389 (raw value " 51389") | y |
| 5 | 077927 | ·08222 (raw value " 08222") | N |
| 6 | 092803 | 30041 | true |
| 7 | 085051 | ⇥12134· (raw value "\t12134 ") | N |
| 8 | 000001 | 00501 | yes |
| 9 | 004197 | ·52402 (raw value " 52402") | true |
| 10 | 016386 | ⇥46532· (raw value "\t46532 ") | no |
| 11 | 016648 | ·20460 (raw value " 20460") | yes |
| 12 | 078978 | missing | no |
| 13 | 062844 | 82606· (raw value "82606 ") | y |
| 14 | 040367 | ⇥71788· (raw value "\t71788 ") | No· (raw value "No ") |
| 15 | 054272 | 78568· (raw value "78568 ") | YES |
| 16 | 022910 | ⇥11785 (raw value "\t11785") | YES |
| 17 | 076971 | 60216· (raw value "60216 ") | missing |
Run preview · in your browser, not a grade
Loading the preview runtime…
Check answer · graded on an isolated server, including hidden datasets
Your code runs in a locked-down machine with no network, on the visible data and on hidden datasets that follow the same rules.
Learn from this exercise
A worked solution becomes available after your first check or once you've used every hint.