Category Codes From SKU Prefixes — SQL Strings Problem
Product SKUs are built as <category code>-<number>, and a pack-size variant adds one more part, as in SNK-6001-L.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #376
Problem statement
Product SKUs are built as <category code>-<number>, and a pack-size variant adds one more part, as in SNK-6001-L. The category code is always the part before the first hyphen.
Return one row per category code with the columns category_code, product_count (how many products carry it) and max_mrp (the highest MRP among them). Order the rows by category_code.
Tables
Table: Product
| Column | Type |
|---|---|
| sku | varchar |
| product_name | varchar |
| mrp | int |
Primary key: sku.
One row per product in the chain's catalogue; mrp is the printed maximum retail price in rupees. SKUs are upper case.
Examples
Example 1
Product
| sku | product_name | mrp |
|---|---|---|
| GRO-1001 | Basmati Rice 5kg | 640 |
| GRO-1002 | Toor Dal 1kg | 165 |
| SNK-6001 | Masala Chips 90g | 30 |
| SNK-6001-L | Masala Chips 180g | 55 |
| DAI-2002 | Paneer 200g | 90 |
| HOM-5001 | Dishwash Gel 500ml | 99 |
| HOM-5001-R | Dishwash Gel Refill 1L | 165 |
Output
| category_code | product_count | max_mrp |
|---|---|---|
| DAI | 1 | 90 |
| GRO | 2 | 640 |
| HOM | 2 | 165 |
| SNK | 2 | 55 |
How to solve Category Codes From SKU Prefixes
The category is not a column — it is hidden inside the SKU, so the query first derives it and then groups on the derived value. SUBSTRING_INDEX(sku, '-', 1) returns everything before the first occurrence of the delimiter, which is exactly the code for both SNK-6001 and the variant SNK-6001-L. LEFT(sku, LOCATE('-', sku) - 1) computes the same thing from the hyphen's position.
Once each row has its code, the rest is a plain GROUP BY with COUNT(*) and MAX(mrp). Grouping by the same expression that is selected keeps MySQL's ONLY_FULL_GROUP_BY mode happy; computing the code in a derived table first, as the last alternative does, avoids repeating the expression.
Two pitfalls: cutting at the last hyphen (SUBSTRING_INDEX(sku, '-', -1) takes the tail instead), and grouping by sku, which would give one row per product. Every SKU here contains a hyphen; if some did not, LOCATE would return 0 and the LEFT version would give an empty code while SUBSTRING_INDEX would give the whole SKU.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(sku, '-', 1) AS category_code,
COUNT(*) AS product_count,
MAX(mrp) AS max_mrp
FROM Product
GROUP BY SUBSTRING_INDEX(sku, '-', 1)
ORDER BY category_codeAnother way
SELECT LEFT(sku, LOCATE('-', sku) - 1) AS category_code, COUNT(*) AS product_count, MAX(mrp) AS max_mrp FROM Product GROUP BY LEFT(sku, LOCATE('-', sku) - 1) ORDER BY category_codeAnother way
SELECT category_code, COUNT(*) AS product_count, MAX(mrp) AS max_mrp FROM (SELECT SUBSTRING(sku, 1, INSTR(sku, '-') - 1) AS category_code, mrp FROM Product) p GROUP BY category_code ORDER BY category_code← Stores Crossing ₹20,000 in Paid POS Bills · Open Purchase Orders Past Their Promised Date →