Product Descriptions with Valid Serial Tokens

Easy

A product operations team needs to identify catalog entries whose free-text description includes at least one properly formatted serial token. Table: Products product_id: unique product identifier product_name: display name used in storefront and operations dashboards description: free-text product description A serial token is valid only when it matches this exact structure: Prefix "SN" (uppercase, case-sensitive). Exactly 4 digits. A hyphen '-'. Exactly 4 digits. The token is not immediately followed by another digit. Examples: "SN1234-5678" is valid. "SN123-5678" is invalid (only 3 digits before hyphen). "SN1234-56789" is invalid (extra trailing digit). "sn1234-5678" is invalid (lowercase prefix). Task: Return every product whose description contains at least one valid serial token. Output requirements: Return exactly these columns in this order: product_id product_name description Sort by product_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Products:
| product_id | product_name | description                                             |
|------------|--------------|---------------------------------------------------------|
| 1          | Widget A     | Includes safety code SN1234-5678 for activation.       |
| 2          | Widget B     | Legacy note sn1234-5678 appears in old docs.           |
| 3          | Widget C     | Tracking id SN1234-56789 was mistyped in draft.        |
| 4          | Widget D     | No serial token appears in this description.           |
| 5          | Widget E     | Pack contains SN4321-8765 and warranty details.        |

Output

[
  {"product_id":1,"product_name":"Widget A","description":"Includes safety code SN1234-5678 for activation."},
  {"product_id":5,"product_name":"Widget E","description":"Pack contains SN4321-8765 and warranty details."}
]

Explanation

Rows 1 and 5 contain an uppercase SN token with exactly 4 digits, a hyphen, and exactly 4 trailing digits not followed by another digit.

Example 2

Input

Products:
| product_id | product_name | description                               |
|------------|--------------|-------------------------------------------|
| 10         | Core Kit     | Prefix9SN0001-2222 remains valid.         |
| 11         | Eco Kit      | SN0001-2222 is valid; SN0001-22221 is not |
| 12         | Lite Kit     | SN0001/2222 uses wrong separator.         |
| 13         | Pro Kit      | SN00012-2222 has too many left digits.    |

Output

[
  {"product_id":10,"product_name":"Core Kit","description":"Prefix9SN0001-2222 remains valid."},
  {"product_id":11,"product_name":"Eco Kit","description":"SN0001-2222 is valid; SN0001-22221 is not"}
]

Explanation

A digit before SN does not invalidate the token. The strict structure check is applied from the SN start position.

Example 3

Input

Products:
| product_id | product_name | description                                     |
|------------|--------------|-------------------------------------------------|
| 20         | Unit M       | Valid at end SN9000-1000                        |
| 21         | Unit N       | Multiple tokens: SN1111-2222 then SN3333-4444   |
| 22         | Unit O       | Broken forms SN111-2222 and SN1111-222 only     |
| 23         | Unit P       | Mixed symbols [SN9999-0000] accepted            |

Output

[
  {"product_id":20,"product_name":"Unit M","description":"Valid at end SN9000-1000"},
  {"product_id":21,"product_name":"Unit N","description":"Multiple tokens: SN1111-2222 then SN3333-4444"},
  {"product_id":23,"product_name":"Unit P","description":"Mixed symbols [SN9999-0000] accepted"}
]

Explanation

Any row with at least one valid token is returned, even if other malformed patterns appear in the same description.

Constraints

0 <= number of rows in Products <= 200000
product_id is unique within a test case
1 <= product_id <= 1000000000
product_name is a non-null text value
description is a non-null text value
1 <= LENGTH(product_name) <= 80
1 <= LENGTH(description) <= 220
Valid token format is exactly: SN[0-9]{4}-[0-9]{4}
A candidate token is invalid if immediately followed by a digit
Prefix matching is case-sensitive (SN, not sn)
Output columns must be exactly: product_id, product_name, description
Final order must be product_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [product_id, product_name, description]

Products: | product_id | product_name | description | |------------|--------------|---------------------------------------------------------| | 1 | Widget A | Includes safety code SN1234-5678 for activation. | | 2 | Widget B | Legacy note sn1234-5678 appears in old docs. | | 3 | Widget C | Tracking id SN1234-56789 was mistyped in draft. | | 4 | Widget D | No serial token appears in this description. | | 5 | Widget E | Pack contains SN4321-8765 and warranty details. |

[ {"product_id":1,"product_name":"Widget A","description":"Includes safety code SN1234-5678 for activation."}, {"product_id":5,"product_name":"Widget E","description":"Pack contains SN4321-8765 and warranty details."} ]

Products: | product_id | product_name | description | |------------|--------------|-------------------------------------------| | 10 | Core Kit | Prefix9SN0001-2222 remains valid. | | 11 | Eco Kit | SN0001-2222 is valid; SN0001-22221 is not | | 12 | Lite Kit | SN0001/2222 uses wrong separator. | | 13 | Pro Kit | SN00012-2222 has too many left digits. |

[ {"product_id":10,"product_name":"Core Kit","description":"Prefix9SN0001-2222 remains valid."}, {"product_id":11,"product_name":"Eco Kit","description":"SN0001-2222 is valid; SN0001-22221 is not"} ]

Products: | product_id | product_name | description | |------------|--------------|-------------------------------------------------| | 20 | Unit M | Valid at end SN9000-1000 | | 21 | Unit N | Multiple tokens: SN1111-2222 then SN3333-4444 | | 22 | Unit O | Broken forms SN111-2222 and SN1111-222 only | | 23 | Unit P | Mixed symbols [SN9999-0000] accepted |

[ {"product_id":20,"product_name":"Unit M","description":"Valid at end SN9000-1000"}, {"product_id":21,"product_name":"Unit N","description":"Multiple tokens: SN1111-2222 then SN3333-4444"}, {"product_id":23,"product_name":"Unit P","description":"Mixed symbols [SN9999-0000] accepted"} ]