Product Descriptions with Valid Serial Tokens

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Products =

[{"product_id":1,"product_name":"Widget A","description":"Includes safety code SN1234-5678 for activation."},{"product_id":2,"product_name":"Widget B","description":"Legacy docs mention sn1234-5678 only."},{"product_id":3,"product_name":"Widget C","description":"Draft used SN1234-56789 and must be corrected."},{"product_id":4,"product_name":"Widget D","description":"No serial marker appears in this description."},{"product_id":5,"product_name":"Widget E","description":"Pack contains SN4321-8765 with warranty paper."}]

Product Descriptions with Valid Serial Tokens

Hints

Product Descriptions with Valid Serial Tokens

Hints