Distinct Product Categories

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Google

Problem

We're rebuilding our product taxonomy from the products table and need every category that appears on any product, each one listed once.

A quick category inventory.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

This is a set extraction dressed up as a taxonomy request. products holds one row per product, and the ask is the set of category values, not the rows. Anyone finds the category column. What separates candidates is saying the grain out loud before typing: a bare SELECT category FROM products returns 200 rows on this data for 11 real categories, so merchandising gets a taxonomy where 'Books' shows up twenty times.

Getting from rows to values

Step 1: Name the grain of products

product_id is unique, category is not. Most categories sit on 20 products, Sports on 18, and 'Collectibles' on just two. The table is product-grain, so the answer has to change grain to one row per category.

Step 2: Collapse on category alone

Project only category and deduplicate. The dedup applies to everything in the select list, so any second column you add (product_name, price) makes the pair unique again and you are back to 200 rows.

Step 3: Keep every category, stocked or not

Every 'Music' product has in_stock = 0. A reflexive WHERE in_stock = 1 to mean 'what we sell' quietly returns 10 categories instead of 11. The taxonomy covers every category on any product, so no row filter belongs here.

SELECT DISTINCT category
FROM products

Dedup the values

SELECT DISTINCT category says exactly what is asked: the set of values. It is the form to reach for when nothing is computed per category.

Group the values

GROUP BY category returns the same 11 rows and usually the same plan. It earns its place the moment a follow-up wants COUNT(*) per category next to the name.

Common follow-up questions

• How many products sit in each category? (Switches to `GROUP BY category` with `COUNT(*)`; the grain argument is unchanged.)
• What if `category` can be `NULL`? (The dedup keeps one `NULL` row; decide whether an uncategorized bucket belongs in the taxonomy.)
• What if the same category is stored as 'Books' and ' books '? (Normalize with `TRIM` and `LOWER` before deduplicating, or the set has near-duplicates.)
• Which categories have nothing in stock? (Tests a per-group condition, e.g. `MAX(in_stock) = 0`, which surfaces 'Music'.)