Fix Product Name Format - Practice Coding Problems

Fix Product Name Format - Problem

Database Easy

🛍️ Sales Data Cleanup Challenge

You're working as a data analyst for an e-commerce company, and you've discovered that the sales database from the year 2000 is a complete mess! 😱

The Sales table contains product sales data, but since it was filled manually, the product names are inconsistent:

Some have extra spaces at the beginning or end
Some are in UPPERCASE, others in lowercase, and some are MiXeD cAsE
You need to clean this data and generate a monthly sales report

Your mission: Transform messy product names into a clean, standardized format and count how many times each product was sold per month.

Column Name	Type
sale_id	int
product_name	varchar
sale_date	date

Output Requirements:

Product names should be lowercase and trimmed of whitespace
Dates should be formatted as 'YYYY-MM'
Include the total count of sales for each product per month
Results ordered by product_name ASC, then sale_date ASC

Input & Output

example_1.sql — Basic Product Name Cleanup

$ Input: Sales table: +----------+----------------+------------+ | sale_id | product_name | sale_date | +----------+----------------+------------+ | 1 | ' iPhone ' | 2000-01-01 | | 2 | 'IPHONE' | 2000-01-15 | | 3 | ' Samsung ' | 2000-02-01 | | 4 | 'iphone' | 2000-01-20 | | 5 | 'SAMSUNG' | 2000-02-10 | +----------+----------------+------------+

› Output: +----------------+------------+-------+ | product_name | sale_date | total | +----------------+------------+-------+ | 'iphone' | '2000-01' | 3 | | 'samsung' | '2000-02' | 2 | +----------------+------------+-------+

💡 Note: Product names are cleaned (lowercased and trimmed), dates are formatted to YYYY-MM, and sales are counted per product per month. 'iPhone' variants (3 sales) are grouped together, and 'Samsung' variants (2 sales) are grouped together.

example_2.sql — Multiple Months Same Product

$ Input: Sales table: +----------+----------------+------------+ | sale_id | product_name | sale_date | +----------+----------------+------------+ | 1 | ' MacBook ' | 2000-01-05 | | 2 | 'MACBOOK' | 2000-01-15 | | 3 | ' macbook' | 2000-02-01 | | 4 | 'MacBook Pro' | 2000-01-20 | +----------+----------------+------------+

› Output: +----------------+------------+-------+ | product_name | sale_date | total | +----------------+------------+-------+ | 'macbook' | '2000-01' | 2 | | 'macbook' | '2000-02' | 1 | | 'macbook pro' | '2000-01' | 1 | +----------------+------------+-------+

💡 Note: Same product 'macbook' appears in different months, so it gets separate entries. 'MacBook Pro' is treated as a different product. Results are ordered by product name first, then by date.

example_3.sql — Edge Case with Extreme Whitespace

$ Input: Sales table: +----------+-------------------------+------------+ | sale_id | product_name | sale_date | +----------+-------------------------+------------+ | 1 | '\t\t XBOX \n ' | 2000-03-01 | | 2 | ' Xbox ' | 2000-03-15 | | 3 | ' ' | 2000-03-20 | | 4 | 'PlayStation' | 2000-03-01 | +----------+-------------------------+------------+

› Output: +----------------+------------+-------+ | product_name | sale_date | total | +----------------+------------+-------+ | '' | '2000-03' | 1 | | 'playstation' | '2000-03' | 1 | | 'xbox' | '2000-03' | 2 | +----------------+------------+-------+

💡 Note: Extreme whitespace (tabs, newlines, multiple spaces) is handled by TRIM function. Empty string after trimming is treated as a valid product name. Xbox variants are properly grouped together.

Visualization

Tap to expand

Understanding the Visualization

Input Raw Data

Messy sales records with inconsistent product names and full dates

Clean Product Names

Apply LOWER() and TRIM() to standardize all product names

Extract Month

Format dates to YYYY-MM for monthly grouping

Group & Count

Use GROUP BY to aggregate sales counts per product per month

Sort Results

ORDER BY product name, then date for consistent output

Key Takeaway

🎯 Key Insight: SQL's GROUP BY with aggregate functions transforms messy data into clean reports in a single optimized query, leveraging database engine optimizations for maximum performance.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database engine optimizes grouping and sorting, typically O(n log n) for sorting

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique product-month combinations (much smaller than n)

✓ Linear Space

Constraints

1 ≤ number of sales records ≤ 10⁵
1 ≤ product_name.length ≤ 100
product_name may contain leading/trailing whitespace and mixed case
sale_date is in valid YYYY-MM-DD format
All sale_date values are from the year 2000

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses SQL GROUP BY with string functions to clean product names and aggregate sales data efficiently. Key techniques include LOWER(TRIM(product_name)) for standardization, DATE_FORMAT() for month extraction, and COUNT(*) with GROUP BY for aggregation. This approach leverages database engine optimizations for O(n log n) performance.

Common Approaches

Approach	Time	Space	Notes
✓ SQL GROUP BY with String Functions (Optimal)	O(n log n)	O(k)	Use SQL aggregate functions to clean and group data in a single query
Brute Force (Cursor-based Approach)	O(n²)	O(n)	Process each row individually using cursors and loops

SQL GROUP BY with String Functions (Optimal) — Algorithm Steps

Apply LOWER(TRIM(product_name)) to clean product names
Use DATE_FORMAT or equivalent to extract 'YYYY-MM' from sale_date
GROUP BY the cleaned product name and formatted date
Use COUNT(*) to get the total sales per group
ORDER BY product_name ASC, sale_date ASC for proper sorting

Visualization

Tap to expand

Step-by-Step Walkthrough

String Function Application

Apply LOWER(TRIM()) to all product names simultaneously

Date Format Extraction

Extract YYYY-MM format from all dates using DATE_FORMAT()

Efficient Grouping

Database engine groups records by cleaned name and month using hash tables

Aggregation & Sorting

COUNT(*) aggregation and ORDER BY optimization in single pass

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database engine optimizes grouping and sorting, typically O(n log n) for sorting

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique product-month combinations (much smaller than n)

✓ Linear Space

Constraints

1 ≤ number of sales records ≤ 10⁵
1 ≤ product_name.length ≤ 100
product_name may contain leading/trailing whitespace and mixed case
sale_date is in valid YYYY-MM-DD format
All sale_date values are from the year 2000

42.3K Views

High Frequency

~15 min Avg. Time

1.8K 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

🛍️ Sales Data Cleanup Challenge

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

SQL GROUP BY with String Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler