Map channels with an approved dictionary · SQL
Task: the rules
Input
tickets(id, channel) — text. id is unique. channel may be missing.
Rules
channel: remove ASCII whitespace (space, tab, CR, LF) at both ends, then lowercase; an empty result is missing.
Map channel with this approved dictionary (left → right) and nothing else:
email → email e-mail → email
phone → phone telephone → phone
chat → chat web chat → chat webchat → chat
Quarantine to exceptions with reason:
missing_channel channel is missing
unmapped_channel channel is present but not in the dictionary (never guess a mapping)
Outputs (row order does not matter)
clean(id text, channel text)
exceptions(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.
| # | idstring | channelstring |
|---|---|---|
| 1 | tx6ld | empty |
| 2 | t1bmf | Phone· (raw value "Phone ") |
| 3 | t6s3m | TELEPHONE |
| 4 | t7f5e | ·email (raw value " email") |
| 5 | tsvpj | Web Chat |
| 6 | ta4en | e mail |
| 7 | tacyt | WebChat· (raw value "WebChat ") |
| 8 | t95p3 | ·· (raw value " ") |
| 9 | t9ulv | chat |
| 10 | t9k3m | e-mail⇥ (raw value "e-mail\t") |
| 11 | tzj97 | missing |
| 12 | t22bj | TELEPHONE |
| 13 | tk313 | web-chat |
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.