Fix Product Name Format - Practice Coding Problems

Fix Product Name Format - Problem

Database Easy

Given a Sales table that contains product sales data from the year 2000, you need to clean and format the product names and aggregate sales data by month.

Table: Sales

Column Name	Type
sale_id	int
product_name	varchar
sale_date	date

Requirements:

Clean product_name by removing leading/trailing whitespace and converting to lowercase
Format sale_date to 'YYYY-MM' format
Count total sales for each product per month
Order by product_name ascending, then by sale_date ascending

Table Schema

Sales

Column Name	Type	Description
`sale_id` PK	int	Unique identifier for each sale
`product_name`	varchar	Product name (may have whitespace and mixed case)
`sale_date`	date	Date when the product was sold

Primary Key: sale_id

Note: Table was filled manually in 2000, so product names may have inconsistent formatting

Input & Output

Example 1 — Basic cleaning and grouping

Input Table:

sale_id	product_name	sale_date
1	LC Aloe vera	2000-01-01
2	LC Aloe vera	2000-01-15
3	CHAMOMILE	2000-02-01
4	Chamomile	2000-02-10

Output:

product_name	sale_date	total
chamomile	2000-02	2
lc aloe vera	2000-01	2

💡 Note:

The query cleans product names by removing whitespace and converting to lowercase. ' LC Aloe vera ' and 'LC Aloe vera' both become 'lc aloe vera'. Sales dates are formatted to month format, and sales are counted per product per month.

Example 2 — Single product multiple months

Input Table:

sale_id	product_name	sale_date
1	Green Tea	2000-03-01
2	GREEN TEA	2000-03-15
3	green tea	2000-04-01

Output:

product_name	sale_date	total
green tea	2000-03	2
green tea	2000-04	1

💡 Note:

All variations of 'Green Tea' are normalized to 'green tea'. The product appears in two different months (March and April), so we get separate rows for each month with their respective counts.

Constraints

1 ≤ sale_id ≤ 1000
product_name contains only alphabetic characters, spaces, and common punctuation
All dates are from the year 2000
sale_date is in valid date format

Visualization

Tap to expand

Understanding the Visualization

Raw Data

Messy product names with whitespace

Clean & Group

String functions + aggregation

Monthly Summary

Clean names with counts

Key Takeaway

🎯 Key Insight: Use string functions with GROUP BY to clean and aggregate messy data in a single query

Asked in

A Amazon 23 M Microsoft 18 G Google 15

Use string functions like TRIM() and LOWER() to clean product names, TO_CHAR() to format dates to YYYY-MM, then GROUP BY with COUNT() to aggregate monthly sales.

Table Schema

Sales

Column Name	Type	Description
`sale_id` PK	int	Unique identifier for each sale
`product_name`	varchar	Product name (may have whitespace and mixed case)
`sale_date`	date	Date when the product was sold

Primary Key: sale_id

Note: Table was filled manually in 2000, so product names may have inconsistent formatting

Common Approaches

Approach	Time	Space	Notes
✓ String Functions with GROUP BY	O(n log n)	O(k)	Clean product names with TRIM and LOWER, format dates, then group and count

String Functions with GROUP BY — Algorithm Steps

Step 1: Clean product names with TRIM() and LOWER()
Step 2: Format dates to YYYY-MM using DATE_FORMAT()
Step 3: GROUP BY cleaned name and formatted date, COUNT sales

Visualization

Tap to expand

Step-by-Step Walkthrough

Clean Names

TRIM + LOWER functions

Format Dates

Extract YYYY-MM

Group & Count

Aggregate sales

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Grouping and sorting operations

⚡ Linearithmic

Space Complexity

O(k)

Space for unique product-month combinations

✓ Linear Space

25.8K Views

Medium Frequency

~12 min Avg. Time

890 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

String Functions with GROUP BY — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler