Find Products with Valid Serial Numbers - Practice Coding Problems

Find Products with Valid Serial Numbers - Problem

Database Easy

You're working as a data analyst for an e-commerce company that needs to identify products with valid serial numbers in their descriptions for inventory tracking purposes.

Given a products table containing product information, your task is to find all products whose descriptions contain a valid serial number pattern.

What makes a serial number valid?

Starts with exactly SN (case-sensitive - must be uppercase)
Followed by exactly 4 digits
Then a hyphen -
Followed by exactly 4 more digits
Example: SN1234-5678 ✅, sn1234-5678 ❌, SN123-5678 ❌

The serial number can appear anywhere within the description text - it doesn't need to be at the beginning.

Goal: Return all products with valid serial numbers, ordered by product_id in ascending order.

Input & Output

example_1.sql — Basic Pattern Matching

💡 Note: Product 1 and 3 have valid serial numbers (SN1234-5678 and SN4567-8901). Product 2 fails because 'sn' is lowercase, not uppercase 'SN'.

example_2.sql — Edge Cases

💡 Note: Only product 6 has a valid pattern. Product 4 has only 3 digits before hyphen, Product 5 has 5 digits before hyphen (both invalid).

example_3.sql — Multiple Serials

💡 Note: Product 7 has no serial. Product 8 contains valid serials (having multiple valid patterns still counts as valid). Product 9 has extra hyphen making it invalid.

Constraints

1 ≤ number of products ≤ 10⁴
1 ≤ product_id ≤ 10⁶
1 ≤ length of product_name ≤ 100
1 ≤ length of description ≤ 1000
Serial number pattern is case-sensitive
Serial number can appear anywhere in the description

Visualization

Tap to expand

Understanding the Visualization

Define Pattern

Set scanner to recognize SN####-#### format only

Scan Descriptions

Scanner checks each product description for the pattern

Pattern Match

When valid pattern found, product is flagged as valid

Collect Results

All flagged products are returned sorted by ID

Key Takeaway

🎯 Key Insight: Regular expressions provide an efficient, readable way to match complex string patterns in database queries, making them ideal for validation tasks like serial number format checking.

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 18

This database problem is best solved using regular expressions with the pattern SN[0-9]{4}-[0-9]{4}. The optimal SQL query uses REGEXP to efficiently match the exact serial number format: 'SN' followed by exactly 4 digits, a hyphen, and 4 more digits. Results are ordered by product_id ascending.

Common Approaches

Approach	Time	Space	Notes
✓ Regular Expression Pattern Matching (Optimal)	O(n × m)	O(1)	Use regex to efficiently find the serial number pattern in one pass
Character-by-Character Pattern Matching	O(n × m)	O(1)	Manually scan each position in the description for the serial number pattern

Regular Expression Pattern Matching (Optimal) — Algorithm Steps

Define regex pattern: 'SN[0-9]{4}-[0-9]{4}'
Use SQL REGEXP or LIKE with pattern matching
Database engine efficiently searches all descriptions
Return matching products ordered by product_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Define Pattern

Create regex pattern: SN[0-9]{4}-[0-9]{4}

Scan Text

Database engine efficiently scans description for pattern

Match Found

When pattern matches, include product in results

Sort Results

Order results by product_id ascending

Code -

solution.c — C

// C doesn't have built-in regex, but here's the SQL solution
#include <stdio.h>
#include <string.h>

// For C, we'd typically use a library like PCRE for regex
// or implement the character-by-character approach from brute force

// SQL Solution (most appropriate for this database problem)
char* sqlQuery = """
SELECT product_id, product_name, description
FROM products
WHERE description REGEXP 'SN[0-9]{4}-[0-9]{4}'
ORDER BY product_id ASC;
""";

// Alternative: Use the character-by-character function from brute force approach
// as C doesn't have native regex support

Time & Space Complexity

Time Complexity

⏱️

O(n × m)

Where n is number of products and m is average description length, but highly optimized by database engine

✓ Linear Growth

Space Complexity

O(1)

Only stores the result set, no additional data structures needed

✓ Linear Space

23.4K Views

Medium Frequency

~12 min Avg. Time

842 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Regular Expression Pattern Matching (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler