Exercise · beginner · 15 min
Join orders to customers without matching blanks
Look up each order's customer segment. A missing key must never match the customer row whose key is also missing.
The rules
These rules decide what a correct result is. Your answer is checked on this dataset and on hidden datasets that follow the same rules.
Inputs orders(id, customer_key) — text. id is unique. customers(customer_key, segment) — text. Present keys are trimmed and unique. One row has a missing key. Rules customer_key: remove ASCII whitespace (space, tab, CR, LF) at both ends; an empty result is missing. Add segment from customers by customer_key. Keep every order. An order whose key is missing, or has no matching customer, gets a missing segment. A missing key never matches a missing key. Output (row order does not matter) clean(id text, customer_key text, segment text)
The data
orders · 9 rows
| id | customer_key |
|---|---|
| oz2w7 | missing |
| of1nx | C076 |
| okyr4 | ·· |
| olohu | C031 |
| olfmk | C031 |
| okpom | ·C021· |
| ogp5l | missing |
| ofluk | C076 |
| oo7yv | missing |
customers · 4 rows
| customer_key | segment |
|---|---|
| missing | UNASSIGNED |
| C031 | partner |
| C076 | retail |
| C021 | online |
What you practise
- joins
- missing values
- trimming
Your answer is also checked on hidden datasets generated from the same rules.
Sign in to bookmark this and keep private notes.