Validate quantities and prices against ranges · SQL
Task: the rules
Input inventory(id, sku, quantity, unit_price, country) — text. id is unique; sku is always present. Normalize country: remove ASCII whitespace at both ends, then uppercase. Quarantine each invalid row with the FIRST failing reason, checked in this order: 1. missing_quantity quantity is missing 2. bad_quantity quantity is not a whole number: optional "-" then digits only 3. quantity_out_of_range quantity is less than 1 or greater than 500 4. bad_price unit_price is missing, or is not: optional "-", digits, optionally "." and 1–2 digits 5. price_out_of_range unit_price is zero or negative 6. bad_country country is not one of US, CA, MX (a missing country is also bad_country) Outputs (row order does not matter) clean(id text, sku text, quantity int64, unit_price decimal(10,2), country 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 | skustring | quantitystring | unit_pricestring | countrystring |
|---|---|---|---|---|---|
| 1 | invybqz | SKU-6252 | 1 | 760.28 | ·CA (raw value " CA") |
| 2 | invf1ge | SKU-7738 | 501 | 580.05 | MX |
| 3 | invoi41 | SKU-0003 | 12 | -1.00 | USA |
| 4 | inveree | SKU-5191 | 23 | 572.57 | ·CA (raw value " CA") |
| 5 | inv29z8 | SKU-0001 | 0 | 0.00 | UK |
| 6 | invvnhm | SKU-4924 | 434 | 524.17 | US |
| 7 | invkmms | SKU-4496 | 7.5 | 573.38 | CA |
| 8 | inveec2 | SKU-6847 | 1 | 0 | us |
| 9 | invezdp | SKU-0936 | 436 | 956.62 | US |
| 10 | invgt2u | SKU-9259 | 440 | 1,200.00 | MX |
| 11 | inv4nh6 | SKU-6643 | 72 | 1,200.00 | MX |
| 12 | invi6w4 | SKU-0002 | missing | abc | missing |
| 13 | invqlou | SKU-7543 | 470 | 12.5 | mx⇥ (raw value "mx\t") |
| 14 | inv04ar | SKU-1830 | empty | 969.00 | US |
| 15 | invqmkt | SKU-6302 | 34 | 9.999 | MX |
| 16 | invkepr | SKU-9281 | empty | missing | CA |
| 17 | invbl5x | SKU-4147 | 500 | missing | USA |
| 18 | inv6qmz | SKU-4169 | 388 | 0 | empty |
| 19 | invympz | SKU-6955 | empty | 9.999 | mx⇥ (raw value "mx\t") |
| 20 | invxr2k | SKU-4668 | 73 | 0.99 | US |
| 21 | inv2c6v | SKU-0917 | 171 | -1.00 | USA |
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.