Skip to main content
Talentd

Fix Product Name Format - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Sales

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| sale_id      | int     |
| product_name | varchar |
| sale_date    | date    |
+--------------+---------+
sale_id is the column with unique values for this table.
Each row of this table contains the product name and the date it was sold.

 

Since table Sales was filled manually in the year 2000, product_name may contain leading and/or trailing white spaces, also they are case-insensitive.

Write a solution to report

  • product_name in lowercase without leading or trailing white spaces.
  • sale_date in the format ('YYYY-MM').
  • total the number of times the product was sold in this month.

Return the result table ordered by product_name in ascending order. In case of a tie, order it by sale_date in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Sales table:
+---------+--------------+------------+
| sale_id | product_name | sale_date  |
+---------+--------------+------------+
| 1       | LCPHONE      | 2000-01-16 |
| 2       | LCPhone      | 2000-01-17 |
| 3       | LcPhOnE      | 2000-02-18 |
| 4       | LCKeyCHAiN   | 2000-02-19 |
| 5       | LCKeyChain   | 2000-02-28 |
| 6       | Matryoshka   | 2000-03-31 |
+---------+--------------+------------+
Output: 
+--------------+-----------+-------+
| product_name | sale_date | total |
+--------------+-----------+-------+
| lckeychain   | 2000-02   | 2     |
| lcphone      | 2000-01   | 2     |
| lcphone      | 2000-02   | 1     |
| matryoshka   | 2000-03   | 1     |
+--------------+-----------+-------+
Explanation: 
In January, 2 LcPhones were sold. Please note that the product names are not case sensitive and may contain spaces.
In February, 2 LCKeychains and 1 LCPhone were sold.
In March, one matryoshka was sold.

Approach Overview

Problem Overview: The Fix Product Name Format problem asks you to normalize inconsistent product names and aggregate sales by month. Product names may contain uppercase letters or extra spaces, and the sale date must be formatted as YYYY-MM. After cleaning the data, you group by the normalized product name and month, then count how many sales occurred.

Approach 1: Direct Aggregation Without Normalization (O(n) time, O(1) space)

The most naive approach simply groups by the raw product_name and formatted month from sale_date. You can use DATE_FORMAT(sale_date, '%Y-%m') and aggregate with COUNT(*). This runs in O(n) time because the database scans all rows once during grouping. The problem is data quality: names like 'iPhone', 'iphone', or ' iphone ' are treated as different groups. This produces incorrect aggregates when the same product appears in multiple formats.

Approach 2: Normalize Name + Monthly Aggregation (O(n) time, O(1) space)

The correct solution standardizes the product name before aggregation. Use TRIM() to remove leading and trailing spaces and LOWER() to convert the string to lowercase. Then format the date using DATE_FORMAT(sale_date, '%Y-%m'). After normalization, group by these cleaned values and compute the count with COUNT(*). The database still performs a single scan with grouping, so the time complexity remains O(n) and the extra space is constant outside the aggregation process.

This pattern appears frequently in SQL and database interview questions. Real production datasets often contain inconsistent casing or whitespace. Normalizing strings before aggregation ensures logically identical values collapse into the same group. SQL string functions like LOWER, TRIM, and date formatting utilities are the core tools.

Recommended for interviews: Interviewers expect the normalization approach. Showing the naive aggregation first demonstrates you understand grouping logic, but the correct solution proves you think about messy real-world data. Combining string cleanup with aggregation is a common pattern in SQL problem solving.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Direct AggregationO(n)O(1)Quick grouping when data is already clean and consistently formatted
Normalize Name + Aggregate (LOWER, TRIM, DATE_FORMAT)O(n)O(1)Best choice when product names contain inconsistent casing or extra whitespace

Video Solution

LeetCode 1543 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 2,071 views views

Frequently Asked Questions

Is Fix Product Name Format easy or hard?
Fix Product Name Format is categorized as Easy. The main challenge is recognizing that inconsistent casing and whitespace must be normalized before aggregation. Once you apply TRIM, LOWER, and DATE_FORMAT, the GROUP BY query becomes straightforward.
Fix Product Name Format Python/Java solution
This problem is typically solved directly in SQL rather than Python or Java. In MySQL, combine LOWER(TRIM(product_name)) and DATE_FORMAT(sale_date, '%Y-%m') with GROUP BY and COUNT(*). Application languages would only execute the SQL query against the database.
How to solve Fix Product Name Format in O(n)?
Scan the table once and normalize values during the query. Use LOWER(TRIM(product_name)) to standardize product names and DATE_FORMAT(sale_date, '%Y-%m') to extract the year and month. Group by these computed values and count rows with COUNT(*). This keeps the complexity linear with respect to the number of records.
What is the best approach for Fix Product Name Format?
The best approach normalizes product names using TRIM() to remove spaces and LOWER() to standardize casing, then formats the sale date with DATE_FORMAT('%Y-%m'). After normalization, use GROUP BY on the cleaned product name and month and compute COUNT(*). This produces correct aggregates even when the raw data contains inconsistent formatting.
Is Fix Product Name Format asked at Google/Amazon/Meta?
SQL data-cleaning and aggregation problems like this commonly appear in data engineering, analytics, and backend interview rounds at companies such as Amazon, Google, and Meta. The focus is usually on string normalization, date formatting, and correct grouping logic.
What data structure is used in Fix Product Name Format?
The problem relies on relational database aggregation rather than traditional in-memory data structures. Internally the database engine uses grouping mechanisms similar to hash aggregation to group rows by normalized product name and month.
What is the time complexity of Fix Product Name Format?
The query runs in O(n) time where n is the number of rows in the Sales table. The database performs a single scan while applying string functions and grouping the results. Extra space is O(1) outside the internal aggregation structures managed by the database engine.

Ready to solve this problem?

Practice Fix Product Name Format with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary