Label unknown categories, keep missing numbers missing · SQL
Task: the rules
Input visits(id, region, duration_min) — text. id is unique. Rules region: remove ASCII whitespace at both ends; an empty result is missing; then a missing region becomes the label "unknown" (the report groups by region and needs a bucket for it). duration_min: remove ASCII whitespace at both ends; an empty result is missing; convert to a whole number. A missing duration stays missing. Never fill it with 0 or any other number. Every duration that is present is a valid whole number. Output (row order does not matter) clean(id text, region text, duration_min int64)
Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
| # | idstring | regionstring | duration_minstring |
|---|---|---|---|
| 1 | vplbs | East | ·80· (raw value " 80 ") |
| 2 | vivo8 | ⇥East (raw value "\tEast") | 108 |
| 3 | v08ub | empty | missing |
| 4 | vtj0d | South | 0 |
| 5 | v59m5 | ·North (raw value " North") | ·66· (raw value " 66 ") |
| 6 | vo7m1 | missing | empty |
| 7 | v67u0 | empty | 0 |
| 8 | vpbs2 | West | ·28· (raw value " 28 ") |
| 9 | vep98 | West | 0 |
| 10 | vfx87 | ·North (raw value " North") | ·66· (raw value " 66 ") |
| 11 | vgnmc | ·North (raw value " North") | missing |
| 12 | vmoeo | empty | 83 |
| 13 | vglcg | South· (raw value "South ") | 104 |
| 14 | vmus1 | · (raw value " ") | missing |
| 15 | vwgco | ⇥East (raw value "\tEast") | empty |
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.