Category Codes From SKU Prefixes

Easy
376.

Category Codes From SKU Prefixes

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

ColumnType
skuvarchar
product_namevarchar
mrpint

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.

Example

The rows must come back in the order shown.

Product

skuproduct_namemrp
GRO-1001Basmati Rice 5kg640
GRO-1002Toor Dal 1kg165
SNK-6001Masala Chips 90g30
SNK-6001-LMasala Chips 180g55
DAI-2002Paneer 200g90
HOM-5001Dishwash Gel 500ml99
HOM-5001-RDishwash Gel Refill 1L165

Output

category_codeproduct_countmax_mrp
DAI190
GRO2640
HOM2165
SNK255
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
skuvarchar
product_namevarchar
mrpint

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

skuproduct_namemrp
GRO-1001Basmati Rice 5kg640
GRO-1002Toor Dal 1kg165
SNK-6001Masala Chips 90g30
SNK-6001-LMasala Chips 180g55
DAI-2002Paneer 200g90
HOM-5001Dishwash Gel 500ml99
HOM-5001-RDishwash Gel Refill 1L165

Output

category_codeproduct_countmax_mrp
DAI190
GRO2640
HOM2165
SNK255

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_code

Another 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_code

Another 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 →